Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread View on postgr.es
From: Peter Geoghegan <pg@bowt.ie>
To: Tomas Vondra <tomas@vondra.me>
Cc: Andres Freund <andres@anarazel.de>, 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-17T18:17:15Z
Lists: pgsql-hackers 
On Mon, Feb 16, 2026 at 10:44 AM Tomas Vondra <tomas@vondra.me> wrote:
> I've described this exact behavior a couple months ago in this very
> thread. The queue of batches has limited size, the prefetch needs to be
> paused - at that point read_stream_pause did not exist, so it was done
> by terminating the stream and then read_stream_reset to "resume" it.
>
> That has the unfortunate effect that it resets distance to 1, and so it
> easily led exactly to the issue you describe. But without the pausing it
> would work perfectly fine, in many cases.

Just to be clear: it *used* to work that way, back when we weren't
using Thomas Munro's built-in pausing mechanism. Because we'd actually
reset the read stream back then, as a workaround for the lack of any
built-in pausing mechanism. But we don't do that anymore; recent
versions only reset in cases where it's strictly necessary, such as on
a rescan, or when the scan direction changes.

Recent versions of the patch are just about impossible to make pause.
Because (for better or worse) we'll yield before ever running out of
batch slots/before ever pausing. Andres has shown that this can be
detrimental to our ability to maintain an adequate prefetch distance.
But that's likely only because pausing hurts when it prevents us from
aggressively ramping up prefetch distance at the start of the scan.

Even without yielding, pausing is rare and likely doesn't hurt us
much. After all, 64 batches is usually plenty.

> With the resetting, this effect is pretty brutal.

What resetting? We don't do that anymore? And even back when the patch
did things that way, we made sure to save the old prefetch distance
(which was a kludge upon a kludge)?

Basically I'm confused about why you're now talking about how the
patch worked months ago, which was always a temporary workaround to
deal with the lack of a built-in pausing mechanism. All of our current
problems are due to yielding (not pausing), and/or issues on the read
stream side.

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