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>
In-Reply-To: <CAE8JnxPqNGReQWc+-fyprdOXwqODymgoxcxvNrC0W+AJRXpRfQ@mail.gmail.com>
From: Peter Geoghegan <pg@bowt.ie>
Date: Sun, 22 Feb 2026 14:28:00 -0500
Message-ID: <CAH2-WzkfGN3EBqdiLt=aAGJ36a1dD2s4HNKzYViXaEv9pQ-z1g@mail.gmail.com>
Subject: Re: index prefetching
To: Alexandre Felipe <o.alexandre.felipe@gmail.com>
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: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAH2-WzkfGN3EBqdiLt%3DaAGJ36a1dD2s4HNKzYViXaEv9pQ-z1g%40mail.gmail.com>
Precedence: bulk

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 i=
n 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 scans =
would have loaded in blocks. But these are fundamentally the same, as I pos=
ted about this a markov model, and the limit will be something like max_dis=
tance * 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 send =
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 all?

> I/O REORDERING
>
> I did an experiment reordering the heap accesses, following a zig-zag pat=
tern

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.

--=20
Peter Geoghegan