Subject: Re: hashjoin chosen over 1000x faster plan
From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	pgsql-performance@postgresql.org
In-Reply-To: <20980.1192039650@sss.pgh.pa.us>
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> <20980.1192039650@sss.pgh.pa.us>
Content-Type: text/plain
Date: Wed, 10 Oct 2007 20:01:03 +0100
Message-Id: <1192042863.4233.340.camel@ebony.site>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Wed, 2007-10-10 at 14:07 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Basically the planner doesn't ever optimise for the possibility of the
> > never-executed case because even a single row returned would destroy
> > that assumption. 
> 
> It's worse than that: the outer subplan *does* return some rows.
> I suppose that all of them had NULLs in the join keys, which means
> that (since 8.1 or so) nodeMergejoin discards them as unmatchable.
> Had even one been non-NULL the expensive subplan would have been run.

Yup

> This seems like too much of a corner case to justify adding a lot of
> machinery for.

Well, I thought about that and it is pretty common to have root classes
left outer joined to sub-classes, if you are using the One Table per
Subclass object-relational mapping. The joined-subclass mapping within
Hibernate implements this.

Not everybody uses that, but it is an option some people take in some
circumstances. So we should keep it on our radar if we want to extend
our for support complex applications.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com