Message-Id: <470CD3E3.EE98.0025.0@wicourts.gov>
Date: Wed, 10 Oct 2007 13:30:13 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Simon Riggs" <simon@2ndquadrant.com>,
	"Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: hashjoin chosen over 1000x faster plan
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>
In-Reply-To: <20980.1192039650@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

>>> On Wed, Oct 10, 2007 at  1:07 PM, in message <20980.1192039650@sss.pgh.=
pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:=20
> 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.=20
>=20
> 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.
=20
Well, this query is run tens of thousands of times per day by our web
application; less than one percent of those runs would require the
subplan.  (In my initial post I showed counts to demonstrate that 1%
of the rows had a non-NULL value and, while I wouldn't expect the
planner to know this, these tend to be clustered on a lower
percentage of cases.)  If the philosophy of the planner is to go for
the lowest average cost (versus lowest worst case cost) shouldn't it
use the statistics for to look at the percentage of NULLs?
=20
-Kevin
=20