Re: Sql Query :: Any advice ? - Mailing list pgsql-performance
From | vinny |
---|---|
Subject | Re: Sql Query :: Any advice ? |
Date | |
Msg-id | 1fd2cd0b983d53719d7a0f50855303a6@xs4all.nl Whole thread Raw |
In response to | Re: Sql Query :: Any advice ? (Henrik Ekenberg <henrik@ekenberg.pw>) |
List | pgsql-performance |
Are the forex and options in the hist_account_balance table? The sequential scan is on that table so if they are, so I'm guessing they should probably by in the index. On 2016-11-15 15:30, Henrik Ekenberg wrote: > 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@xs4all.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@postgresql.org) >> To make changes to your >> subscription:http://www.postgresql.org/mailpref/pgsql-performance
pgsql-performance by date: