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-02-24T18:13:25Z
Lists: pgsql-hackers

Attachments

On Tue, Feb 17, 2026 at 2:27 PM Andres Freund <andres@anarazel.de> wrote:
> On 2026-02-17 12:16:23 -0500, Peter Geoghegan wrote:
> > > Note that there is pretty much *no* readhead, because the yields happen more
> > > frequently than a io_combine_limit sized IO can be formed.
> >
> > ISTM that we need the yields to better cooperate with whatever's
> > happening on the read stream side.
>
> Plausible.  It could be that we could get away with controlling the rampup to
> be slower in potentially problematic cases, without needing the yielding, but
> not sure.

Attached is v11, which makes the read stream yielding mechanism better
cooperate with index prefetching, so as to avoid interefering with
io_combine_limit. This should deal with the odd performance that you
complained about. See
v11-0006-Introduce-read_stream_-pause-resume-yield.patch (and the
later prefetching patch
v11-0007-Add-heapam-index-scan-I-O-prefetching.patch) for details.

The whole idea of measuring "batch distance" is gone in this version,
though we do still only consider whether now is a good time to yield
at "batch boundaries". We always refuse yield on the first few batches
of the scan, so the idea of caring about batch boundaries is still
there, albeit in a much more limited form.

I think that the read stream aspects of this need expert review from
somebody like Melanie, Andres, or Thomas. The new approach to yielding
should definitely be considered a work in progress. Though testing
seems to show that it's a step or two in the right direction.

The other focus for v11 has been fixing regressions, particularly with
index-only scans that run on fully cached data. We've added a more
efficient approach to how we manage the memory for batches used for
index-only scans -- they require extra space to store cached
visibility info taken from the visibility map, which is now more compact.
It is allocated as part of the main batch allocation, avoiding needless
palloc churn.

This v11 also includes a new "Don't allocate _bt_search stack" patch,
which avoids allocating memory for an nbtree descent stack during
index scans. I found that this helped a bit with certain index scans
that call _bt_search frequently/descend the index many times. Anything
that avoids the use of palloc in a critical path is a good idea,
especially when doing so is as straightforward as it is with the
_bt_search stack (we simply don't need any stack in the hot _bt_first
code path).


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