From: CS DBA <cs_dba@consistentstate.com>
To: Szymon Guz <mabewlun@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Query tuning help
Date: Tue, 11 Oct 2011 12:31:07 -0600
Message-ID: <4E948B6B.8000905@consistentstate.com> (raw)
In-Reply-To: <CAFjNrYt2JP-w731bkWhm_b3--mw8X+VD5PJ=pP5b_K=WGRucGQ@mail.gmail.com>
References: <4E948263.8040305@consistentstate.com>
<CAFjNrYt2JP-w731bkWhm_b3--mw8X+VD5PJ=pP5b_K=WGRucGQ@mail.gmail.com>
On 10/11/2011 12:03 PM, Szymon Guz wrote:
>
>
> On 11 October 2011 19:52, CS DBA <cs_dba@consistentstate.com
> <mailto: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
---------------------------------------------
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, mabewlun@gmail.com
Subject: Re: Query tuning help
In-Reply-To: <4E948B6B.8000905@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