Re: Problems with get_actual_variable_range's VISITED_PAGES_LIMIT

On Tue, Feb 10, 2026 at 4:26 AM Jakub Wartak
<jakub(dot)wartak(at)enterprisedb(dot)com> wrote:
> Well because those below are the timing literally from the case that caused
> this back then
> (not kidding):
> Planning Time: 1620687.653 ms
> Execution Time: 970.716 ms

To be fair, the fact that selfuncs.c is very deliberate about setting
LP_DEAD bits in index pages these days was likely a big help. AFAICT,
the only problem that remains is with VISITED_PAGES_LIMIT itself,
which doesn't seem very well attuned to the costs that we need to keep
reasonably well bounded.

> I just worry that if get_actual_variable_range() starts reporting much worse
> estimates what do we do then? Well maybe, we'll have pg_plan_advice then,
> or maybe the depth (time?) of search should be somehow bound to the column's
> statistics_target too, but on second thought it looks like it should be more
> property of the query itself (so maybe some GUC?)

The purpose of get_actual_variable_range is to get the extremal value
in the index -- a single value from a single tuple. If we can't manage
that by reading only 2 or 3 pages, then I see no good reason to
believe we can by reading many more pages.

In other words, we're perfectly justified in making a soft expectation
that it'll require very little work to get an extremal value. But once
we notice that our soft assumption doesn't hold, all bets are off --
we're then justified in giving up relatively quickly. Having several
contiguous pages that are completely empty (or empty of
non-LP_DEAD-marked tuples) at the extreme leftmost or rightmost en of
the index is absolutely a pathological case. It strongly suggests a
queue-like workload of the kind that my testing shows that
VISITED_PAGES_LIMIT can't deal with sensibly.

get_actual_variable_range exists to ascertain information about a
particular index. To me, it makes perfect sense that a mechanism such
as this would work in terms of costs paid on the index AM side. (The
heap fetches matter a great deal too, of course, but it's reasonable
to use index costs as a proxy for heap/table AM costs -- but it's not
reasonable to use table/heap AM costs as a proxy for index AM costs.
It doesn't work both ways.)

--
Peter Geoghegan