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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
