Subject: Re: hashjoin chosen over 1000x faster plan
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>
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>
Content-Type: text/plain
Date: Wed, 10 Oct 2007 20:52:25 +0100
Message-Id: <1192045945.4233.351.camel@ebony.site>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Wed, 2007-10-10 at 14:35 -0500, Kevin Grittner wrote:
> >>> On Wed, Oct 10, 2007 at  1:54 PM, in message
> <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