pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: CS DBA <cs_dba@consistentstate.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Query tuning help
Date: Tue, 11 Oct 2011 12:14:25 -0600
Message-ID: <4E948781.9090806@consistentstate.com> (raw )
In-Reply-To: <CAFj8pRDCcXha7fsD6N=-9_xh2-nLx-6Zb-Uc+ex5wGqe4=XtLQ@mail.gmail.com >
References: <4E948263.8040305@consistentstate.com >
<CAFj8pRDCcXha7fsD6N=-9_xh2-nLx-6Zb-Uc+ex5wGqe4=XtLQ@mail.gmail.com >
On 10/11/2011 12:02 PM, Pavel Stehule wrote:
> Hello
>
> please, send EXPLAIN ANALYZE output instead.
>
> Regards
>
> Pavel Stehule
>
> 2011/10/11 CS DBA<cs_dba@consistentstate.com>:
>> 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...
>>
>>
>>
>>
>>
Explain Analyze:
Hash Join (cost=154.46..691776.11 rows=10059626 width=100) (actual
time=5.191..37551.360 rows=10063432 loops=1)
Hash Cond: (a.order_id = o.order_id)
-> Seq Scan on cust_acct a (cost=0.00..540727.26 rows=10059626
width=92) (actual time=0.022..18987.095 rows=10063432 loops=1)
-> Hash (cost=124.76..124.76 rows=2376 width=12) (actual
time=5.135..5.135 rows=2534 loops=1)
-> Seq Scan on cust_orders o (cost=0.00..124.76 rows=2376
width=12) (actual time=0.011..2.843 rows=2534 loops=1)
Total runtime: 43639.105 ms
(6 rows)
>>
>>
>>
>>
>>
>>
>> --
>> ---------------------------------------------
>> Kevin Kempter - Constent State
>> A PostgreSQL Professional Services Company
>> www.consistentstate.com
>> ---------------------------------------------
--
---------------------------------------------
Kevin Kempter - Constent State
A PostgreSQL Professional Services Company
www.consistentstate.com
---------------------------------------------
view thread (22+ messages) latest in thread
Message-ID: <4E948781.9090806@consistentstate.com>
Permalink: ../4E948781.9090806@consistentstate.com/
Also on: postgresql.org/message-id/4E948781.9090806@consistentstate.com
copy link · copy postgr.es
reply 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, pavel.stehule@gmail.com
Subject: Re: Query tuning help
In-Reply-To: <4E948781.9090806@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