Re: [PERFORM] Sql Query :: Any advice ?
Here are the indexes I have for those queries
Indexes:
hist_account_balance :: "hist_account_balance_ix1" btree (trade_no)
trades :: "trades_pkey" PRIMARY KEY, btree (trade_no)
"trades_trade_date_index" btree (trade_date)
//H
Quoting vinny <vi...@xs4all.nl>:
On 2016-11-15 14:27, Henrik Ekenberg wrote:
Hi,
I have some data to join and I want to get som advice from you.
Any tips ? Any comments are apreciated
//H
select trade_no
from
forecast_trades.hist_account_balance
left join trades using (trade_no)
where trade_date > current_date - 120
and trade_date < current_date - 30
and forex = 'f'
and options = 'f'
group by trade_no
having max(account_size) > 0
;
( Query Plan : https://explain.depesz.com/s/4lOD )
QUERY PLAN
--
HashAggregate (cost=34760605.76..34773866.26 rows=1060840 width=15)
(actual time=1142816.632..1150194.076 rows=2550634 loops=1)
Group Key: hist_account_balance.trade_no
Filter: (max(hist_account_balance.account_size) > 0::numeric)
Rows Removed by Filter: 18240023
-> Hash Join (cost=3407585.35..34530512.29 rows=46018694
width=15) (actual time=60321.201..1108647.151 rows=44188963 loops=1)
Hash Cond: (hist_account_balance.trade_no = trades.trade_no)
-> Seq Scan on hist_account_balance
(cost=0.00..14986455.20
rows=570046720 width=15) (actual time=0.016..524427.140 rows=549165594
loops=1)
-> Hash (cost=3159184.13..3159184.13 rows=19872098
width=12) (actual time=60307.001..60307.001 rows=20790658 loops=1)
Buckets: 2097152 Batches: 1 Memory Usage:
913651kB
-> Index Scan using trades_trade_date_index on
trades
(cost=0.58..3159184.13 rows=19872098 width=12) (actual
time=0.078..52213.976 rows=20790658 loops=1)
Index Cond: ((trade_date >
(('now'::cstring)::date - 120)) AND (trade_date <
(('now'::cstring)::date - 30)))
Filter: ((NOT forex) AND (NOT options))
Rows Removed by Filter: 2387523
Planning time: 2.157 ms
Execution time: 1151234.290 ms
(15 rows)
What kind of indexes have you created for those tables?
--
Sent via pgsql-performance mailing list
(pgsql-performance@postgresql.org)
To make changes to your
subscription:http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Sql Query :: Any advice ?
Hi,
I have some data to join and I want to get som advice from you.
Any tips ? Any comments are apreciated
//H
select trade_no
from
forecast_trades.hist_account_balance
left join trades using (trade_no)
where trade_date > current_date - 120
and trade_date < current_date - 30
and forex = 'f'
and options = 'f'
group by trade_no
having max(account_size) > 0
;
( Query Plan : https://explain.depesz.com/s/4lOD )
QUERY
PLAN
--
HashAggregate (cost=34760605.76..34773866.26 rows=1060840 width=15)
(actual time=1142816.632..1150194.076 rows=2550634 loops=1)
Group Key: hist_account_balance.trade_no
Filter: (max(hist_account_balance.account_size) > 0::numeric)
Rows Removed by Filter: 18240023
-> Hash Join (cost=3407585.35..34530512.29 rows=46018694 width=15)
(actual time=60321.201..1108647.151 rows=44188963 loops=1)
Hash Cond: (hist_account_balance.trade_no =
trades.trade_no)
-> Seq Scan on hist_account_balance
(cost=0.00..14986455.20 rows=570046720 width=15) (actual
time=0.016..524427.140 rows=549165594 loops=1)
-> Hash (cost=3159184.13..3159184.13 rows=19872098
width=12) (actual time=60307.001..60307.001 rows=20790658 loops=1)
Buckets: 2097152 Batches: 1 Memory Usage:
913651kB
-> Index Scan using trades_trade_date_index
on trades (cost=0.58..3159184.13 rows=19872098 width=12) (actual
time=0.078..52213.976 rows=20790658 loops=1)
Index Cond: ((trade_date >
(('now'::cstring)::date - 120)) AND (trade_date < (('now'::cstring)::date -
30)))
Filter: ((NOT forex) AND (NOT
options))
Rows Removed by Filter: 2387523
Planning time: 2.157 ms
Execution time: 1151234.290 ms
(15 rows)
[PERFORM] Any advice tuning this query ?
Hi,
I have a select moving around a lot of data and takes times
Any advice tuning this query ?
EXPLAIN (ANALYZE ON, BUFFERS ON)
select
d.books,
d.date publish_date,
extract(dow from d.date) publish_dow,
week_num_fixed,
coalesce(sum(case when i.invno is not null then 1 else 0 end),0) as
daily_cnt,
coalesce(sum(i.activation_amount_sek),0) as daily_amt_sek
from dates_per_books d
left join publishing_data i on (d.books=i.books and
d.date=i.publish_date)
group by 1,2,3,4;
( explain : https://explain.depesz.com/s/aDOi )
QUERY
PLAN
--
GroupAggregate (cost=44606264.52..48172260.66 rows=4318263 width=68)
(actual time=839980.887..1029679.771 rows=43182733 loops=1)
Group Key: d.books, d.date, (date_part('dow'::text,
(d.date)::timestamp without time zone)), d.week_num_fixed
Buffers: shared hit=3, local hit=10153260 read=165591641, temp
read=2097960 written=2097960
I/O Timings: read=399828.103
-> Sort (cost=44606264.52..45104896.89 rows=199452945 width=48)
(actual time=839980.840..933883.311 rows=283894005 loops=1)
Sort Key: d.books, d.date, (date_part('dow'::text,
(d.date)::timestamp without time zone)), d.week_num_fixed
Sort Method: external merge Disk: 16782928kB
Buffers: shared hit=3, local hit=10153260 read=165591641,
temp read=2097960 written=2097960
I/O Timings: read=399828.103
-> Merge Left Join (cost=191.15..13428896.40
rows=199452945 width=48) (actual time=0.031..734937.112 rows=283894005
loops=1)
Merge Cond: ((d.books = i.books) AND (d.date =
i.publish_date))
Buffers: local hit=10153260 read=165591641
I/O Timings: read=399828.103
-> Index Scan using books_date on
dates_per_books d (cost=0.56..1177329.91 rows=43182628 width=20) (actual
time=0.005..33789.216 rows=43182733 loops=1)
Buffers: local hit=10 read=475818
I/O Timings: read=27761.376
-> Index Scan using activations_books_date
on publishing_data i (cost=0.57..7797117.25 rows=249348384 width=32)
(actual time=0.004..579806.706 rows=249348443 loops=1)
Buffers: local hit=10153250
read=165115823
I/O Timings: read=372066.727
Planning time: 2.864 ms
Execution time: 1034284.193 ms
(21 rows)
(END)
Re: [PERFORM] Tuning one Recurcive CTE
Hi, I will need to anonymized before sending it. Do you know if there is any tuning documents related to CTE scans //H På onsdag 09. november 2016 kl. 14:05:55, skrev Henrik Ekenberg <hen...@ekenberg.pw>: Hi, I try to tune one Recursive CTE. Explain Plan can be found here https://explain.depesz.com/s/yLVd Anyone can give me direction to check? //H. Rule number one; Always provide the query in question when asking for help tuning it. --ANDREAS JOSEPH KROGH CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com www.visena.com[1] [1] Links: -- [1] https://www.visena.com
[PERFORM] Tuning one Recurcive CTE
Hi, I try to tune one Recursive CTE. Explain Plan can be found here https://explain.depesz.com/s/yLVd Anyone can give me direction to check? //H.
