Re: Slow Query on Postgres 8.2

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(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Adam Rich
Sent: Thursday, January 04, 2007 7:19 PM
To: 'Dave Dutcher'; pgsql-performance(at)postgresql(dot)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(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','BASK
ET8','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