MIME-Version: 1.0
References: <CAE8JnxN_EwnTLLMWGhvgwaomYZ0ysm7NeogA-BqBd=Rs3S7Oqw@mail.gmail.com>
 <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> <ttuirsz636fr227k7bfbsuqnmpxknvq2hw6yeg56xb45txjxkd@kvubmvhrd32a>
In-Reply-To: <ttuirsz636fr227k7bfbsuqnmpxknvq2hw6yeg56xb45txjxkd@kvubmvhrd32a>
From: Peter Geoghegan <pg@bowt.ie>
Date: Tue, 17 Feb 2026 20:11:24 -0500
Message-ID: <CAH2-Wz=sO3sjqR05t32HkMZ+-iWrZhrNTYE7sg7sxsJBbB2Dpw@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%3DsO3sjqR05t32HkMZ%2B-iWrZhrNTYE7sg7sxsJBbB2Dpw%40mail.gmail.com>
Precedence: bulk

On Tue, Feb 17, 2026 at 7:31=E2=80=AFPM Andres Freund <andres@anarazel.de> =
wrote:
> Interestingly, I can't reproduce a regression compared to index prefetchi=
ng
> being disabled.
>
> If I evict just prefetch_customers between runs, I see prefetch + no-yiel=
d
> being the fastest by a good amount.
>
> If I evict prefetch_customers as well as prefetch_customers_pkey, yieldin=
g
> wins, but only just about. Which I guess makes sense, the index reads are
> synchronous random reads, and we do more of those if we prefetch too
> aggressively.

That can't have been a factor when I ran the query (which is pretty
obvious from the EXPLAIN ANALYZE output).

> I ran the queries both with pgbench (in a script that evicts the buffers,=
 but
> then just looks at the per-statement time for the SELECT, 30 iterations) =
and
> separately interactively with EXPLAIN ANALYZE to get IO stats.
>
>
> This is with debug_io_direct=3Ddata, were you measuring this without DIO?=
 If so,
> was the data in the page cache or did you evict it from there?

I rarely use debug_io_direct=3Ddata for any of my testing. The standard
for me is to use buffered IO + io_uring, while prewarming all indexes
and evicting all heap relations. And by evicting the OS filesystem
cache before each run.

FWIW, the version of the patch you're using is slightly different to
the one I have here. Since I worked on unrelated issues with things
like the cost of rescans with nestloop joins. Another difference is
that the memory allocation for the VM cache is now combined with the
main batch alloc, which seems to be more cache efficient. And saves us
memory for plain index scans.

> We really should add a function to pg_prewarm (or pg_buffercache, or ...)=
 that
> evicts pages in a targeted way from the kernel page cache... Flushing the
> entire kernel pagecache leads to undesirable noise, because it also evict=
s
> filesystem metadata (on somefilesystems at least) etc.

Yeah, having to flush the kernel page cache has been really inconvenient.

> FWIW, I got a crash in a mark-restore query. I think I see the problem:
>
>         /*
>          * Release all currently loaded batches, being sure to avoid free=
ing
>          * markBatch (unless called with complete, where we're supposed t=
o)
>          */
>         for (uint8 i =3D batchringbuf->headBatch; i !=3D batchringbuf->ne=
xtBatch; i++)
>         {
>                 IndexScanBatch batch =3D index_scan_batch(scan, i);
>
>                 if (complete || batch !=3D markBatch)
>                 {
>                         markBatchFreed =3D (batch =3D=3D markBatch);
>                         tableam_util_free_batch(scan, batch);
>                 }
>         }
>
>         if (complete && markBatch !=3D NULL && !markBatchFreed)
>         {
>                 /*
>                  * We didn't free markBatch because it was no longer load=
ed in ring
>                  * buffer.  Do so now instead.
>                  */
>                 tableam_util_free_batch(scan, markBatch);
>         }
>
> If, in the loop, there's a batch after the markBatch in the ring, it'll r=
eset
> markBatchFreed to false. Which then leads to the batch being freed a seco=
nd
> time.

Oops.


--=20
Peter Geoghegan