Sql Query :: Any advice ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux