MIME-Version: 1.0
References: <64a2re223ajj4popowsyu4xekbuvvyfwkrihn5yzyrkwsmsuvp@2lls3tpww5dl>
 <a67mvhyi2q45eg4eimhpwdg6l3s3dmpahti2svffvmvzwmss27@r4nohusvndbq>
 <c19a18fc-ef2e-4d91-b7ba-576d1891315d@vondra.me> <il7jtfowpatrlg33qb5plj7v7pferes4ogerq5fdczszi4kokh@sbwvb2ukfgos>
 <52512325-b1f2-4fff-819e-f68122b2e427@vondra.me> <ws47e3wly6skt36b23zy5qfvcxzueo6od3uicunuodsqnxl7os@7v2qi7qkxzbz>
 <CAH2-Wzk-89uCvdJ1Q6NsM6LvDvUEt6Qy66T6A60J=D_voWxZDg@mail.gmail.com>
 <64mfcfv7iihc4pmqlxarii4esnmqry52ckz5m7lmwylnfnuxuz@oxh4ioxkjtep>
 <CAH2-Wz=GQaLKy3qaq1iYuKZOKt_2YVx13_wxcTf+xcLMEO4qyg@mail.gmail.com>
 <720560ca-97b9-40a1-ad40-9f9b8a6648e9@vondra.me> <cdpcdaaiceonmplfbl6oujf57gzzgdijknwyo2wg4d3lj2zxx3@zp7sbpvi5h5k>
 <2f7fdaa6-2855-4a49-884c-16b91db9a97b@vondra.me> <CAE8JnxPqNGReQWc+-fyprdOXwqODymgoxcxvNrC0W+AJRXpRfQ@mail.gmail.com>
 <CAH2-WzkfGN3EBqdiLt=aAGJ36a1dD2s4HNKzYViXaEv9pQ-z1g@mail.gmail.com>
In-Reply-To: <CAH2-WzkfGN3EBqdiLt=aAGJ36a1dD2s4HNKzYViXaEv9pQ-z1g@mail.gmail.com>
From: Alexandre Felipe <o.alexandre.felipe@gmail.com>
Date: Mon, 23 Feb 2026 13:27:09 +0000
Message-ID: <CAE8JnxNziSMLDgwJVxMgH2HBT03hB19eaaACfZih+42VSkLihQ@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/alternative; boundary="0000000000000b1535064b7dbb2c"
Archived-At: <https://www.postgresql.org/message-id/CAE8JnxNziSMLDgwJVxMgH2HBT03hB19eaaACfZih%2B42VSkLihQ%40mail.gmail.com>
Precedence: bulk

--0000000000000b1535064b7dbb2c
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

> We're in crunch mode right now, ahead of feature freeze, which is less
> than 6 weeks away.

BTW, it seems that someone forgot to #include "utils/rel.h" in
src/backend/access/transam/xloginsert.c

> Tomas has been working on this project for about 3
> years, and I've been working on it for about 1.

That is why I gave up on contributing a feature that I wanted back in 2022
(that could make some of our queries dozens of times faster queries).
Before going for it I tried something easier staying away from critical
things like IO, contention, replication and submitting a planner
improvement (5479), and while I waited I decided to look for high impact
features. That is how I ended up in this thread. So even if I am
inconvenient you can look at my presence here as a compliment!

> Long digressions about
> the asymptotic complexity of priority queues add less than zero value.

Sorry for that, for some readers that could not be obvious. Let me know if
you think there is something where I can add greater than or equal zero
value.


On Sun, Feb 22, 2026 at 7:28=E2=80=AFPM Peter Geoghegan <pg@bowt.ie> wrote:

> On Sun, Feb 22, 2026 at 11:23=E2=80=AFAM Alexandre Felipe
> <o.alexandre.felipe@gmail.com> wrote:
> > DISTANCE CONTROL
> >
> > I tested different strategies to increase distance. 2*d, 2*d+1, d+2,
> d+4, and so on. In my head, what would make sense is d + io_combine_limi,
> but in the end the 2*d gives the best results across different patterns,
> e.g. (h{200}m{200}) that seems to be a more reasonable pattern, as previo=
us
> scans would have loaded in blocks. But these are fundamentally the same, =
as
> I posted about this a markov model, and the limit will be something like
> max_distance * sigmoid(h * (p - p0)), what changes is the transient when =
we
> go in and out of a cached region.
>
> I don't understand. Why, in general, would a Markov model be useful
> for determining prefetch distance?
>
> > LIMITING PREFETCH
> >
> > To avoid prefetch waste with a limit node wouldn't it make sense to sen=
d
> from the executor an estimate of how many rows will be required.
>
> There's a patch that does that. Have you looked at the patch series at al=
l?
>
> > I/O REORDERING
> >
> > I did an experiment reordering the heap accesses, following a zig-zag
> pattern
>
> There's no question that reordering heap accesses is an interesting
> direction to eventually take this infrastructure in. I've experimented
> with that myself. But this is the worst possible time to be increasing
> the scope of the patch for an uncertain benefit.
>
> We're in crunch mode right now, ahead of feature freeze, which is less
> than 6 weeks away. Tomas has been working on this project for about 3
> years, and I've been working on it for about 1. Long digressions about
> the asymptotic complexity of priority queues add less than zero value.
>
> --
> Peter Geoghegan
>

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

<div dir=3D"ltr">&gt; We&#39;re in crunch mode right now, ahead of feature =
freeze, which is less<br>&gt; than 6 weeks away.<br><br>BTW, it seems that =
someone forgot to #include &quot;utils/rel.h&quot; in src/backend/access/tr=
ansam/xloginsert.c<div><br>&gt; Tomas has been working on this project for =
about 3<br>&gt; years, and I&#39;ve been working on it for about 1.<br><br>=
</div><div>That is why I gave up on contributing a=C2=A0feature that=C2=A0I=
 wanted back in 2022 (that could make some of our queries dozens of times f=
aster queries). Before going for it I tried something easier staying away f=
rom critical things like IO, contention, replication and submitting a plann=
er improvement (5479), and while I waited I decided to look for high impact=
 features. That is how I ended up in this thread. So even if I am inconveni=
ent you can look at my presence here as a compliment!</div><div><br></div><=
div>&gt; Long digressions about<br>&gt; the asymptotic complexity of priori=
ty queues add less than zero value.</div><div><br></div><div>Sorry for that=
, for some readers=C2=A0that could not be obvious. Let me know if you think=
 there is something where I can add greater than or equal zero value.</div>=
<div><br></div><div><br></div><div><br></div></div><br><div class=3D"gmail_=
quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Sun, =
Feb 22, 2026 at 7:28=E2=80=AFPM Peter Geoghegan &lt;<a href=3D"mailto:pg@bo=
wt.ie">pg@bowt.ie</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote"=
 style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p=
adding-left:1ex">On Sun, Feb 22, 2026 at 11:23=E2=80=AFAM Alexandre Felipe<=
br>
&lt;<a href=3D"mailto:o.alexandre.felipe@gmail.com" target=3D"_blank">o.ale=
xandre.felipe@gmail.com</a>&gt; wrote:<br>
&gt; DISTANCE CONTROL<br>
&gt;<br>
&gt; I tested different strategies to increase distance. 2*d, 2*d+1, d+2, d=
+4, and so on. In my head, what would make sense is d + io_combine_limi, bu=
t in the end the 2*d gives the best results across different patterns, e.g.=
 (h{200}m{200}) that seems to be a more reasonable pattern, as previous sca=
ns would have loaded in blocks. But these are fundamentally the same, as I =
posted about this a markov model, and the limit will be something like max_=
distance * sigmoid(h * (p - p0)), what changes is the transient when we go =
in and out of a cached region.<br>
<br>
I don&#39;t understand. Why, in general, would a Markov model be useful<br>
for determining prefetch distance?<br>
<br>
&gt; LIMITING PREFETCH<br>
&gt;<br>
&gt; To avoid prefetch waste with a limit node wouldn&#39;t it make sense t=
o send from the executor an estimate of how many rows will be required.<br>
<br>
There&#39;s a patch that does that. Have you looked at the patch series at =
all?<br>
<br>
&gt; I/O REORDERING<br>
&gt;<br>
&gt; I did an experiment reordering the heap accesses, following a zig-zag =
pattern<br>
<br>
There&#39;s no question that reordering heap accesses is an interesting<br>
direction to eventually take this infrastructure in. I&#39;ve experimented<=
br>
with that myself. But this is the worst possible time to be increasing<br>
the scope of the patch for an uncertain benefit.<br>
<br>
We&#39;re in crunch mode right now, ahead of feature freeze, which is less<=
br>
than 6 weeks away. Tomas has been working on this project for about 3<br>
years, and I&#39;ve been working on it for about 1. Long digressions about<=
br>
the asymptotic complexity of priority queues add less than zero value.<br>
<br>
-- <br>
Peter Geoghegan<br>
</blockquote></div>

--0000000000000b1535064b7dbb2c--