Skip site navigation (1) Skip section navigation (2)

Re: Query tuning help

From: CS DBA <cs_dba(at)consistentstate(dot)com>
To: Szymon Guz <mabewlun(at)gmail(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Query tuning help
Date: 2011-10-11 18:31:07
Message-ID: 4E948B6B.8000905@consistentstate.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 10/11/2011 12:03 PM, Szymon Guz wrote:
>
>
> On 11 October 2011 19:52, CS DBA <cs_dba(at)consistentstate(dot)com 
> <mailto:cs_dba(at)consistentstate(dot)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
---------------------------------------------

In response to

pgsql-performance by date

Next:From: Pavel StehuleDate: 2011-10-11 18:59:51
Subject: Re: Query tuning help
Previous:From: Anibal David AcostaDate: 2011-10-11 18:20:12
Subject: Re: should i expected performance degradation over time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group