Horton

Re: index prefetching

Alexandre Felipe <o.alexandre.felipe@gmail.com>

View thread View on postgr.es
From: Alexandre Felipe <o.alexandre.felipe@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: Tomas Vondra <tomas@vondra.me>, Peter Geoghegan <pg@bowt.ie>, 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-05T13:47:10Z
Lists: pgsql-hackers 
Thank you Andres,

I see, It combines an array that is fast for few buffers and a hash that in
theory
scales well for very large number of buffers. And avoids using an array that
would be fast but would multiply the memory usage by the number of backends.

> Index prefetching patch:
> uncorrelated: 228.936 ms
> correlated:   71.684  ms


I did some tests
> Possible improvements to refcount tracking:
>
> - increase REFCOUNT_ARRAY_ENTRIES - there's a very significant cliff at 8
>  right now, and with vectorized lookup it might not hurt too much to go
to 16
>  or so

Yes, that is true, but only up to 16, the index prefetch test I was doing
was
getting to 90 or so, and that was clipped by max_pinned_buffers.
Also, I noticed a commit 3 months ago that removed the mid-loop return
that effectively will add the first few pins right to left instead of left
to right.

Maybe this works well with vectorisation, but I see an optimization for the
for the (pin/unpin)+ sequence, what about the pin(pin/unpin)+ sequence.
The previous code would always find the buffers on the first or the second
iteration, the new implementation will have to go to the 7th or 8th
iteration,
(or I am not missing something very important).

> - To make the cliff at REFCOUNT_ARRAY_ENTRIES smaller, replace dynahash
with
>  simplehash. That should reduce the perf penalty a good bit.

This is also true, even remove the refcount array completely.

> Unfortunately it's not just the refcount tracking, it's also resowner
> management that gets more expensive.

I didn't read this sentence until I came back to reply. It is exactly what
I noticed.
Once we fix the reference counting the resowner still puts a floor.
And that is even more important when a buffer is pinned multiple times
because the resowner will add one entry to the buffer for each pin.

There is another problem, ResOwnerReleaseBuffer unlocks buffers,
even if it is not the owner of the lock.

I think this deserves a separate thread.

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.