Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread View on postgr.es
From: Peter Geoghegan <pg@bowt.ie>
To: Alexandre Felipe <o.alexandre.felipe@gmail.com>
Cc: Tomas Vondra <tomas@vondra.me>, 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-15T02:04:22Z
Lists: pgsql-hackers 
On Sat, Feb 14, 2026 at 7:13 PM Alexandre Felipe
<o.alexandre.felipe@gmail.com> wrote:
> 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.

How did you account for the OS filesystem cache? It looks like you
didn't, based on this run_benchmarks.sh code:

> drop_caches() {
>    echo "Skip dropping OS caches (requires sudo)..."
> }

I ran your benchmark test_prefetch_regressions.sql, using "psql -f
test_prefetch_regressions.sql". Against my working copy of the patch,
this procedure gave me these results:

┌─────────────┬───────────┬─────────────┬────────────┬────┬────────┬───────┬────────┐
│ column_name │ io_method │ num_workers │ evict_mode │ n  │ OFF_ms │
ON_ms │ effect │
├─────────────┼───────────┼─────────────┼────────────┼────┼────────┼───────┼────────┤
│ periodic    │ io_uring  │           0 │ off        │ 20 │  76.59 │
77.11 │  0.003 │
│ periodic    │ io_uring  │           0 │ pg         │ 20 │  87.22 │
89.77 │  0.014 │
│ periodic    │ io_uring  │           2 │ off        │ 20 │  76.46 │
77.94 │  0.010 │
│ periodic    │ io_uring  │           2 │ pg         │ 20 │  87.42 │
89.64 │  0.013 │
│ random      │ io_uring  │           0 │ off        │ 20 │  77.76 │
78.32 │  0.004 │
│ random      │ io_uring  │           0 │ pg         │ 20 │  87.66 │
91.52 │  0.022 │
│ random      │ io_uring  │           2 │ off        │ 20 │  77.59 │
78.57 │  0.006 │
│ random      │ io_uring  │           2 │ pg         │ 20 │  87.85 │
91.19 │  0.019 │
│ sequential  │ io_uring  │           0 │ off        │ 20 │  64.18 │
64.64 │  0.004 │
│ sequential  │ io_uring  │           0 │ pg         │ 20 │  73.04 │
69.24 │ -0.027 │
│ sequential  │ io_uring  │           2 │ off        │ 20 │  64.16 │
64.36 │  0.002 │
│ sequential  │ io_uring  │           2 │ pg         │ 20 │  73.05 │
69.16 │ -0.027 │
└─────────────┴───────────┴─────────────┴────────────┴────┴────────┴───────┴────────┘
(12 rows)

The "effect" shown on my Linux workstation just looks like random
noise (I also tested "worker", with similar results). iostat seems to
show just about no I/O on my system when psql runs. I certainly didn't
do anything to drop the OS filesystem cache here, so this isn't
surprising.

How should I go about recreating your result? This was my best guess
at how to do so. But it doesn't feel like a good guess.

> Running from python with psycopg
>
> SUMMARY

Are the numbers you showed comparing the patch to the master branch?
Or is it just comparing enable_indexscan_prefetch=on to
enable_indexscan_prefetch=off with the patch?

Did you write all this test code yourself?

> I could not see the expected positive impact and when using the python script and buffers evicted prefetch had a detrimental impact.

What expected benefit?

The results you've shown put the patch in a very negative light -- at
least if taken at face value. There are a few small single digit
percentage improvements, but no positive result that is
distinguishable from noise. There are several queries that are more
than 2x slower.

It's very hard to believe that what you've shown could be in any way
representative of the patch -- it's completely at odds with my
experience testing the patch. I've spent weeks and weeks looking for
regressions in the patch, often using adversarial, randomized query
generation. But I haven't seen anything near a 2x slowdown, for any
query, with any possible config.

There's no point in speculating what might have happened here until I
can reproduce your results, so I'll refrain from that.

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