public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexandre Felipe <[email protected]>
To: Peter Geoghegan <[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: Sun, 15 Feb 2026 08:00:23 +0000
Message-ID: <CAE8JnxMuyu7_ctueuBSUvbfx=nfCi-wk0_9+HmCs0VRC=aZPkA@mail.gmail.com> (raw)
In-Reply-To: <CAH2-Wzme0Gok57LkHEQQzNwJ+F2JK370yt5f2=v6gOcOedgkpA@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>
<CAH2-Wzme0Gok57LkHEQQzNwJ+F2JK370yt5f2=v6gOcOedgkpA@mail.gmail.com>
Wow, quick response.
OS eviction is only implemented in the python version. But the most
intriguing part is that the python version also shows a degradation by
simply evicting with pg_buffeercache_evict. I checked many times, first I
thought that my differences had a sign inversion. Then I thought it could
be something about the python speed, could it be? I am timing with explain
analyse, if I was fetching the rows this would be the obvious culprit.
How did you account for the OS filesystem cache? It looks like you
> didn't, based on this run_benchmarks.sh code:
>
Sorry I attached the wrong file. I didn't use this run_benchmark.sh.
> 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.
Could you try running the python script or should I provide it in a
different way?
> 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?
Just changing the parameter.
> Did you write all this test code yourself?
>
In the sense of typing it no, I used cursor, so I just edited what I could
see it didn't get right.
The results you've shown put the patch in a very negative light -- at
> least if taken at face value.
Please notice that I tried to be neutral in the narrative.
Maybe because I tested on a MacOS maybe it works differently from the type
of
"modern storage" mentioned at the start of the thread.
There's no point in speculating what might have happened here until I
> can reproduce your results
>
I totally agree, that is why my first step was to try to reproduce your
results independently.
I see another message, checking that.
--
Alexandre
Attachments:
[text/x-sh] purge_cache.sh (409B, 3-purge_cache.sh)
download | inline:
#!/bin/bash
# Purge OS filesystem cache
# Give this script appropriate privileges:
# macOS: sudo chmod +s purge_cache.sh (or add to sudoers)
# Linux: sudo chmod +s purge_cache.sh (or add to sudoers)
case "$(uname)" in
Darwin)
purge
;;
Linux)
sync
echo 3 > /proc/sys/vm/drop_caches
;;
*)
echo "Unsupported OS" >&2
exit 1
;;
esac
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: <CAE8JnxMuyu7_ctueuBSUvbfx=nfCi-wk0_9+HmCs0VRC=aZPkA@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