Re: Sql Query :: Any advice ?

<div dir="ltr">Hi, <br>
</div><div dir="ltr"><br>
</div><div dir="ltr">Forex and options are in  trades table<br>
</div><div dir="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><div class="wps_quotion">On vinny &lt;vinny(at)xs4all(dot)nl&gt;, Nov 15, 2016 6:46 PM wrote:<br type="attribution"><blockquote class="quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><p></p><p dir="ltr">Are the forex and options in the hist_account_balance table?
<br>
The sequential scan is on that table so if they are,
<br>
so I&#39;m guessing they should probably by in the index.
<br>

<br>
On 2016-11-15 15:30, Henrik Ekenberg wrote:
<br>
&gt; Here are the indexes I have for those queries
<br>
&gt;
<br>
&gt; Indexes:
<br>
&gt;
<br>
&gt; hist_account_balance  :: &quot;hist_account_balance_ix1&quot; btree (trade_no)
<br>
&gt;
<br>
&gt; trades :: &quot;trades_pkey&quot; PRIMARY KEY, btree  (trade_no)
<br>
&gt;  &quot;trades_trade_date_index&quot; btree (trade_date)
<br>
&gt;
<br>
&gt; //H
<br>
&gt;
<br>
&gt; Quoting vinny &lt;<a href="mailto:vinny(at)xs4all(dot)nl">vinny(at)xs4all(dot)nl</a>&gt;:
<br>
&gt;
<br>
&gt;&gt; On 2016-11-15 14:27, Henrik Ekenberg wrote:
<br>
&gt;&gt;
<br>
&gt;&gt;&gt; Hi,
<br>
&gt;&gt;&gt;
<br>
&gt;&gt;&gt; I have some data to join and I want to get som advice from you.
<br>
&gt;&gt;&gt;
<br>
&gt;&gt;&gt; Any tips ? Any comments are apreciated
<br>
&gt;&gt;&gt;
<br>
&gt;&gt;&gt; //H
<br>
&gt;&gt;&gt;
<br>
&gt;&gt;&gt; select trade_no
<br>
&gt;&gt;&gt; from
<br>
&gt;&gt;&gt; forecast_trades.hist_account_balance
<br>
&gt;&gt;&gt; left join trades using (trade_no)
<br>
&gt;&gt;&gt; where  trade_date &gt; current_date - 120
<br>
&gt;&gt;&gt; and    trade_date &lt; current_date - 30
<br>
&gt;&gt;&gt; and    forex = &#39;f&#39;
<br>
&gt;&gt;&gt; and    options = &#39;f&#39;
<br>
&gt;&gt;&gt; group by trade_no
<br>
&gt;&gt;&gt; having max(account_size) &gt; 0
<br>
&gt;&gt;&gt; ;
<br>
&gt;&gt;&gt;
<br>
&gt;&gt;&gt; ( Query Plan : <a href="https://explain.depesz.com/s/4lOD">https://explain.depesz.com/s/4lOD</a> )
<br>
&gt;&gt;&gt;
<br>
&gt;&gt;&gt; QUERY PLAN
<br>
&gt;&gt;&gt;
<br>
&gt;&gt;&gt;
<br>
&gt;&gt;
<br>
&gt; ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<br>
&gt;&gt;&gt; HashAggregate  (cost=34760605.76..34773866.26 rows=1060840
<br>
&gt;&gt;&gt; width=15)
<br>
&gt;&gt;&gt; (actual time=1142816.632..1150194.076 rows=2550634 loops=1)
<br>
&gt;&gt;&gt; Group Key: hist_account_balance.trade_no
<br>
&gt;&gt;&gt; Filter: (max(hist_account_balance.account_size) &gt; 0::numeric)
<br>
&gt;&gt;&gt; Rows Removed by Filter: 18240023
<br>
&gt;&gt;&gt; -&gt;  Hash Join  (cost=3407585.35..34530512.29 rows=46018694
<br>
&gt;&gt;&gt; width=15) (actual time=60321.201..1108647.151 rows=44188963
<br>
&gt;&gt;&gt; loops=1)
<br>
&gt;&gt;&gt; Hash Cond: (hist_account_balance.trade_no =
<br>
&gt;&gt;&gt; trades.trade_no)
<br>
&gt;&gt;&gt; -&gt;  Seq Scan on hist_account_balance
<br>
&gt;&gt;&gt; (cost=0.00..14986455.20
<br>
&gt;&gt;&gt; rows=570046720 width=15) (actual time=0.016..524427.140
<br>
&gt;&gt;&gt; rows=549165594
<br>
&gt;&gt;&gt; loops=1)
<br>
&gt;&gt;&gt; -&gt;  Hash  (cost=3159184.13..3159184.13 rows=19872098
<br>
&gt;&gt;&gt; width=12) (actual time=60307.001..60307.001 rows=20790658 loops=1)
<br>
&gt;&gt;&gt; Buckets: 2097152  Batches: 1  Memory Usage: 913651kB
<br>
&gt;&gt;&gt; -&gt;  Index Scan using trades_trade_date_index on
<br>
&gt;&gt;&gt; trades
<br>
&gt;&gt;&gt; (cost=0.58..3159184.13 rows=19872098 width=12) (actual
<br>
&gt;&gt;&gt; time=0.078..52213.976 rows=20790658 loops=1)
<br>
&gt;&gt;&gt; Index Cond: ((trade_date &gt;
<br>
&gt;&gt;&gt; ((&#39;now&#39;::cstring)::date - 120)) AND (trade_date &lt;
<br>
&gt;&gt;&gt; ((&#39;now&#39;::cstring)::date - 30)))
<br>
&gt;&gt;&gt; Filter: ((NOT forex) AND (NOT options))
<br>
&gt;&gt;&gt; Rows Removed by Filter: 2387523
<br>
&gt;&gt;&gt; Planning time: 2.157 ms
<br>
&gt;&gt;&gt; Execution time: 1151234.290 ms
<br>
&gt;&gt;&gt; (15 rows)
<br>
&gt;&gt; What kind of indexes have you created for those tables?
<br>
&gt;&gt;
<br>
&gt;&gt; --
<br>
&gt;&gt; Sent via pgsql-performance mailing list
<br>
&gt;&gt; (<a href="mailto:pgsql-performance(at)postgresql(dot)org">pgsql-performance(at)postgresql(dot)org</a>)
<br>
&gt;&gt; To make changes to your
<br>
&gt;&gt; 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(at)postgresql(dot)org">pgsql-performance(at)postgresql(dot)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>
</p>
</blockquote></div>