From: Adam Rich <adam.r@sbcglobal.net>
To: 'Dave Dutcher' <dave@tridecap.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow Query on Postgres 8.2
Date: Thu, 4 Jan 2007 19:19:25 -0600
Message-ID: <00f501c73067$89f2bd60$6400a8c0@dualcore> (raw)
In-Reply-To: <006801c73058$7eb7d2a0$8300a8c0@tridecap.com>
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@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] 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','
BASKET8','BASKET9','BASKET10','BASKET11')
and owner_trader_id in ('dave','sam','bob','tad',
'tim','harry','frank','bart','lisa','homer','marge','maggie','apu','milh
ouse','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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: adam.r@sbcglobal.net, dave@tridecap.com
Subject: Re: Slow Query on Postgres 8.2
In-Reply-To: <00f501c73067$89f2bd60$6400a8c0@dualcore>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox