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-19T17:44:16Z
Lists: pgsql-hackers

Attachments

On Tue, Mar 17, 2026 at 1:37 PM Peter Geoghegan <pg@bowt.ie> wrote:
> v14 has already bitrot, so I'm posting this new v15 to keep the
> patchset applying cleanly.

And now v15 has bitrot. So here's a new v16. There are 2 notable updates here.

Most notably, I decided to take a more conservative approach to fixing
the issues with the selfuncs.c mechanism that gives up when
get_actual_variable_range does too much work during planning. Rather
than inventing a new approach that bases the decision to give up on
the number of leaf pages accessed, which has certain advantages [1], I
have decided to cut scope and more or less reimplement the existing
VISITED_PAGES_LIMIT mechanism on the heapam side. This was a pragmatic
choice; there is no clear consensus on whether the design I proposed
for this will be acceptable, and we lack the time to resolve those
issues now.

To recap, selfuncs.c can no longer be the place where
VISITED_PAGES_LIMIT is implemented, because the new slot-based
interface won't return until it finds a visible tuple. The new
higher-level interface simply cannot support callers that want to
count the number of heap pages examined themselves.

The other notable change aims to improve performance for cached
workloads through more aggressive specialization of callback routines
like heapam_index_getnext_slot. There are now a total of 4 versions:

1. One for index-only scans + amgetbatch.
2. One for one for index-only scans + amgettuple.
3. One for plain index scans + amgetbatch.
4. One for plain index scans + amgettuple.

I've also aggressively used pg_attribute_always_inline (without this
specialization, performance ends up worse rather than better).

There might be some concerns about the distributed costs of increased
code size. But this revision is the fastest so far across all
available metrics (though of course the specialization work is
focussed on cached query performance specifically).

[1] (see https://www.postgresql.org/message-id/flat/CAH2-Wzkt1WkKp4VRJu3qHfmKXc8W%2BXYv1RXg5d2d3fSvAeO%3Drg%40mail.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.