Re: Row number estimation...

<marin(at)kset(dot)org> writes:
> Yesterday I was trying to calculate by hand the row number estimates using
> the examples from
> http://www.postgresql.org/docs/9.3/static/row-estimation-examples.html
> It is usually correct, except when you try to get an estimate near the
> first (or last) histogram bound. Let me demonstrate with an example:
> ...
> As no statistics are collected for the index (as stated in the
> pg_statistic documentation "No entry is made for an ordinary non-expression
> index column, however, since it would be redundant with the entry for the
> underlying table column.") it is mystery to me how these estimates are
> calculated. I tried to look at the code (my guess was that I could find it
> in btcostestimate) but I wasn't able to figure it out.

scalarineqsel() is where to look.

> Can somebody explain how are the estimates calculated when there is an
> index involved?

If the comparison value is outside the range recorded in the histogram,
and there's a suitable index available, the planner uses the index to
find out the actual column min or max rather than believing the histogram
completely. See get_actual_variable_range().

Possibly this behavior ought to be mentioned in the docs ...

regards, tom lane