To: Jeff Davis <pgsql@j-davis.com>
cc: len@pdx.edu, len@cs.pdx.edu, pgsql-performance@postgresql.org
Subject: Re: Understanding histograms 
In-reply-to: <1209595622.14025.112.camel@dogma.ljc.laika.com> 
References: <4817FC00.50609@cs.pdx.edu> <14251.1209532791@sss.pgh.pa.us>
	<c5ee9b8a0804292332q32b468e3ga6b99e25b56c18c7@mail.gmail.com>
	<18628.1209566591@sss.pgh.pa.us>
	<1209595622.14025.112.camel@dogma.ljc.laika.com>
Comments: In-reply-to Jeff Davis <pgsql@j-davis.com>
	message dated "Wed, 30 Apr 2008 15:47:02 -0700"
Date: Wed, 30 Apr 2008 19:17:44 -0400
Message-ID: <26230.1209597464@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Jeff Davis <pgsql@j-davis.com> writes:
> On Wed, 2008-04-30 at 10:43 -0400, Tom Lane wrote:
>> Surely that's not very sane?  The MCV list plus histogram generally
>> don't include every value in the table.

> My understanding of Len's question is that, although the MCV list plus
> the histogram don't include every distinct value in the general case,
> they do include every value in the specific case where the histogram is
> not full.

I don't believe that's true.  It's possible that a small histogram means
that you are seeing every value that was in ANALYZE's sample, but it's
a mighty long leap from that to the assumption that there are no other
values in the table.  In any case that seems more an artifact of the
implementation than a property the histogram would be guaranteed to
have.

> ... the statistics aren't guaranteed to be perfectly up-to-date, so an
> estimate of zero might be risky.

Right.  As a matter of policy we never estimate less than one matching
row; and I've seriously considered pushing that up to at least two rows
except when we see that the query condition matches a unique constraint.
You can get really bad join plans from overly-small estimates.

			regards, tom lane