Re: Understanding histograms

Len Shapiro <len@[EMAIL PROTECTED]
> 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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance