MIME-Version: 1.0
References: <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-Wzmy7NMba9k8m_VZ-XNDZJEUQBU8TeLEeL960-rAKb-+tQ@mail.gmail.com>
 <issqornf6vdn3vb64fjuoathypmu3e5pgputd3lpfuvoeqyvzr@qfordnhplp2v>
 <CAH2-WzkEZiRL5wedTTnB0uKZFkm52BPJO6wZugoik95amtz4DQ@mail.gmail.com> <dfabr4ep4evhcifczb47qllxrewz7t4dlalqiazi6c46fz7iv2@e4556vi3s626>
In-Reply-To: <dfabr4ep4evhcifczb47qllxrewz7t4dlalqiazi6c46fz7iv2@e4556vi3s626>
From: Peter Geoghegan <pg@bowt.ie>
Date: Sat, 28 Feb 2026 14:32:35 -0500
Message-ID: <CAH2-Wz=EVnDidFckVXq=iA=ugix=r986VUepmXO3c+1xt951Qw@mail.gmail.com>
Subject: Re: index prefetching
To: Andres Freund <andres@anarazel.de>
Cc: Tomas Vondra <tomas@vondra.me>, Alexandre Felipe <o.alexandre.felipe@gmail.com>, 
	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-Wz%3DEVnDidFckVXq%3DiA%3Dugix%3Dr986VUepmXO3c%2B1xt951Qw%40mail.gmail.com>
Precedence: bulk

On Fri, Feb 27, 2026 at 7:37=E2=80=AFPM Andres Freund <andres@anarazel.de> =
wrote:
> I do continue to wonder if we ought to pass down some hints from the plan=
ner
> about how much data an indexscan is likely to read to influence readahead
> aggressiveness.

As you know, I already have some of that, but it isn't particularly
well developed. I agree that pushing the hinting mechanism further and
using something like your proposed READ_STREAM_SLOW_START mechanism
(which you mention in your later email from today) seems most
promising.

> I do agree it's right beign concerned about the increase in index fetches=
 with
> such mark/restore cases.

> Is this, by any chance, with starting the server and running these querie=
s in
> that order?  Are you repeating these runs within one server start, evicti=
ng
> the buffers inbetween?

Yes, I repeat the runs 3 times, evicting/prewarming as needed each
time. I go with the fastest run.

> Are you using huge pages?  I see rather differing performance results
> with/without when not prefetching.

Yes, I always use huge_pages (and never use transparent huge pages).

> It really shouldn't be sensitive - that query will never be able to reuse
> heapam pages within a query, and evicting a clean buffer isn't that expen=
sive.

Could have been my previous failure to use "cpupower idle-set -D 0" to
get maximally stable performance for my microbenchmarks. As discussed
privately, I've added this as yet another step in the script that
automates all this for me/that prepares my system to run a
microbenchmark.

--=20
Peter Geoghegan