Horton

Re: index prefetching

Tomas Vondra <tomas@vondra.me>

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

On 2/15/26 01:13, Alexandre Felipe wrote:
> Hi,
> I decided to test this PR.
> 
> I didn't take much time to go through the thread or the code in detail
> yet. But I have my first benchmark results and I would like to share.
> 

I'm quite confused by the scripts you shared, it seems incomplete. The
run_regression.py is meant to call purge_cache.sh (which is missing),
and the run_benchmark tries to call all sorts of missing .sql scripts.

So how do we use that?

> EXPERIMENT
> 
> I tested [CF 4351] v10 - Index Prefetching
> 
> I created a table with 100k rows and
> Sequential is, as guessed, 1,2,3,4 (indexed)
> Periodic is a quasi random (i * jump) % num_rows, where gcd(jump,
> num_rows) = 1, guarantee that there are no repeated entries (indexed)
> Random is a `row_number() over (order by random())` (indexed)
> The payload is a fixed 200 character long string, just to make it more
> realistic.
> 
> For the tests, I disable sorting, sequential scans, index only scans and
> bitmap scans.
> Since buffer cache always has a significant impact on the query
> performance, I shuffled the tests, and tried to adjust for the number of
> buffer hit/read, but later I found that the best way to control that was
> to use a table small enough to be entirely held in cache, and evict the
> buffers.
> 

That seems a bit bizarre. The whole point of index prefetching is better
I/O scheduling (ahead of time), but if you "control" the impact of cache
by making sure everything is cached, that kinda defeats the whole thing.

A table that is just 24MB and fits into buffers is a bit useless. It
means that even with random pattern (which is generally about the best
for prefetching), only about ~1/30 of pages will require I/O. Each page
has ~32 items, but only the first item from each page will incur an I/O.


> * off: buffers are kept in cache
> * pg: buffers evicted from postgres pg_buffercache_evict from
> pg_buffercache extension.
> * os: supported only in  python, I separated the buffer eviction in
> purge_cache as it requires sudo (tested only in MacOS).
> 
> I varied 
>  * max_parallel_workers_per_gather (although I guess it wasn't exploited), 
>  * enable_index_prefetch 
>  * the column used as sorting key and, as a result, the index used.
>  * and buffer eviction mode.
> 
> Running from python with psycopg
> 

On what kind of hardware? How much variance is in the results?


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.