Обсуждение: 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)
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?
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 <vinny@xs4all.nl>:
On 2016-11-15 14:27, Henrik Ekenberg wrote:
What kind of indexes have you created for those tables?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)
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance
Are the forex and options in the hist_account_balance table? The sequential scan is on that table so if they are, so I'm guessing they should probably by in the index. On 2016-11-15 15:30, Henrik Ekenberg wrote: > 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 <vinny@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
<div dir="ltr">Hi, <br /></div><div dir="ltr"><br /></div><div dir="ltr">Forex and options are in trades table<br /></div><divdir="ltr"><br /></div><div dir="ltr">Best regards <br /></div><div dir="ltr">Henrik <br /></div><div dir="ltr"><br/></div><div dir="ltr"><br /></div><div dir="ltr"><br /></div><div class="wps_signature">Sent from my Mi pad</div><divclass="wps_quotion">On vinny <vinny@xs4all.nl>, Nov 15, 2016 6:46 PM wrote:<br type="attribution" /><blockquoteclass="quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><p><p dir="ltr">Are theforex and options in the hist_account_balance table? <br /> The sequential scan is on that table so if they are, <br />so I'm guessing they should probably by in the index. <br /><br /> On 2016-11-15 15:30, Henrik Ekenberg wrote: <br /> >Here are the indexes I have for those queries <br /> > <br /> > Indexes: <br /> > <br /> > hist_account_balance :: "hist_account_balance_ix1" btree (trade_no) <br /> > <br /> > trades :: "trades_pkey" PRIMARYKEY, btree (trade_no) <br /> > "trades_trade_date_index" btree (trade_date) <br /> > <br /> > //H <br />> <br /> > Quoting vinny <<a href="mailto:vinny@xs4all.nl">vinny@xs4all.nl</a>>: <br /> > <br /> >>On 2016-11-15 14:27, Henrik Ekenberg wrote: <br /> >> <br /> >>> Hi, <br /> >>> <br /> >>>I have some data to join and I want to get som advice from you. <br /> >>> <br /> >>> Any tips? Any comments are apreciated <br /> >>> <br /> >>> //H <br /> >>> <br /> >>> selecttrade_no <br /> >>> from <br /> >>> forecast_trades.hist_account_balance <br /> >>> leftjoin trades using (trade_no) <br /> >>> where trade_date > current_date - 120 <br /> >>> and trade_date < current_date - 30 <br /> >>> and forex = 'f' <br /> >>> and options = 'f' <br/> >>> group by trade_no <br /> >>> having max(account_size) > 0 <br /> >>> ; <br /> >>><br /> >>> ( Query Plan : <a href="https://explain.depesz.com/s/4lOD">https://explain.depesz.com/s/4lOD</a>) <br /> >>> <br /> >>> QUERYPLAN <br /> >>> <br /> >>> <br /> >> <br /> > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- <br/> >>> HashAggregate (cost=34760605.76..34773866.26 rows=1060840 <br /> >>> width=15) <br /> >>>(actual time=1142816.632..1150194.076 rows=2550634 loops=1) <br /> >>> Group Key: hist_account_balance.trade_no<br /> >>> Filter: (max(hist_account_balance.account_size) > 0::numeric) <br />>>> Rows Removed by Filter: 18240023 <br /> >>> -> Hash Join (cost=3407585.35..34530512.29 rows=46018694<br /> >>> width=15) (actual time=60321.201..1108647.151 rows=44188963 <br /> >>> loops=1)<br /> >>> Hash Cond: (hist_account_balance.trade_no = <br /> >>> trades.trade_no) <br /> >>>-> Seq Scan on hist_account_balance <br /> >>> (cost=0.00..14986455.20 <br /> >>> rows=570046720width=15) (actual time=0.016..524427.140 <br /> >>> rows=549165594 <br /> >>> loops=1) <br/> >>> -> Hash (cost=3159184.13..3159184.13 rows=19872098 <br /> >>> width=12) (actual time=60307.001..60307.001rows=20790658 loops=1) <br /> >>> Buckets: 2097152 Batches: 1 Memory Usage: 913651kB<br /> >>> -> Index Scan using trades_trade_date_index on <br /> >>> trades <br /> >>>(cost=0.58..3159184.13 rows=19872098 width=12) (actual <br /> >>> time=0.078..52213.976 rows=20790658loops=1) <br /> >>> Index Cond: ((trade_date > <br /> >>> (('now'::cstring)::date - 120))AND (trade_date < <br /> >>> (('now'::cstring)::date - 30))) <br /> >>> Filter: ((NOT forex) AND(NOT options)) <br /> >>> Rows Removed by Filter: 2387523 <br /> >>> Planning time: 2.157 ms <br />>>> Execution time: 1151234.290 ms <br /> >>> (15 rows) <br /> >> What kind of indexes have youcreated for those tables? <br /> >> <br /> >> -- <br /> >> Sent via pgsql-performance mailing list <br/> >> (<a href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a>) <br /> >> Tomake changes to your <br /> >> subscription:<a href="http://www.postgresql.org/mailpref/pgsql-performance">http://www.postgresql.org/mailpref/pgsql-performance</a><br /><br/><br /> -- <br /> Sent via pgsql-performance mailing list (<a href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a>)<br /> To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-performance">http://www.postgresql.org/mailpref/pgsql-performance</a><br /></blockquote></div>