Re: Slow Query on Postgres 8.2

["Dave Dutcher" <dave@xxxxxxxxxxxx>]

Title: Message

The source data is a little different.  The fast query was on our production 8.1 server, and the other was a test 8.2 server with day old data.  The production server has like 3.84 million rows vs 3.83 million rows in test, so the statistics might be a little different, but I would figure the compairison is still valid.

 

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