From: CS DBA <cs_dba@consistentstate.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Query tuning help
Date: Tue, 11 Oct 2011 11:52:35 -0600
Message-ID: <4E948263.8040305@consistentstate.com> (raw)
Hi all ;
I'm trying to tune a difficult query.
I have 2 tables:
cust_acct (9million rows)
cust_orders (200,000 rows)
Here's the query:
SELECT
a.account_id, a.customer_id, a.order_id, a.primary_contact_id,
a.status, a.customer_location_id, a.added_date,
o.agent_id, p.order_location_id_id,
COALESCE(a.customer_location_id, p.order_location_id) AS
order_location_id
FROM
cust_acct a JOIN
cust_orders o
ON a.order_id = p.order_id;
I can't get it to run much faster that about 13 seconds, in most cases
it's more like 30 seconds.
We have a box with 8 2.5GZ cores and 28GB of ram, shared_buffers is at 8GB
I've tried separating the queries as filtering queries & joining the
results, disabling seq scans, upping work_mem and half a dozen other
approaches. Here's the explain plan:
Hash Join (cost=151.05..684860.30 rows=9783130 width=100)
Hash Cond: (a.order_id = o.order_id)
-> Seq Scan on cust_acct a (cost=0.00..537962.30 rows=9783130
width=92)
-> Hash (cost=122.69..122.69 rows=2269 width=12)
-> Seq Scan on cust_orders o (cost=0.00..122.69 rows=2269
width=12)
Thanks in advance for any help, tips, etc...
--
---------------------------------------------
Kevin Kempter - Constent State
A PostgreSQL Professional Services Company
www.consistentstate.com
---------------------------------------------
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: cs_dba@consistentstate.com
Subject: Re: Query tuning help
In-Reply-To: <4E948263.8040305@consistentstate.com>
* 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