Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 6A50DB5DBDE for ; Tue, 11 Oct 2011 15:14:36 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 28366-01 for ; Tue, 11 Oct 2011 18:14:29 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-qy0-f174.google.com (mail-qy0-f174.google.com [209.85.216.174]) by mail.postgresql.org (Postfix) with ESMTP id 70233B5D803 for ; Tue, 11 Oct 2011 15:14:29 -0300 (ADT) Received: by qyk30 with SMTP id 30so3810999qyk.19 for ; Tue, 11 Oct 2011 11:14:28 -0700 (PDT) Received: by 10.68.74.194 with SMTP id w2mr37255206pbv.91.1318356868525; Tue, 11 Oct 2011 11:14:28 -0700 (PDT) Received: from issac.consistentstate.com (173-164-51-9-colorado.hfc.comcastbusiness.net. [173.164.51.9]) by mx.google.com with ESMTPS id ml4sm1295384pbc.0.2011.10.11.11.14.26 (version=SSLv3 cipher=OTHER); Tue, 11 Oct 2011 11:14:27 -0700 (PDT) Message-ID: <4E948781.9090806@consistentstate.com> Date: Tue, 11 Oct 2011 12:14:25 -0600 From: CS DBA User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.22) Gecko/20110906 Fedora/3.1.14-1.fc14 Lightning/1.0b2 Thunderbird/3.1.14 MIME-Version: 1.0 To: Pavel Stehule CC: "pgsql-performance@postgresql.org" Subject: Re: Query tuning help References: <4E948263.8040305@consistentstate.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------030807070200090509020906" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201110/107 X-Sequence-Number: 45329 This is a multi-part message in MIME format. --------------030807070200090509020906 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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: >> 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 --------------------------------------------- --------------030807070200090509020906 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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
---------------------------------------------
--------------030807070200090509020906--