Re: Slow Query on Postgres 8.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Title: Message
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

 

 

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux