From: Simon Riggs <simon@2ndquadrant.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: 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 19:54:52 +0100
Message-ID: <1192042492.4233.334.camel@ebony.site> (raw)
In-Reply-To: <470CD3E3.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>
On Wed, 2007-10-10 at 13:30 -0500, Kevin Grittner wrote:
> >>> 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?
But the planner doesn't work on probability. It works on a best-guess
selectivity, as known at planning time.
That's why dynamic planning was invented, which we don't do yet. Don't
hold your breath either.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
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: simon@2ndquadrant.com, Kevin.Grittner@wicourts.gov, tgl@sss.pgh.pa.us
Subject: Re: hashjoin chosen over 1000x faster plan
In-Reply-To: <1192042492.4233.334.camel@ebony.site>
* 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