Subject: Re: index prefetching

From: Peter Geoghegan <[email-masked]>
Date: Fri, 30 Jan 2026 19:18:02 -0500

On Sun, Jan 18, 2026 at 6:51 PM Peter Geoghegan <[email-masked]> wrote:
> Attached is v8. A lot has changed in just the past week. We're feeling
> optimistic about getting I/O prefetching into Postgres 19, and so
> we've decided to fully commit ourselves to that.

Attached is v9.

v8 just stopped applying against HEAD. But there have been a few
notable developments since v8.

> Dropping leaf page buffer pins during index-only scans
> ------------------------------------------------------
>
> I realized (following a point made by Matthias van de Meent during a
> private call) that it simply isn't necessary for index pages to hold
> on to buffer pins, in general. We haven't actually done that with
> nbtree plain index scans with MVCC snapshots since 2015's commit
> 2ed5b87f, which added what we now call nbtree's dropPin optimization.
> What if we could find a way to teach *every* amgetbatch-supporting
> index AM to do the same trick for *all* scans (barring non-MVCC
> snapshot scans)? Including index-only scans and scans of unlogged
> relations? Then we'd have zero chance of unintended interactions with
> the read stream; there'd simply be no extra buffer pins that might
> confuse the read stream in the first place!

Changes in v9, compared to v8:

* It turns out that this "always set all items all-visible up front"
approach can be too aggressive with certain kinds of queries -- we
need to be less aggressive with dropping leaf page pins during index
only scans.

It's always correct, but it wastes cycles in certain kinds of queries.
Things like nestloop anti-joins with an inner index scan will
generally only need to access the first item's VM info; wasting cycles
on eagerly setting all the cached VM info up front regresses such
queries unacceptably (like maybe 30%; way too much).

Tomas wrote code that works around the problem, by only dropping a
leaf page buffer pin (during an index only scan) when all of the
batch's items visibility info has been looked by the VM. At the same
time we still make strong guarantees about holding on to no more than
one such pin at a time. Which is all we really need to keep the pin
resource management problems (read stream pin interactions) simple
during scans that use prefetching.

* Separately, there's a new experimental patch that passes down a hint
from ExecSetTupleBound to index scans.

This allows us to be cleverer about avoiding starting a read stream in
queries that use nestloop joins with inner index scans that can end
early/on the first tuple (and also with simple ORDER BY ... LIMIT N
index scans). In other words, it fixes regressions with queries rather
like the ones we were concerned about with the previous VM caching
change. Though this second enhancement isn't just helpful when it's an
index-only scan on the inner side.

* Much simpler heapam_batch_getnext_tid logic, particularly for
dealing with changes in scan direction for a scrollable cursor.

The new design makes a clean separation between the need to
invalidate/disable our read stream due to a tuple-level change in the
scan's direction, and the need to deal with a change in scan direction
that actually crosses a boundary between 2 adjoining batches/index
pages. In the former case we need to avoid allowing the scan to read
the now-wrong heap blocks from the read stream, whereas in the latter
case we need to be careful about keeping the batch ring buffer state
consistent.

Notice that these are 2 distinct (albeit conceptually close) problems.
Cursor scans that (for whatever reason) don't use a read stream never
have the first problem, but might still run into the second. OTOH a
scan can easily need to deal with invalidating this way, without ever
getting as far as crossing a batch boundary (the scan could just end,
the scan direction to change back, and then we don't have to change
anything about batchringbuf state).

Also improved the ring buffer data structure. It now uses uint8
offsets that wrap as needed. This is more idiomatic, and seems easier
to test.

selfuncs.c problem
------------------

Also worth noting that we recently found a problem with selfuncs.c:
the VISITED_PAGES_LIMIT stuff in selfuncs.c is broken right now. v9
tears that code out, pending adding back a real fix (earlier versions
of the patch had VISITED_PAGES_LIMIT, but it didn't work).

The underlying problem is that the existing VISITED_PAGES_LIMIT design
is incompatible with our new table_index_getnext_slot interface. The
new interface doesn't stop scanning until it is able to at least
return 1 tuple. But VISITED_PAGES_LIMIT was invented precisely because
get_actual_variable_endpoint's index scans took far too long, even
though they're only ever required to locate 1 tuple. So that just
won't work.

We'll need to invent some kind of API that directly acknowledges the
needs of the selfuncs.c caller, and others like it. Doing it in an
ad-hoc way just doesn't seem possible anymore. That will have to wait
for the next revision, though (or the one after that).

-- 
Peter Geoghegan

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 9 Feb 2026 17:44:11 -0500

> Attached is v9.

Attached is v10. There are 2 major areas of improvement in this latest revision:

1. We have enhanced the read stream callback (heapam_getnext_stream,
which is added by
v10-0005-Add-prefetching-to-index-scans-using-batch-inter.patch),
making it yield at key intervals. When we yield, we temporarily
suspend prefetching -- but only for long enough to give the scan the
opportunity to return one more matching tuple (don't confuse yielding
with pausing; we do both, but the goals are rather different in each
case).

Yielding like this keeps the scan responsive during prefetching: the
scan should never go too long without returning at least one tuple
(except when that just isn't possible at all). Testing has shown that
keeping the scan responsive in this sense is particularly important
during scans that appear in "ORDER BY ... LIMIT N" queries, as well as
during scans that feed into certain merge joins. IOW, it is
particularly important that we "keep the scan responsive" whenever it
has the potential to allow the scan to shut down before it has
performed work that turns out to be unnecessary (though it also seems
to have some benefits even when that isn't the case).

There is a complex trade-off here: we want to yield when we expect
some benefit from doing so. But we don't want to yield when doing so
risks compromising the read stream's ability to maintain an adequate
prefetch distance. There are comments in heapam_getnext_stream that
describe the theory in more detail. There are heuristics that were
derived using adversarial benchmarking/stress-testing. I'm sure that
they need more work, but this does seem like roughly the right idea.
Note that we now test whether the scan's read stream is using its
fast-path mode (read stream uses this when the scan reads heap pages
that are all cached).

2. A new patch (v10-0007-Limit-get_actual_variable_range-to-scan-one-inde.patch)
compensates for the fact that the main prefetching commit removes
get_actual_variable_range's VISITED_PAGES_LIMIT mechanism. Since
VISITED_PAGES_LIMIT cannot easily be ported over to the new table AM
interface selfuncs.c now uses.

> selfuncs.c problem

The new patch deals with the problem in a completely different way,
and at a completely different layer: it adds a new
IndexScanDescData.xs_read_extremal_only field, set only by
get_actual_variable_range. When nbtree sees that the field has been
set, it gives up after scanning only one leaf page (the page that
contains extremal values that are of interest to
get_actual_variable_range). Note that we completely stop caring about
heap page fetches with this new approach.

There are good reasons to believe that the new
IndexScanDescData.xs_read_extremal_only approach will solve existing
problems with VISITED_PAGES_LIMIT. Recent benchmarking from Mark
Callaghan [1] (which I've independently recreated with my own minimal
test suite) shows that VISITED_PAGES_LIMIT becomes completely
ineffective, once we reach a tipping point where many index tuples at
one end of the index all have their LP_DEAD bit set.

I'm going to start a new thread to discuss the issues in this area
later today. I'm aiming to fix an existing, independent issue in this
new patch, so it makes sense to discuss it on a completely separate
thread.

[1] https://smalldatum.blogspot.com/2026/01/cpu-bound-insert-benchmark-vs-postgres.html
--
Peter Geoghegan