public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter Geoghegan <[email protected]>
To: Alexandre Felipe <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Thomas Munro <[email protected]>
Cc: Nazir Bilal Yavuz <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Melanie Plageman <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Georgios <[email protected]>
Cc: Konstantin Knizhnik <[email protected]>
Cc: Dilip Kumar <[email protected]>
Subject: Re: index prefetching
Date: Sat, 14 Feb 2026 21:04:22 -0500
Message-ID: <CAH2-Wzme0Gok57LkHEQQzNwJ+F2JK370yt5f2=v6gOcOedgkpA@mail.gmail.com> (raw)
In-Reply-To: <CAE8JnxN_EwnTLLMWGhvgwaomYZ0ysm7NeogA-BqBd=Rs3S7Oqw@mail.gmail.com>
References: <[email protected]>
<5pltwb73d7cynsxo2yb54ygjk7haviatkrx43mnzihc6kkield@ahnstpgof46i>
<CA+hUKGKL3MRvEftAE+kwBuL2PLg2CwUoHEMr=-KSvsWN8pHq9w@mail.gmail.com>
<[email protected]>
<e33gafg4p7iwvo24ytrxuw43nafm5xm3jefpdspnarcbkfurs7@3jbgdiinxem5>
<[email protected]>
<CAH2-Wz=DfvzasnzLv43cu36Q1Ca8Qi70_JjZ7SRbNhDwwgvirg@mail.gmail.com>
<qdl4fojnbfcnm2k7b4zpvgd6gwzwdgtbl5c7shpimrb76dbyy6@scdnspus3ejh>
<bpdeohyqvltb77viyft4bza4xc4peed3jcoep74d2ih6ynqlke@wbnhcwmq3ril>
<CAH2-WznFdjY_OB2S7_BY4iAyeffK+XrE2qsX6aghgP63VocRfQ@mail.gmail.com>
<4zeu5yb73byiquvf3eefsunnrydyqfxy3eup66jrliutrtd4xl@5iifjey4n5m5>
<[email protected]>
<CAH2-WzkAC_gWQgc3MV3MWBx2EUaE4TfPk8XQPkJgs1R_qNvo=A@mail.gmail.com>
<CAH2-WznLRnUZHC4kmR+J3MgGpN0iTUaZ-4xeSHPiaYR=y3C8Og@mail.gmail.com>
<CAH2-WzkHK0++yn3_nmWL2rWzXiu8Qc=2=NPQDG4nfVjvYkRDrQ@mail.gmail.com>
<CAH2-Wzk9=x=a2TbcqYcX+XXmDHQr5=1v9m4Z_v8a-KwF1Zoz0A@mail.gmail.com>
<CAH2-WzmYqhacBH161peAWb5eF=Ja7CFAQ+0jSEMq=qnfLVTOOg@mail.gmail.com>
<CAH2-Wzmm+mXuv_r+eYgcwOKZzNSmbYoEmxi5mdKMUtkmYa_qXw@mail.gmail.com>
<CAH2-WzkC_EdGB_tdEQr63BiOuJbcvZiV=xO+OJYux4wOaxPD8w@mail.gmail.com>
<CAH2-Wzn8whv-RE4E5L2WQB_Ha_jy0UReJq1yypu83gDYHeXq5g@mail.gmail.com>
<CAH2-Wzm7-QuDOs6TcqfhhDsGEZCuHtn=D-SriOTnTZ_fiXNBvA@mail.gmail.com>
<CAH2-WzmH7pVQ0-mYAxb82aWbz29_BiBPq2wV5p7+1o2sRFqDRQ@mail.gmail.com>
<CAH2-Wz=6a7fGz2rALDX+xiFDuEaGQWpZ49xEaBUDKiPH8gcL+Q@mail.gmail.com>
<CAH2-WzkehuhxyuA8quc7rRN3EtNXpiKsjPfO8mhb+0Dr2K0Dtg@mail.gmail.com>
<CAH2-WzmymSyOt5Y2RGbm6cJXg18J_ttfqjdcpodHe6Gp23ConQ@mail.gmail.com>
<CAH2-Wznv9_KGqHQ1vCW2pkiA6QskBGcx5NC_-UXnD6GEQasvAQ@mail.gmail.com>
<CAE8JnxN_EwnTLLMWGhvgwaomYZ0ysm7NeogA-BqBd=Rs3S7Oqw@mail.gmail.com>
On Sat, Feb 14, 2026 at 7:13 PM Alexandre Felipe
<[email protected]> 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
view thread (87+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: index prefetching
In-Reply-To: <CAH2-Wzme0Gok57LkHEQQzNwJ+F2JK370yt5f2=v6gOcOedgkpA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox