From: Tom Lane <tgl@sss.pgh.pa.us>
To: len@pdx.edu
Cc: len@cs.pdx.edu
Cc: pgsql-performance@postgresql.org
Subject: Re: Understanding histograms
Date: Wed, 30 Apr 2008 10:43:11 -0400
Message-ID: <18628.1209566591@sss.pgh.pa.us> (raw)
In-Reply-To: <c5ee9b8a0804292332q32b468e3ga6b99e25b56c18c7@mail.gmail.com>
References: <4817FC00.50609@cs.pdx.edu>
<14251.1209532791@sss.pgh.pa.us>
<c5ee9b8a0804292332q32b468e3ga6b99e25b56c18c7@mail.gmail.com>
"Len Shapiro" <lenshap@gmail.com> writes:
> I asked about n_distinct, whose documentation reads in part "The
> negated form is used when ANALYZE believes that the number of distinct
> values is likely to increase as the table grows". and I asked about
> why ANALYZE believes that the number of distinct values is likely to
> increase. I'm unclear why you quoted to me the documentation on
> stadistinct.
n_distinct is just a view of stadistinct. I assumed you'd poked around
in the code enough to know that ...
>>> The "rows=2" estimate makes sense when const = 1 or 5, but it makes no
>>> sense to me for other values of const not in the MVC list.
>>
>> I'm not sure what estimate you'd expect instead?
> 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.
> Where in the source is the code that manipulates the histogram?
commands/analyze.c builds it, and most of the estimation with it
happens in utils/adt/selfuncs.c.
regards, tom lane
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: tgl@sss.pgh.pa.us, len@pdx.edu, len@cs.pdx.edu
Subject: Re: Understanding histograms
In-Reply-To: <18628.1209566591@sss.pgh.pa.us>
* 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