On 11 October 2011 19:52, CS DBA <cs_...@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? - show us the table structure, together with index definitions regards Szymon
