MIME-Version: 1.0
References: <6d59c277-c440-4d1f-a46e-157958c06a5f@vondra.me>
 <5pltwb73d7cynsxo2yb54ygjk7haviatkrx43mnzihc6kkield@ahnstpgof46i>
 <CA+hUKGKL3MRvEftAE+kwBuL2PLg2CwUoHEMr=-KSvsWN8pHq9w@mail.gmail.com>
 <ffcc1472-604e-48cf-afd1-25a245d3df4a@vondra.me> <e33gafg4p7iwvo24ytrxuw43nafm5xm3jefpdspnarcbkfurs7@3jbgdiinxem5>
 <931afce3-8c86-4c96-9861-0ffa17c6560f@vondra.me> <CAH2-Wz=DfvzasnzLv43cu36Q1Ca8Qi70_JjZ7SRbNhDwwgvirg@mail.gmail.com>
 <qdl4fojnbfcnm2k7b4zpvgd6gwzwdgtbl5c7shpimrb76dbyy6@scdnspus3ejh>
 <bpdeohyqvltb77viyft4bza4xc4peed3jcoep74d2ih6ynqlke@wbnhcwmq3ril>
 <CAH2-WznFdjY_OB2S7_BY4iAyeffK+XrE2qsX6aghgP63VocRfQ@mail.gmail.com>
 <4zeu5yb73byiquvf3eefsunnrydyqfxy3eup66jrliutrtd4xl@5iifjey4n5m5>
 <c515d037-9c9c-40d3-9f1f-f0e68bf5842b@vondra.me> <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>
In-Reply-To: <CAH2-Wzme0Gok57LkHEQQzNwJ+F2JK370yt5f2=v6gOcOedgkpA@mail.gmail.com>
From: Alexandre Felipe <o.alexandre.felipe@gmail.com>
Date: Sun, 15 Feb 2026 08:00:23 +0000
Message-ID: <CAE8JnxMuyu7_ctueuBSUvbfx=nfCi-wk0_9+HmCs0VRC=aZPkA@mail.gmail.com>
Subject: Re: index prefetching
To: Peter Geoghegan <pg@bowt.ie>
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>
Content-Type: multipart/mixed; boundary="000000000000ba9c55064ad83b61"
Archived-At: <https://www.postgresql.org/message-id/CAE8JnxMuyu7_ctueuBSUvbfx%3DnfCi-wk0_9%2BHmCs0VRC%3DaZPkA%40mail.gmail.com>
Precedence: bulk

--000000000000ba9c55064ad83b61
Content-Type: multipart/alternative; boundary="000000000000ba9c53064ad83b5f"

--000000000000ba9c53064ad83b5f
Content-Type: text/plain; charset="UTF-8"

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

--000000000000ba9c53064ad83b5f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Wow, quick response.</div><div dir=3D"ltr=
"><br></div><div dir=3D"ltr"><div><br>OS eviction is only implemented in th=
e python version. But the most intriguing part is that the python version a=
lso shows a degradation by simply evicting with pg_buffeercache_evict. I ch=
ecked=C2=A0many times, first I thought that my differences had a sign inver=
sion. 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 woul=
d be the obvious culprit.<div><br></div></div><div></div><br></div><div cla=
ss=3D"gmail_quote gmail_quote_container"><blockquote class=3D"gmail_quote" =
style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pa=
dding-left:1ex">How did you account for the OS filesystem cache? It looks l=
ike you<br>
didn&#39;t, based on this run_benchmarks.sh code:<br>
</blockquote><div><br></div><div>Sorry I attached the wrong file. I didn=
9;t use this run_benchmark.sh.</div><div>=C2=A0</div><blockquote class=3D"g=
mail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204=
,204,204);padding-left:1ex">
How should I go about recreating your result? This was my best guess<br>
at how to do so. But it doesn&#39;t feel like a good guess.</blockquote><di=
v><br></div><div>Could you try running the python script or should I provid=
e=C2=A0it in a different way?</div><div>=C2=A0</div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex">
Are the numbers you showed comparing the patch to the master branch?<br>
Or is it just comparing enable_indexscan_prefetch=3Don to<br>
enable_indexscan_prefetch=3Doff with the patch?</blockquote><div><br></div>=
<div>Just changing the parameter.</div><div>=C2=A0</div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">
Did you write all this test code yourself?<br></blockquote><div><br></div><=
div>In the sense of typing it no, I=C2=A0used cursor, so I just edited what=
 I=C2=A0could see it didn&#39;t get right.</div><div><br></div><blockquote =
class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px sol=
id rgb(204,204,204);padding-left:1ex">The results you&#39;ve shown put the =
patch in a very negative light -- at<br>
least if taken at face value. </blockquote><div><br></div><div>Please notic=
e that I tried to be neutral in the narrative.</div><div><br></div><div>May=
be because I tested on a=C2=A0MacOS maybe=C2=A0it works differently from=C2=
=A0the type of=C2=A0</div><div>&quot;modern storage&quot; mentioned at the =
start of the thread.</div><div><br></div><blockquote class=3D"gmail_quote" =
style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pa=
dding-left:1ex">
There&#39;s no point in speculating what might have happened here until I<b=
r>
can reproduce your results<br></blockquote><div>=C2=A0</div><div>I totally =
agree, that is why my first=C2=A0step was to try to reproduce your results =
independently.</div><div><br></div><div>I see another message, checking tha=
t.</div><div><br></div><div>--</div><div>Alexandre=C2=A0</div></div></div>

--000000000000ba9c53064ad83b5f--
--000000000000ba9c55064ad83b61
Content-Type: text/x-sh; charset="US-ASCII"; name="purge_cache.sh"
Content-Disposition: attachment; filename="purge_cache.sh"
Content-Transfer-Encoding: base64
Content-ID: <f_mlnfxafa0>
X-Attachment-Id: f_mlnfxafa0

IyEvYmluL2Jhc2gKIyBQdXJnZSBPUyBmaWxlc3lzdGVtIGNhY2hlCiMgR2l2ZSB0aGlzIHNjcmlw
dCBhcHByb3ByaWF0ZSBwcml2aWxlZ2VzOgojICAgbWFjT1M6IHN1ZG8gY2htb2QgK3MgcHVyZ2Vf
Y2FjaGUuc2ggKG9yIGFkZCB0byBzdWRvZXJzKQojICAgTGludXg6IHN1ZG8gY2htb2QgK3MgcHVy
Z2VfY2FjaGUuc2ggKG9yIGFkZCB0byBzdWRvZXJzKQoKY2FzZSAiJCh1bmFtZSkiIGluCiAgICBE
YXJ3aW4pCiAgICAgICAgcHVyZ2UKICAgICAgICA7OwogICAgTGludXgpCiAgICAgICAgc3luYwog
ICAgICAgIGVjaG8gMyA+IC9wcm9jL3N5cy92bS9kcm9wX2NhY2hlcwogICAgICAgIDs7CiAgICAq
KQogICAgICAgIGVjaG8gIlVuc3VwcG9ydGVkIE9TIiA+JjIKICAgICAgICBleGl0IDEKICAgICAg
ICA7Owplc2FjCg==
--000000000000ba9c55064ad83b61--