Re: Sql Query :: Any advice ? - Mailing list 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>