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-31T17:39:30Z
Lists: pgsql-hackers

Attachments

On Fri, Mar 27, 2026 at 8:35 PM Peter Geoghegan <pg@bowt.ie> wrote:
> Attached is v18, which addresses the tricky issues I skipped in v17.

v19 is attached. It contains a few notable improvements over v18:

* The first commit (the one that simply moves code into the new
heapam_indexscan.c file) now moves heap_hot_search_buffer over there
as well.

Andres suggested this at one point, and I believe it wins us a small
but significant performance benefit.

> * The "Add UnlockBufferGetLSN utility function" commit now uses an
> atomic operation (in the common case where it actually drops the index
> page's pin), just like UnlockReleaseBuffer itself following Andres'
> commit f39cb8c011062d65e146c1e9d1aae221e96d8320 from today.

* v19 removes the "Add UnlockBufferGetLSN utility function" patch entirely.

Following Andres' commit f39cb8c0, this no longer seemed to help
performance (compared to just calling BufferGetLSNAtomic and
UnlockReleaseBuffer directly), so I removed it.

> I believe I have now acted on all of Andres' feedback, with one minor
> exception: Andres disliked how we use 'if (scan->xs_heapfetch)' to
> determine whether to use the scan's batch cache (we don't want to use
> it at all if the scan is ending). I just ran out of steam, so this
> version doesn't address the problem.

* v19 fixes this by adding a new helper function. This allows us to
pass down the required context (whether the scan is ending) to inform
the relevant batch recycling code whether it really should place its
caller's batch in the scan's cache (or whether it should just pfree it
right away).

As in previous versions, I'm including several extra read stream
patches to address various problems in that area. These problems, tied
to how index prefetching stresses the read stream that are new and not
encountered by existing read stream users. Andres wrote all of these
patches. Those aspects of the patch should be discussed on a new
thread dedicated to index prefetching-related read stream
enhancements: https://postgr.es/m/f3xxfrkafjxpyqxywcxricxgyizjirfceychyxsgn7bwjp5eda@kwbduhy7tfmu

(Though I'm also carrying some extra bug fix patches here, not
included on that other thread, to address preexisting read stream
bugs, albeit bugs highly unlikely to affect current read stream
users.)

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