Horton

Re: index prefetching

Tomas Vondra <tomas@vondra.me>

View thread View on postgr.es
From: Tomas Vondra <tomas@vondra.me>
To: Andres Freund <andres@anarazel.de>
Cc: Peter Geoghegan <pg@bowt.ie>, 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-18T15:39:30Z
Lists: pgsql-hackers 

On 2/18/26 05:21, Andres Freund wrote:
> Hi,
> 
> On 2026-02-17 22:36:53 +0100, Tomas Vondra wrote:
>> On 2/17/26 21:16, Peter Geoghegan wrote:
>>> 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:
>>>>> On Mon, Feb 16, 2026 at 11:48 AM Andres Freund <andres@anarazel.de> wrote:
>>>>> I agree that the current heuristics (which were invented recently) are
>>>>> too conservative. I overfit the heuristics to my current set of
>>>>> adversarial queries, as a stopgap measure.
>>>>
>>>> Are you doing any testing on higher latency storage?  I found it to be quite
>>>> valuable to use dm_delay to have a disk with reproducible (i.e. not cloud)
>>>> higher latency (i.e. not just a local SSD).
>>>
>>> I sometimes use dm_delay (with the minimum 1ms delay) when testing,
>>> but don't do so regularly. Just because it's inconvenient to do so
>>> (perhaps not a great reason).
>>>
>>>> Low latency NVMe can reduce the
>>>> penalty of not enough readahead so much that it's hard to spot problems...
>>>
>>> I'll keep that in mind.
>>>
>>
>> So, what counts as "higher latency" in this context? What delays should
>> we consider practical/relevant for testing?
> 
> 0.5-4ms is the range I've seen in various clouds across their reasonable
> storage products (i.e. not spinning disks or other ver bulk oriented things).
> 
> Unfortunately dm_delay doesn't support < 1ms delays, but it's still much
> better than nothing.
> 
> I've been wondering about teaching AIO to delay IOs (by adding a sleep to
> workers and linking a IORING_OP_TIMEOUT submission with the actually intended
> IO) to allow testing smaller delays.
> 

Could be useful testing facility, if it's done in a way that does not
limit the IO concurrency (i.e. the delay should probably be when
consuming the IO, depending on the timestamp of the IO start).

> 
>>> That would make sense. You can already tell when that's happened by
>>> comparing the details shown by EXPLAIN ANALYZE against the same query
>>> execution on master, but that approach is inconvenient. Automating my
>>> microbenchmarks has proven to be important with this project. There's
>>> quite a few competing considerations, and it's too easy to improve one
>>> query at the cost of regressing another.
>>>
>>
>> What counts as "unconsumed IO"? The IOs the stream already started, but
>> then did not consume? That shouldn't be hard, I think.
> 
> Yes, the number of IOs that were started but not consumed. Or, even better,
> the number of IOs that completed but were not consumed - but that'd be harder
> to get right now.
> 
> I agree that started-but-not-consumed should be pretty easy.
> 

I'll try to add it to the EXPLAIN.


regards

-- 
Tomas Vondra

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.