Re: index prefetching - Mailing list 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
adjustfor the number of buffer hit/read, but later I found that the best way to control that was to use a table small
enoughto 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
detrimentalimpact.
 

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