Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 349059FB535 for ; Wed, 10 Oct 2007 19:09:09 -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 23904-08 for ; Wed, 10 Oct 2007 19:08:58 -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 03F129FB47F for ; Wed, 10 Oct 2007 19:09:03 -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 l9AM8x5a025225; Wed, 10 Oct 2007 18:08:59 -0400 (EDT) To: "Kevin Grittner" cc: "Simon Riggs" , pgsql-performance@postgresql.org Subject: Re: hashjoin chosen over 1000x faster plan In-reply-to: <470CF450.EE98.0025.0@wicourts.gov> References: <470B99BE.EE98.0025.0@wicourts.gov> <1191997904.4233.125.camel@ebony.site> <470C9813.EE98.0025.0@wicourts.gov> <1192035955.4233.306.camel@ebony.site> <1192035955.4233.306.camel@ebony.site> <20980.1192039650@sss.pgh.pa.us> <470CD3E3.EE98.0025.0@wicourts.gov> <1192042492.4233.334.camel@ebony.site> <470CE34D.EE98.0025.0@wicourts.gov><470CE34D.EE98.0025.0@wicourts.gov> <23650.1192048377@sss.pgh.pa.us> <470CF450.EE98.0025.0@wicourts.gov> Comments: In-reply-to "Kevin Grittner" message dated "Wed, 10 Oct 2007 15:48:32 -0500" Date: Wed, 10 Oct 2007 18:08:59 -0400 Message-ID: <25224.1192054139@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/116 X-Sequence-Number: 27138 "Kevin Grittner" writes: > I'm not sure why it looks at the slow option at all; it seems like a remain= > ing weakness in the OUTER JOIN optimizations. I think that comes mostly from the fact that you've got non-nullable targetlist entries in the definition of the CaseTypeHistEvent view. Those prevent that view from being flattened into the upper query when it's underneath an outer join, because the current variable-evaluation rules provide no other way to ensure that the values are forced NULL when they need to be. This is something we should fix someday but don't hold your breath waiting --- it's likely to take some pretty fundamental rejiggering of the planner's handling of Vars. regards, tom lane