Re: Estimating number of distinct values.

Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> writes:
> I will be pleased if somebody (first of all Robert) can comment me
> "strange" results of distinct values estimation.

Estimating the number of distinct values from a small sample is a hard
problem; every algorithm is going to blow it in some cases.

> In my case there are no null values:
> totalrows = 48980014
> samplerows = 30000
> ndistinct = 29135
> nmultiple = 800

You seem to be using the default statistics target. Possibly raising that
would give better answers for this column.

> May be instead of sampling-based estimation use streaming
> based estimation, for example HyperLogLog algorithm already present in
> Postgres?

Maybe, but I'd be really surprised if HLL were any sort of magic bullet.
I think it's intended to make an ndistinct estimate with just a small
amount of state preserved as rows are scanned, which is an admirable
goal but not one that ANALYZE particularly needs. Adopting such a
constraint when we don't need it does not sound like a recipe for
getting a better final answer.

regards, tom lane