Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread View on postgr.es
From: Peter Geoghegan <pg@bowt.ie>
To: Andres Freund <andres@anarazel.de>
Cc: Tomas Vondra <tomas@vondra.me>, Alexandre Felipe <o.alexandre.felipe@gmail.com>, Thomas Munro <thomas.munro@gmail.com>, Nazir Bilal Yavuz <byavuz81@gmail.com>, Robert Haas <robertmhaas@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Georgios <gkokolatos@protonmail.com>, Konstantin Knizhnik <knizhnik@garret.ru>, Dilip Kumar <dilipbalaut@gmail.com>
Date: 2026-03-10T20:57:35Z
Lists: pgsql-hackers

Attachments

On Fri, Feb 27, 2026 at 6:52 PM Andres Freund <andres@anarazel.de> wrote:
> This is a huge change. Is there a chance we can break it up into more
> manageable chunks?

Attached is v12, which has revisions that address most of your
feedback items. It also includes items that address problems that I
noticed during performance validation work.

Highlights:

* Substantial revisions that give table AMs and index AMs direct
control over batch layout -- without giving up on batch
recycling/caching. This is essentially what you (Andres) requested
because the design from v11 was not sufficiently AM agnostic. In
particular:

- Table AMs now control the size and layout of visibility information
(in practice heapam uses this to store per-item visibility state from
the visibility map).

- Index AMs have their own opaque state for things like sibling link
block numbers, avoiding the assumption that other index AMs supporting
amgetbatch will need to work like nbtree and hash as regards how they
navigate to the next index page/index keyspace associated with each
batch.

* No more read stream yielding. Numerous new patches from Andres are
now included, which helps with this. In particular, "WIP: read_stream:
Only increase distance when waiting for IO" fixes the problematic
regression in an adversarial query -- the one that prompted me to
invent yielding in the first place. As a result of all this, the read
stream callback added by the prefetching commit itself is now
substantially simpler than it was in v11.

* There are now a couple of extra patches created by breaking things
into more distinct commits. Namely, there's a new "heapam: Track heap
block in IndexFetchHeapData using xs_blk" commit, as well as a new
"Make IndexScanInstrumentation a pointer in executor scan nodes"
commit.

* Moreover, some commits now appear in a slightly different order,
prioritizing work closer to being committable; those commits now come
first.

* The SGML doc changes are now broken up a bit better: the commit that
adds basic amgetbatch functionality now includes SGML doc updates
describing the required API revisions. The SGML doc changes that
describe prefetching concepts now appear in the later prefetching
commit.

* New commit "Use simple hash for PrivateRefCount" addresses some of
the problems we were seeing with PrivateRefCount performance. This
generic optimization addresses an existing problem that would
otherwise be much worse with the index prefetching work in place.

* The new xs_read_extremal_only mechanism (which replaces selfuncs.c's
current use of VISITED_PAGES_LIMIT) will now hold on for longer -- up
to 3 index leaf pages can be read before we give up on the scan. The
number of pages that we're willing to read before giving up is now
under the control of the selfuncs.c caller. This addresses concerns
that Tom had about selfuncs.c giving up too easily, for example when
the rightmost/leftmost leaf page happens to have very few items.

* v12 fixes a ~5% regression in bitmap index scans, which was tied to
the fact that we were naively using retail pallocs to allocate new
batches in all cases. The fix gives amgetbitmap routines the ability
to use their own batch cache mechanism, similar to the one used during
amgetbatch scans. In practice, btgetbitmap and hashgetbitmap scans
will never need to do more than one palloc to allocate a batch; they
can continually reuse the same batch. This relies on the fact that
amgetbitmap routines don't actually need to hold open more than a
single batch at a time/maintain a batch ring buffer (just like master
manages currPos state).

* v12 addresses certain performance issues with nestloop joins and
merge joins by no longer dropping the scan's heap page buffer pin on
rescan. This keeps regressions with fully cached workloads to an
absolute minimum, and seems to have no real downside.

However, I have NOT yet acted on a few feedback items from Andres:

* I still don't know what Andres meant about requiring table AMs to
free batch index page buffer pins representing a modularity violation.
I don't see how we can reasonably avoid it while still preserving the
guarantees needed to safely drop buffer pins eagerly during index-only
scans that require prefetching.

* I'm also not at all sure what Andres meant about index AMs like hash
not holding onto their own buffer pins, given that prefetching uses a
read stream sensitive to the number of buffer pins the backend holds.

I understand the general concern about interfering with the read
stream, I think, but I don't see how imposing such a strict rule on
index AMs, which have always been allowed to do this, can be practical
(though note that nbtree doesn't actually hold on to any buffer pins
for internal purposes, and hasn't since Postgres 18 commit 1bd4bc85,
so at least nbtree can be relied on to never do this). I asked Andres
about these 2 points not too long ago [1], and await further feedback
on those questions.

* Separate from the 2 closely related items about batch buffer pins, I
have not yet made most of the changes Andres requested to the "Use
ExecSetTupleBound hint during index scans" commit -- just because I
haven't had time. v11 of the patch set hasn't applied cleanly in about
a week, so I didn't want to further delay posting v12.

Thanks

[1] https://postgr.es/m/CAH2-Wznxu+AFz-EBOG-XiRA_R3nXLp45NEiGSD3ebx3h=OKPAw@mail.gmail.com

--
Peter Geoghegan

Commits

  1. read stream: Split decision about look ahead for AIO and combining

  2. read_stream: Only increase read-ahead distance when waiting for IO

  3. aio: io_uring: Trigger async processing for large IOs

  4. heapam: Keep buffer pins across index scan resets.

  5. heapam: Track heap block in IndexFetchHeapData.

  6. Move heapam_handler.c index scan code to new file.

  7. Rename heapam_index_fetch_tuple argument for clarity.

  8. Optimize fast-path FK checks with batched index probes

  9. read_stream: Prevent distance from decaying too quickly

  10. read_stream: Issue IO synchronously while in fast path

  11. bufmgr: Return whether WaitReadBuffers() needed to wait

  12. aio: io_uring: Allow IO methods to check if IO completed in the background

  13. bufmgr: Make UnlockReleaseBuffer() more efficient

  14. Add fake LSN support to hash index AM.

  15. Make IndexScanInstrumentation a pointer in executor scan nodes.

  16. Use fake LSNs to improve nbtree dropPin behavior.

  17. Move fake LSN infrastructure out of GiST.

  18. Use simplehash for backend-private buffer pin refcounts.

  19. nbtree: Avoid allocating _bt_search stack.

  20. bufmgr: Fix use of wrong variable in GetPrivateRefCountEntrySlow()

  21. Conditional locking in pgaio_worker_submit_internal

  22. Reduce ExecSeqScan* code size using pg_assume()

  23. Fix rare bug in read_stream.c's split IO handling.

  24. Remove HeapBitmapScan's skip_fetch optimization

  25. Optimize nbtree backwards scans.

  26. Fix multiranges to behave more like dependent types.

  27. Add EXPLAIN (MEMORY) to report planner memory consumption

  28. Optimize nbtree backward scan boundary cases.

  29. Increment xactCompletionCount during subtransaction abort.

  30. Add nbtree Valgrind buffer lock checks.

  31. Add nbtree high key "continuescan" optimization.

  32. Reduce pinning and buffer content locking for btree scans.

  33. Teach btree to handle ScalarArrayOpExpr quals natively.