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-12T19:44:32Z
Lists: pgsql-hackers

Attachments

On Tue, Mar 10, 2026 at 6:29 PM Andres Freund <andres@anarazel.de> wrote:
> This seems pretty unrelated to my concern.  I have a problem with the fact
> that heapam knows which specific resources need to be held (&released) to
> prevent concurrency issues during an index only scan.  I am *NOT* concerned
> with there needing to be a pin and heapam triggering the release of that
> resource.

Attached v13 does things this way.

All index AMs that use amgetbatch must register a new amreleasebatch
callback in v13. This callback releases any buffer pin held as an
interlock against unsafe concurrent TID recycling by VACUUM -- at
least for nbtree and hash. The buffer itself is now stored in the
portion of each batch used as opaque index AM state, so nothing stops
other index AMs that support amgetbatch in the future from using a
different kind of interlock, from holding multiple pins instead of
just one, etc.

We have a generic policy that determines (at the level of each scan)
whether an interlock is required at all. If an interlock isn't
required, index AMs will drop both their lock and pin together.
Otherwise, they will only drop the lock, and only drop the
pin/abstract interlock when the new amreleasebatch callback is called
by the table AM. Our policy is to assume that we don't need a TID
recycling interlock except during index-only scans and non-MVCC scans
(regardless of the table AM and index AM involved).

Separately, I removed the requirement that amgetbatch index AMs
support Valgrind instrumentation requests like those nbtree has long
supported. And I removed all such instrumentation from the hash index
AM.

> > Is this purely because of the potential to
> > disrupt the read stream's management of the backend's buffer pin
> > limit?
>
> I'm not particularly bothered by a small number of extra buffer pins,
> particularly for AMs other than nbtree. They won't cause issues in any real
> world setups.

Okay. Then I don't think that we need to do anything here. As you
know, nbtree never holds buffer pins for its own internal purposes.
The hash index AM continues to hang on to up to 2 extra buffer pins
for its own purposes, just like on master.

--
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.