From: Simon Riggs <simon@2ndquadrant.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: hashjoin chosen over 1000x faster plan
Date: Wed, 10 Oct 2007 20:52:25 +0100
Message-ID: <1192045945.4233.351.camel@ebony.site> (raw)
In-Reply-To: <470CE34D.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>
On Wed, 2007-10-10 at 14:35 -0500, Kevin Grittner wrote:
> >>> On Wed, Oct 10, 2007 at 1:54 PM, in message
> <camel@ebony.site/">1192042492.4233.334.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.com>
> wrote:
> >
> > But the planner doesn't work on probability. It works on a best-guess
> > selectivity, as known at planning time.
>
> The point I'm trying to make is that at planning time the
> pg_statistic row for this "Charge"."reopHistSeqNo" column showed
> stanullfrac as 0.989; it doesn't seem to have taken this into account
> when making its guess about how many rows would be joined when it was
> compared to the primary key column of the "CaseHist" table. I'm
> suggesting that it might be a good thing if it did.
Understood, it would be a good thing if it did.
It's more complex than you think:
The fast plan is an all-or-nothing plan. It is *only* faster when the
number of matched rows is zero. You know it is zero, but currently the
planner doesn't, nor is it able to make use of the information when it
has it, half thru execution. Even if we could work out the high
probability of it being zero, we would still be left with the decision
of whether to optimise for the zero or for the non-zero.
--
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: <1192045945.4233.351.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