Re: Slow Query on Postgres 8.2

-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Adam Rich
Sent: Thursday, January 04, 2007 7:19 PM
To: 'Dave Dutcher'; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] 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@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Dave Dutcher
Sent: Thursday, January 04, 2007 5:32 PM
To: pgsql-performance@postgresql.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','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