Re: a wrong index choose when statistics is out of date

On Mon, 4 Mar 2024 at 19:20, Andrei Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru> wrote:
> Could we use the trick with the get_actual_variable_range() to find some
> reason and extrapolate histogram data out of the boundaries when an
> index shows us that we have min/max outside known statistics?
> Because it would be used for the values out of the histogram, it should
> only add an overhead with a reason.

I think, in theory, it would be possible to add a function similar to
get_actual_variable_range() for equality clauses, but I'd be worried
about the overheads of doing so. I imagine it would be much more
common to find an equality condition with a value that does not fit in
any histogram/MCV bucket. get_actual_variable_range() can be quite
costly when there are a large number of tuples ready to be vacuumed,
and having an equivalent function for equality conditions could appear
to make the planner "randomly" slow without much of an explanation as
to why.

I think we still do get some complaints about
get_actual_variable_range() despite it now using
SnapshotNonVacuumable. It used to be much worse with the snapshot
type it used previous to what it uses today. IIRC it took a few
iterations to get the performance of the function to a level that
seems "mostly acceptable".

David