Re: Sql Query :: Any advice ?

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?