Re: Query tuning help

Hello

please, send EXPLAIN ANALYZE output instead.

Regards

Pavel Stehule

2011/10/11 CS DBA <cs_dba(at)consistentstate(dot)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...
>
>
>
>
>
>
>
>
>
>
>
>
> --
> ---------------------------------------------
> Kevin Kempter - Constent State
> A PostgreSQL Professional Services Company
> www.consistentstate.com
> ---------------------------------------------