Re: Slow Query on Postgres 8.2

Dave,
Is it me or are the two examples you attached returning different row
counts?
That means either the source data is different, or your queries are.

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Dave
Dutcher
Sent: Thursday, January 04, 2007 5:32 PM
To: pgsql-performance(at)postgresql(dot)org
Subject: [PERFORM] Slow Query on Postgres 8.2

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','milh
ouse','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