Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 2617DB5DBDE for ; Tue, 11 Oct 2011 14:59:24 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 61629-05-2 for ; Tue, 11 Oct 2011 17:59:16 +0000 (UTC) X-Greylist: delayed 00:06:37.020564 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 7DBC1B5DBD9 for ; Tue, 11 Oct 2011 14:59:16 -0300 (ADT) Received: by qyk30 with SMTP id 30so3792651qyk.19 for ; Tue, 11 Oct 2011 10:59:16 -0700 (PDT) Received: by 10.68.39.229 with SMTP id s5mr46720963pbk.76.1318355558463; Tue, 11 Oct 2011 10:52:38 -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 ml4sm1126120pbc.0.2011.10.11.10.52.36 (version=SSLv3 cipher=OTHER); Tue, 11 Oct 2011 10:52:37 -0700 (PDT) Message-ID: <4E948263.8040305@consistentstate.com> Date: Tue, 11 Oct 2011 11:52:35 -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: "pgsql-performance@postgresql.org" Subject: Query tuning help Content-Type: multipart/alternative; boundary="------------040407010905090909090201" 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/102 X-Sequence-Number: 45324 This is a multi-part message in MIME format. --------------040407010905090909090201 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 --------------------------------------------- --------------040407010905090909090201 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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
---------------------------------------------

--------------040407010905090909090201--