MIME-Version: 1.0
References: 
 <CAH2-Wzn7vqmt=qE_hDrOx4NETkUoCbdn74G1gswMXi1APUuYrA@mail.gmail.com>
 <ec4d8c7b-bbc7-4805-919f-8fdeb11b3d34@vondra.me>
 <64c8b824-6203-46a3-b045-5e95b796feee@vondra.me>
 <CAH2-Wzm-u6b4gDbLNP=1pkfqJbEyPyey9M-8wG0C+QOTit963Q@mail.gmail.com>
 <03dcc1a9-c5d0-4965-889c-684dc0a7580c@vondra.me>
 <aa46af80-5219-47e6-a7d0-7628106965a6@vondra.me>
 <CA+hUKG+P0RnG_c4vL6=d8ACwpmux5ZF91FO4UpX8PDu1WqEg9w@mail.gmail.com>
 <23f490f4-8325-408c-91a0-a6757ab2441c@vondra.me>
 <CA+hUKG+WWr4-8TYemyU=ucQsNe6bUBN_Sq3mCnBoBtxaJ9w3ug@mail.gmail.com>
 <CA+hUKGLvL408O2ss6YZQsdupryvBqjhuej_hyOYU9fobhdHkTQ@mail.gmail.com>
 <CAN55FZ0y57uEK+Ts8s3NV9Gyg=YiAV-y610XJpfS+jdCh_7f5g@mail.gmail.com>
 <c333af4f-6448-4494-aaf7-9dea3f0afb2e@vondra.me>
 <CAH2-Wzk+ba5oHf1ghC34Z8eoCvum5yHGMxHeFCbC2ibcQ+J7uw@mail.gmail.com>
 <1bebfd1b-aea5-4d41-80a6-eae64b8f9eaf@vondra.me>
 <CAH2-Wzm8AOhY83jPBrPDOO6dauoE9kDcef=b6-4TFSv6AkiNog@mail.gmail.com>
 <8ed1d326-5c6e-476e-b3fd-30d3da210546@vondra.me>
 <38b865bd-2ae9-4a94-a788-6e3dc99ccd70@vondra.me>
In-Reply-To: <38b865bd-2ae9-4a94-a788-6e3dc99ccd70@vondra.me>
From: Peter Geoghegan <pg@bowt.ie>
Date: Thu, 24 Jul 2025 10:40:12 -0400
Message-ID: 
 <CAH2-WzmER9kc4OtmkDh+h51QV=v6Yc5BGsJikwJHtucf1C1HWw@mail.gmail.com>
Subject: Re: index prefetching
To: Tomas Vondra <tomas@vondra.me>
Cc: Nazir Bilal Yavuz <byavuz81@gmail.com>,
 Thomas Munro <thomas.munro@gmail.com>,
	Andres Freund <andres@anarazel.de>, 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-WzmER9kc4OtmkDh%2Bh51QV%3Dv6Yc5BGsJikwJHtucf1C1HWw%40mail.gmail.com>
Precedence: bulk

On Thu, Jul 24, 2025 at 7:19=E2=80=AFAM Tomas Vondra <tomas@vondra.me> wrot=
e:
> I got a bit bored yesterday, so I gave this a try and whipped up a patch
> that adds two pgstattuple functins that I think could be useful for
> analyzing index metrics that matter for prefetching.

This seems quite useful.

I notice that you're not accounting for posting lists. That'll lead to
miscounts of the number of heap blocks in many cases. I think that
that's worth fixing, even given that this patch is experimental.

> It's trivial to summarize this into a per-index statistic (of course,
> there may be some inaccuracies when the run spans multiple ranges), but
> it also seems useful to be able to look at parts of the index.

FWIW in my experience, the per-leaf-page "nhtids:nhblks" tends to be
fairly consistent across all leaf pages from a given index. There are
no doubt some exceptions, but they're probably pretty rare.

> Second, the index is walked sequentially in physical order, from block 0
> to the last block. But that's not really what the index prefetch sees.
> To make it "more accurate" it'd be better to just scan the leaf pages as
> if during a "full index scan".

Why not just do it that way to begin with? It wouldn't be complicated
to make the function follow a chain of right sibling links.

I suggest an interface that takes a block number, and an nblocks int8
argument that must be >=3D 1. The function would start from the block
number arg leaf page. If it's not a non-ignorable leaf page, throw an
error. Otherwise, count the number of distinct heap blocks on the leaf
page, and count the number of heap blocks on each additional leaf page
to the right -- until we've counted the heap blocks from nblocks-many
leaf pages (or until we reach the rightmost leaf page).

I suggest that a P_IGNORE() page shouldn't have its heap blocks
counted, and shouldn't count towards our nblocks tally of leaf pages
whose heap blocks are to be counted. Upon encountering a P_IGNORE()
page, just move to the right without doing anything. Note that the
rightmost page cannot be P_IGNORE().

This scheme will always succeed, no matter the nblocks argument,
provided the initial leaf page is a valid leaf page (and provided the
nblocks arg is >=3D 1).

I get that this is just a prototype that might not go anywhere, but
the scheme I've described requires few changes.

--=20
Peter Geoghegan