From: Tom Lane <tgl@sss.pgh.pa.us>
To: len@cs.pdx.edu
Cc: pgsql-performance@postgresql.org
Subject: Re: Understanding histograms
Date: Wed, 30 Apr 2008 01:19:51 -0400
Message-ID: <14251.1209532791@sss.pgh.pa.us> (raw)
In-Reply-To: <4817FC00.50609@cs.pdx.edu>
References: <4817FC00.50609@cs.pdx.edu>
Len Shapiro <len@cs.pdx.edu> writes:
> 1. Why does Postgres come up with a negative n_distinct?
It's a fractional representation. Per the docs:
> stadistinct float4 The number of distinct nonnull data values in the column. A value greater than zero is the actual number of distinct values. A value less than zero is the negative of a fraction of the number of rows in the table (for example, a column in which values appear about twice on the average could be represented by stadistinct = -0.5). A zero value means the number of distinct values is unknown
> 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.
> For example, if I run the query
> EXPLAIN SELECT * from sailors where rank = -1000;
> Postgres still gives an estimate of "row=2".
I'm not sure what estimate you'd expect instead? The code has a built in
assumption that no value not present in the MCV list can be more
frequent than the last member of the MCV list, so it's definitely not
gonna guess *more* than 2.
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@cs.pdx.edu
Subject: Re: Understanding histograms
In-Reply-To: <14251.1209532791@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