From: Jeff Davis <pgsql@j-davis.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: len@pdx.edu
Cc: len@cs.pdx.edu
Cc: pgsql-performance@postgresql.org
Subject: Re: Understanding histograms
Date: Wed, 30 Apr 2008 15:47:02 -0700
Message-ID: <1209595622.14025.112.camel@dogma.ljc.laika.com> (raw)
In-Reply-To: <18628.1209566591@sss.pgh.pa.us>
References: <4817FC00.50609@cs.pdx.edu>
<14251.1209532791@sss.pgh.pa.us>
<c5ee9b8a0804292332q32b468e3ga6b99e25b56c18c7@mail.gmail.com>
<18628.1209566591@sss.pgh.pa.us>
On Wed, 2008-04-30 at 10:43 -0400, Tom Lane wrote:
> > Instead I would expect an estimate of "rows=0" for values of const
> > that are not in the MCV list and not in the histogram.
>
> Surely that's not very sane? The MCV list plus histogram generally
> don't include every value in the table. IIRC the estimate for values
> not present in the MCV list is (1 - sum(MCV frequencies)) divided by
> (n_distinct - number of MCV entries), which amounts to assuming that
> all values not present in the MCV list occur equally often. The weak
> spot of course is that the n_distinct estimate may be pretty inaccurate.
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.
Essentially, this seems like using the histogram to extend the MCV list
such that, together, they represent all distinct values. This idea only
seems to help when the number of distinct values is greater than the
max size of MCVs, but less than the max size of MCVs plus histogram
bounds.
I'm not sure how much of a gain this is, because right now that could
be accomplished by increasing the statistics for that column (and
therefore all of your distinct values would fit in the MCV list). Also
the statistics aren't guaranteed to be perfectly up-to-date, so an
estimate of zero might be risky.
Regards,
Jeff Davis
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: pgsql@j-davis.com, tgl@sss.pgh.pa.us, len@pdx.edu, len@cs.pdx.edu
Subject: Re: Understanding histograms
In-Reply-To: <1209595622.14025.112.camel@dogma.ljc.laika.com>
* 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