From: | "Dave Dutcher" <dave(at)tridecap(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Slow Query on Postgres 8.2 |
Date: | 2007-01-04 23:31:43 |
Message-ID: | 006801c73058$7eb7d2a0$8300a8c0@tridecap.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
Attachment | Content-Type | Size |
---|---|---|
plans.txt | text/plain | 10.4 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-01-05 01:12:49 | Re: Slow Query on Postgres 8.2 |
Previous Message | Matthew Schumacher | 2007-01-04 18:42:40 | Re: PostgreSQL to host e-mail? |