Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 8CF919FB477 for ; Fri, 21 Sep 2007 14:53:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 01019-03 for ; Fri, 21 Sep 2007 14:53:44 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id D76879FB40E for ; Fri, 21 Sep 2007 14:53:45 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l8LHreW6013645; Fri, 21 Sep 2007 13:53:40 -0400 (EDT) To: Simon Riggs cc: Csaba Nagy , postgres performance list Subject: Re: Searching for the cause of a bad plan In-reply-to: <1190396352.4202.109.camel@ebony.site> References: <1190369024.4661.173.camel@PCD12478> <6175.1190390913@sss.pgh.pa.us> <1190396352.4202.109.camel@ebony.site> Comments: In-reply-to Simon Riggs message dated "Fri, 21 Sep 2007 18:39:12 +0100" Date: Fri, 21 Sep 2007 13:53:40 -0400 Message-ID: <13644.1190397220@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/377 X-Sequence-Number: 26922 Simon Riggs writes: > On Fri, 2007-09-21 at 12:08 -0400, Tom Lane wrote: >> The reason you get a bad plan is that this rowcount estimate is so far >> off: > That's true, but its not relevant, Yes it is --- the reason it wants to use a hashjoin instead of a nestloop is exactly that it thinks the loop would iterate too many times. (Ten is already too many in this case --- if it had estimated five rows out of the join, it'd have gone with the nestloop, since the cost estimate difference at the top level is less than 2x.) regards, tom lane