Hello,
I am looking at
upgrading from 8.1.2 to 8.2.0, and I've found a query which runs a lot
slower. Here is the query:
select type,
currency_id, instrument_id, sum(amount) as total_amount from om_transaction
where
strategy_id in ('BASKET1','BASKET2','BASKET3','BASKET4','BASKET5','BASKET6','BASKET7','BASKET8','BASKET9','BASKET10','BASKET11')
and owner_trader_id in ('dave','sam','bob','tad', 'tim','harry','frank','bart','lisa','homer','marge','maggie','apu','milhouse','disco stu')
and cf_account_id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,29)
and as_of_date > '2006-12-04' and as_of_date <= '2006-12-05'
group by type, currency_id, instrument_id;
where
strategy_id in ('BASKET1','BASKET2','BASKET3','BASKET4','BASKET5','BASKET6','BASKET7','BASKET8','BASKET9','BASKET10','BASKET11')
and owner_trader_id in ('dave','sam','bob','tad', 'tim','harry','frank','bart','lisa','homer','marge','maggie','apu','milhouse','disco stu')
and cf_account_id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,29)
and as_of_date > '2006-12-04' and as_of_date <= '2006-12-05'
group by type, currency_id, instrument_id;
I changed the values
in the in statements to fake ones, but it still takes over three seconds on 8.2,
where 8.1 only takes 26 milliseconds. When I increase the number of
valules in the IN clauses, the query rapidly gets worse. I tried
increasing my stats target to 1000 and analyzing, but that didn't help so I put
that back to 10. While the query is running the CPU is at 100%. Is
there a more efficient way to write a query like this? I've attached the
output from EXPLAIN ANALYZE in a file because it is somewhat
large.
Thanks,
Dave Dutcher
Telluride Asset
Management
952.653.6411
explain analyze
select type, currency_id, instrument_id, sum(amount) as total_amount from om_transaction
where
strategy_id in ('BASKET1','BASKET2','BASKET3','BASKET4','BASKET5','BASKET6','BASKET7','BASKET8','BASKET9','BASKET10','BASKET11')
and owner_trader_id in ('dave','sam','bob','tad', 'tim','harry','frank','bart','lisa','homer','marge','maggie','apu','milhouse','disco stu')
and cf_account_id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,29)
and as_of_date > '2006-12-04' and as_of_date <= '2006-12-05'
group by type, currency_id, instrument_id;
Slow:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=9287.82..9288.44 rows=49 width=35) (actual time=3411.238..3411.238 rows=0 loops=1)
-> Bitmap Heap Scan on om_transaction (cost=7421.67..9282.94 rows=488 width=35) (actual time=3411.235..3411.235 rows=0 loops=1)
Recheck Cond: ((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date) AND ((strategy_id)::text = ANY (('{BASKET1,BASKET2,BASKET3,BASKET4,BASKET5,BASKET6,BASKET7,BASKET8,BASKET9,BASKET10,BASKET11}'::character varying[])::text[])) AND ((owner_trader_id)::text = ANY (('{dave,sam,bob,tad,tim,harry,frank,bart,lisa,homer,marge,maggie,apu,milhouse,"disco stu"}'::character varying[])::text[])) AND (cf_account_id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,29}'::integer[])))
-> Bitmap Index Scan on om_transaction_om_transaction_index (cost=0.00..7421.67 rows=488 width=0) (actual time=3411.227..3411.227 rows=0 loops=1)
Index Cond: ((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date) AND ((strategy_id)::text = ANY (('{BASKET1,BASKET2,BASKET3,BASKET4,BASKET5,BASKET6,BASKET7,BASKET8,BASKET9,BASKET10,BASKET11}'::character varying[])::text[])) AND ((owner_trader_id)::text = ANY (('{dave,sam,bob,tad,tim,harry,frank,bart,lisa,homer,marge,maggie,apu,milhouse,"disco stu"}'::character varying[])::text[])) AND (cf_account_id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,29}'::integer[])))
Total runtime: 3411.429 ms
(6 rows)
Fast:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=431.45..431.87 rows=34 width=35) (actual time=25.442..25.442 rows=0 loops=1)
-> Bitmap Heap Scan on om_transaction (cost=383.09..428.08 rows=337 width=35) (actual time=25.429..25.429 rows=0 loops=1)
Recheck Cond: ((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date) AND (((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date) AND (cf_account_id = 1)) OR ((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date) AND (cf_account_id = 2)) OR (cf_account_id = 3) OR (cf_account_id = 4) OR (cf_account_id = 5) OR (cf_account_id = 6) OR (cf_account_id = 7) OR (cf_account_id = 8) OR (cf_account_id = 9) OR (cf_account_id = 10) OR (cf_account_id = 11) OR (cf_account_id = 12) OR (cf_account_id = 13) OR (cf_account_id = 14) OR (cf_account_id = 15) OR (cf_account_id = 16) OR (cf_account_id = 17) OR (cf_account_id = 18) OR (cf_account_id = 29)))
Filter: ((((strategy_id)::text = 'BASKET1'::text) OR ((strategy_id)::text = 'BASKET2'::text) OR ((strategy_id)::text = 'BASKET3'::text) OR ((strategy_id)::text = 'BASKET4'::text) OR ((strategy_id)::text = 'BASKET5'::text) OR ((strategy_id)::text = 'BASKET6'::text) OR ((strategy_id)::text = 'BASKET7'::text) OR ((strategy_id)::text = 'BASKET8'::text) OR ((strategy_id)::text = 'BASKET9'::text) OR ((strategy_id)::text = 'BASKET10'::text) OR ((strategy_id)::text = 'BASKET11'::text)) AND (((owner_trader_id)::text = 'dave'::text) OR ((owner_trader_id)::text = 'sam'::text) OR ((owner_trader_id)::text = 'bob'::text) OR ((owner_trader_id)::text = 'tad'::text) OR ((owner_trader_id)::text = 'tim'::text) OR ((owner_trader_id)::text = 'harry'::text) OR ((owner_trader_id)::text = 'frank'::text) OR ((owner_trader_id)::text = 'bart'::text) OR ((owner_trader_id)::text = 'lisa'::text) OR ((owner_trader_id)::text = 'homer'::text) OR ((owner_trader_id)::text = 'marge'::text) OR ((owner_trader_id)::text = 'maggie'::text) OR ((owner_trader_id)::text = 'apu'::text) OR ((owner_trader_id)::text = 'milhouse'::text) OR ((owner_trader_id)::text = 'disco stu'::text)))
-> BitmapAnd (cost=383.09..383.09 rows=11 width=0) (actual time=13.497..13.497 rows=0 loops=1)
-> Bitmap Index Scan on om_transaction_om_transaction_index (cost=0.00..101.69 rows=5949 width=0) (actual time=3.419..3.419 rows=7967 loops=1)
Index Cond: ((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date))
-> BitmapOr (cost=281.14..281.14 rows=7297 width=0) (actual time=9.968..9.968 rows=0 loops=1)
-> Bitmap Index Scan on om_transaction_om_transaction_index (cost=0.00..116.57 rows=2699 width=0) (actual time=3.627..3.627 rows=2747 loops=1)
Index Cond: ((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date) AND (cf_account_id = 1))
-> Bitmap Index Scan on om_transaction_om_transaction_index (cost=0.00..116.57 rows=2039 width=0) (actual time=3.501..3.501 rows=2152 loops=1)
Index Cond: ((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date) AND (cf_account_id = 2))
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.053..0.053 rows=0 loops=1)
Index Cond: (cf_account_id = 3)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: (cf_account_id = 4)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=1)
Index Cond: (cf_account_id = 5)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.078..0.078 rows=194 loops=1)
Index Cond: (cf_account_id = 6)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=1)
Index Cond: (cf_account_id = 7)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (cf_account_id = 8)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.023..0.023 rows=6 loops=1)
Index Cond: (cf_account_id = 9)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (cf_account_id = 10)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (cf_account_id = 11)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: (cf_account_id = 12)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.013..0.013 rows=12 loops=1)
Index Cond: (cf_account_id = 13)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (cf_account_id = 14)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: (cf_account_id = 15)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: (cf_account_id = 16)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.036..0.036 rows=66 loops=1)
Index Cond: (cf_account_id = 17)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..15.96 rows=2559 width=0) (actual time=2.403..2.403 rows=8201 loops=1)
Index Cond: (cf_account_id = 18)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.032..0.032 rows=0 loops=1)
Index Cond: (cf_account_id = 29)
Total runtime: 26.109 ms
(47 rows)
