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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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','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
