From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: hashjoin chosen over 1000x faster plan
Date: Wed, 10 Oct 2007 13:30:13 -0500
Message-ID: <470CD3E3.EE98.0025.0@wicourts.gov> (raw)
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>
<1192035955.4233.306.camel@ebony.site>
<20980.1192039650@sss.pgh.pa.us>
>>> 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:
> 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.
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?
-Kevin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, simon@2ndquadrant.com, tgl@sss.pgh.pa.us
Subject: Re: hashjoin chosen over 1000x faster plan
In-Reply-To: <470CD3E3.EE98.0025.0@wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox