Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 94BAB632530 for ; Fri, 30 Jul 2010 11:04:03 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 01398-05 for ; Fri, 30 Jul 2010 14:03:54 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 0B7C06324F0 for ; Fri, 30 Jul 2010 11:03:55 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o6UE3rov025535; Fri, 30 Jul 2010 10:03:53 -0400 (EDT) To: Peter Hussey cc: pgsql-performance Subject: Re: Questions on query planner, join types, and work_mem In-reply-to: References: <18551.1280275502@sss.pgh.pa.us> Comments: In-reply-to Peter Hussey message dated "Wed, 28 Jul 2010 21:30:02 -0700" Date: Fri, 30 Jul 2010 10:03:52 -0400 Message-ID: <25534.1280498632@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201007/449 X-Sequence-Number: 39838 Peter Hussey writes: > Using the default of 1MB work_mem, the planner chooses a hash join plan : > "Hash Left Join (cost=252641.82..11847353.87 rows=971572 width=111) (actual > time=124196.670..280461.604 rows=968080 loops=1)" > ... > For the same default 1MB work_mem, a nested loop plan is better > "Nested Loop Left Join (cost=8.27..15275401.19 rows=971572 width=111) > (actual time=145.015..189957.023 rows=968080 loops=1)" > ... Hm. A nestloop with nearly a million rows on the outside is pretty scary. The fact that you aren't unhappy with that version of the plan, rather than the hash, indicates that the "object" table must be fully cached in memory, otherwise the repeated indexscans would be a lot slower than this: > " -> Index Scan using uq_object on object obj (cost=0.00..3.51 rows=1 > width=95) (actual time=0.168..0.170 rows=1 loops=968080)" > " Index Cond: ((sd.lsid)::text = (obj.objecturi)::text)" My take on it is that the estimate of the hash plan's cost isn't bad; what's bad is that the planner is mistakenly estimating the nestloop as being worse. What you need to do is adjust the planner's cost parameters so that it has a better idea of the true cost of repeated index probes in your environment. Crank up effective_cache_size if you didn't already, and experiment with lowering random_page_cost. See the list archives for more discussion of these parameters. regards, tom lane