Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 3C196B5DBD8 for ; Tue, 11 Oct 2011 15:37:15 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 18316-06 for ; Tue, 11 Oct 2011 18:37:07 +0000 (UTC) X-Greylist: delayed 00:05:55.369648 by SQLgrey-1.8.0-rc2 Received: from mail-iy0-f174.google.com (mail-iy0-f174.google.com [209.85.210.174]) by mail.postgresql.org (Postfix) with ESMTP id CDBDCB5D803 for ; Tue, 11 Oct 2011 15:37:06 -0300 (ADT) Received: by iaek3 with SMTP id k3so1052685iae.19 for ; Tue, 11 Oct 2011 11:37:06 -0700 (PDT) Received: by 10.42.136.196 with SMTP id v4mr28078498ict.3.1318357870840; Tue, 11 Oct 2011 11:31:10 -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 z11sm57357292iba.6.2011.10.11.11.31.08 (version=SSLv3 cipher=OTHER); Tue, 11 Oct 2011 11:31:10 -0700 (PDT) Message-ID: <4E948B6B.8000905@consistentstate.com> Date: Tue, 11 Oct 2011 12:31:07 -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: Szymon Guz CC: "pgsql-performance@postgresql.org" Subject: Re: Query tuning help References: <4E948263.8040305@consistentstate.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------000702030300000909080400" 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/109 X-Sequence-Number: 45331 This is a multi-part message in MIME format. --------------000702030300000909080400 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 10/11/2011 12:03 PM, Szymon Guz wrote: > > > On 11 October 2011 19:52, CS DBA > wrote: > > 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... > > > > > > Hi, > two simple questions: > > - do you really need getting all 9M rows? unfortunately yes > - show us the table structure, together with index definitions > cust_acct table Column | Type | Modifiers -----------------------+-----------------------------+------------------------------------------------------- account_id | bigint | not null default nextval('cust_account_id_seq'::regclass) customer_id | character varying(10) | order_id | integer | not null primary_contact_id | bigint | status | accounts_status_type | not null customer_location_id | integer | added_date | timestamp with time zone | not null Indexes: "cust_acct_pkey" PRIMARY KEY, btree (account_id) "cust_acct_cust_id_indx" btree (customer_id) "cust_acct_order_id_id_indx" btree (order_id) "cust_acct_pri_contact_id_indx" btree (primary_contact_id) cust_orders table Column | Type | Modifiers -----------------------------+-----------------------------+------------------------------------------------------- order_id | integer | not null default nextval('order_id_seq'::regclass) backorder_tag_id | character varying(18) | order_location_id | integer | not null work_order_name | character varying(75) | not null status | programs_status_type | not null additional_info_tag_shipper | character(16) | not null additional_info_tag_cust | character(16) | not null additional_info_tag_manuf | character(16) | not null additional_info_tag_supply | character(16) | not null acct_active_dt | timestamp without time zone | acct_last_activity_date | timestamp without time zone | acct_backorder_items | boolean | not null default false custom_info1 | text | custom_info2 | text | custom_info3 | text | custom_info4 | text | custom_info5 | text | custom_info6 | text | custom_info7 | text | Indexes: "cust_orders_pkey" PRIMARY KEY, btree (order_id) "cust_orders_order_id_loc_id_key" UNIQUE, btree (order_id, order_location_id) "cust_orders_loc_id_idx" btree (order_location_id) > regards > Szymon > > -- --------------------------------------------- Kevin Kempter - Constent State A PostgreSQL Professional Services Company www.consistentstate.com --------------------------------------------- --------------000702030300000909080400 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 10/11/2011 12:03 PM, Szymon Guz wrote:


On 11 October 2011 19:52, CS DBA <cs_dba@consistentstate.com> wrote:
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...





Hi,
two simple questions:

- do you really need getting all 9M rows?
unfortunately yes


- show us the table structure, together with index definitions



cust_acct table

        Column         |            Type             |                       Modifiers                      
-----------------------+-----------------------------+-------------------------------------------------------
 account_id            | bigint                      | not null default nextval('cust_account_id_seq'::regclass)
 customer_id           | character varying(10)       |
 order_id              | integer                     | not null
 primary_contact_id    | bigint                      |
 status                | accounts_status_type        | not null
 customer_location_id  | integer                     |
 added_date            | timestamp with time zone    | not null
Indexes:
    "cust_acct_pkey" PRIMARY KEY, btree (account_id)
    "cust_acct_cust_id_indx" btree (customer_id)
    "cust_acct_order_id_id_indx" btree (order_id)
    "cust_acct_pri_contact_id_indx" btree (primary_contact_id)





cust_orders table


           Column            |            Type             |                       Modifiers                                                                 
-----------------------------+-----------------------------+-------------------------------------------------------                                          
 order_id                    | integer                     | not null default nextval('order_id_seq'::regclass)                                           
 backorder_tag_id            | character varying(18)       |                                                                                                 
 order_location_id           | integer                     | not null                                                                                        
 work_order_name             | character varying(75)       | not null                                                                                        
 status                      | programs_status_type        | not null                                 
 additional_info_tag_shipper | character(16)               | not null                                    
 additional_info_tag_cust    | character(16)               | not null                                            
 additional_info_tag_manuf   | character(16)               | not null                                            
 additional_info_tag_supply  | character(16)               | not null                                       
 acct_active_dt              | timestamp without time zone |                                                                                                 
 acct_last_activity_date     | timestamp without time zone |                                                                                                 
 acct_backorder_items        | boolean                     | not null default false                                                                          
 custom_info1                | text                        |                                                                                                 
 custom_info2                | text                        |                                                                                                 
 custom_info3                | text                        |                                                                                                 
 custom_info4                | text                        |                                                                                                 
 custom_info5                | text                        |                                                                                                 
 custom_info6                | text                        |                 
 custom_info7                | text                        |                                                                                                   
Indexes:                                                                                                                                                     
    "cust_orders_pkey" PRIMARY KEY, btree (order_id)                                                                                                          
    "cust_orders_order_id_loc_id_key" UNIQUE, btree (order_id, order_location_id)                                                                                                                                                                                                          
    "cust_orders_loc_id_idx" btree (order_location_id)













regards
Szymon




-- 
---------------------------------------------
Kevin Kempter       -       Constent State 
A PostgreSQL Professional Services Company
          www.consistentstate.com
---------------------------------------------
--------------000702030300000909080400--