On 10/11/2011 12:02 PM, Pavel Stehule wrote:
Hello please, send EXPLAIN ANALYZE output instead. Regards Pavel Stehule 2011/10/11 CS DBA <cs_dba@xxxxxxxxxxxxxxxxxxx>: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... Explain Analyze: Hash Join (cost=154.46..691776.11 rows=10059626 width=100) (actual time=5.191..37551.360 rows=10063432 loops=1) Hash Cond: (a.order_id = o.order_id) -> Seq Scan on cust_acct a (cost=0.00..540727.26 rows=10059626 width=92) (actual time=0.022..18987.095 rows=10063432 loops=1) -> Hash (cost=124.76..124.76 rows=2376 width=12) (actual time=5.135..5.135 rows=2534 loops=1) -> Seq Scan on cust_orders o (cost=0.00..124.76 rows=2376 width=12) (actual time=0.011..2.843 rows=2534 loops=1) Total runtime: 43639.105 ms (6 rows)
--
---------------------------------------------
Kevin Kempter - Constent State
A PostgreSQL Professional Services Company
www.consistentstate.com
---------------------------------------------
|
