MIME-Version: 1.0
References: <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>
 <64a2re223ajj4popowsyu4xekbuvvyfwkrihn5yzyrkwsmsuvp@2lls3tpww5dl>
 <a67mvhyi2q45eg4eimhpwdg6l3s3dmpahti2svffvmvzwmss27@r4nohusvndbq>
 <c19a18fc-ef2e-4d91-b7ba-576d1891315d@vondra.me> <CAE8JnxNOV9kOgmU1-WUWts9Q-Jj_Nf0K480wyEwJXUQYMnYu3g@mail.gmail.com>
 <e3f17ba9-7160-4fc3-a756-03ca27d148f1@vondra.me>
In-Reply-To: <e3f17ba9-7160-4fc3-a756-03ca27d148f1@vondra.me>
From: Alexandre Felipe <o.alexandre.felipe@gmail.com>
Date: Mon, 16 Feb 2026 10:12:12 +0000
Message-ID: <CAE8JnxOzJQrb44S216Mp71dpqmsaaH5=unJB0zHgVa-+ODPMQA@mail.gmail.com>
Subject: Re: index prefetching
To: Tomas Vondra <tomas@vondra.me>
Cc: Andres Freund <andres@anarazel.de>, Peter Geoghegan <pg@bowt.ie>, 
	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/alternative; boundary="000000000000ec79d0064aee3098"
Archived-At: <https://www.postgresql.org/message-id/CAE8JnxOzJQrb44S216Mp71dpqmsaaH5%3DunJB0zHgVa-%2BODPMQA%40mail.gmail.com>
Precedence: bulk

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

> How did you do that? Did you increase the number of rows, make the rows
> wider (by increasing the 'repeat' parameter in the script), or something
> else? Did you verify the table really is 1000x larger?
>

I increased the number of rows by 1000. I didn't really check the size of
the table, the time increase suggests that it was right.


> The "10k table row" means repeat('x',10000) when generating data? Oh, I
> see you're using some string_agg(), to make it not compress. But note
> that if it's TOASTed, it become entirely irrelevant for the prefetching
> test because it's in a separate relation.
>

sorry, 10k row table, for the payload I left a note

> [b] This time I used a (SELECT string_agg((i*j)::text, '+') FROM
> generate_series(1, 50)) instead of repeat('x', 100), just to prevent it
> from compressing to nothing when I try larger payloads, and hit the
> TOAST thresholds. I removed the primary key `id` because it was annoying
> to take 20 minutes to insert the data in the large scale test.


> Unfortunately, you have not included the new script, so we can't try
> reproducing your results.
>

 Let me try to find something not so insane.

128kB shared buffers is a little bit ... insane. I refuse to optimize
> anything for this value, and I don't even call about regressions. Even
> 128MB is not really practical, any serious system caring about
> performance will use tens or hundreds of GBs of shared buffers.
>

I am not going to dispute that

> If the tests I am doing are pointless, should we consider having
> > something in the planner to prevent these scans from using prefetch?
> >
>
> How would you do that? Please explain.
>

I have no idea. But based on what you said I thought you would know. In my
head: "If my test seemed ridiculous to them all, they have some clear
boundaries in their mind that they could write in the planner".


> ... or you could modify the script to simply use sudo.
>
In that case sudo would request a password to the caller, and the caller is
a python script, no interaction there, of course I could do all the steps
manually, but it is more error prone (just my own mistakes are enough).


Regards,
Alexandre

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

<div dir=3D"ltr"><div class=3D"gmail_quote gmail_quote_container"><div><br>=
</div><div><br></div><div>=C2=A0</div><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddi=
ng-left:1ex">How did you do that? Did you increase the number of rows, make=
 the rows<br>
wider (by increasing the &#39;repeat&#39; parameter in the script), or some=
thing<br>
else? Did you verify the table really is 1000x larger?<br></blockquote><div=
>=C2=A0</div><div>I increased the number of rows by 1000. I didn&#39;t real=
ly check the size of the table, the time increase suggests=C2=A0that it was=
 right.</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"ma=
rgin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:=
1ex">
The &quot;10k table row&quot; means repeat(&#39;x&#39;,10000) when generati=
ng data? Oh, I<br>
see you&#39;re using some string_agg(), to make it not compress. But note<b=
r>
that if it&#39;s TOASTed, it become entirely irrelevant for the prefetching=
<br>
test because it&#39;s in a separate relation.<br></blockquote><div>=C2=A0</=
div><div>sorry, 10k row table, for the payload I left a note<br><br></div><=
div>&gt; [b]=C2=A0This time I used a (SELECT string_agg((i*j)::text, &#39;+=
&#39;) FROM<br>&gt; generate_series(1, 50)) instead of repeat(&#39;x&#39;, =
100), just to prevent it<br>&gt; from compressing to nothing when I try lar=
ger payloads, and hit the<br>&gt; TOAST thresholds. I removed the primary k=
ey `id` because it was annoying<br>&gt; to take 20 minutes to insert the da=
ta in the large scale test.<br>=C2=A0</div><blockquote class=3D"gmail_quote=
" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);=
padding-left:1ex">
Unfortunately, you have not included the new script, so we can&#39;t try<br=
>
reproducing your results.<br></blockquote><div><br></div><div>=C2=A0Let me =
try to find something not so insane.</div><div><br></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">
128kB shared buffers is a little bit ... insane. I refuse to optimize<br>
anything for this value, and I don&#39;t even call about regressions. Even<=
br>
128MB is not really practical, any serious system caring about<br>
performance will use tens or hundreds of GBs of shared buffers.<br></blockq=
uote><div>=C2=A0</div><div>I am not going to dispute that=C2=A0<br><br></di=
v><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex">
&gt; If the tests I am=C2=A0doing are pointless, should=C2=A0we consider ha=
ving<br>
&gt; something in the planner to prevent these scans from using prefetch?<b=
r>
&gt; <br>
<br>
How would you do that? Please explain.<br></blockquote><div><br></div><div>=
I have no idea. But based on what you said I thought you would know. In my =
head: &quot;If my test seemed ridiculous to them all, they have some clear =
boundaries in their mind that they could write in the planner&quot;.</div><=
div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0=
px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
... or you could modify the script to simply use sudo.<br></blockquote><div=
>In that case sudo would request a password to the caller, and the caller i=
s a python script, no interaction there, of course I could do all the steps=
 manually, but it is more error prone (just my own mistakes are enough).</d=
iv><div>=C2=A0</div><div><br></div><div>Regards,</div><div>Alexandre</div><=
/div></div>

--000000000000ec79d0064aee3098--