Re: 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 <vinny(at)xs4all(dot)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(at)postgresql(dot)org)
> To make changes to your
> subscription:http://www.postgresql.org/mailpref/pgsql-performance