Subject: Re: Metadata and record block access stats for indexes

From: lakshmi <[email-masked]>
Date: Tue, 10 Feb 2026 17:23:02 +0530

Hi,

I tested the latest v3 patch on my source code
The patch applied cleanly,postgreSQL build and ran successfully,and the new
idx_metadata_blks column is visible in pg_statio_all_indexes.
To evaluate usefulness,I reproduced a lookup workload on a large indexed
table and compared the traditional overall index hit ratio with the
leaf-page hit ratio enabled by the new counter.The observed results
were:
    overall index hit ratio:85.37%
    leaf-page hit ratio:56.11%
This indicates that cached internal/metadata pages can make overall ratio
appear healthy while a significant portion of leaf pages are still read
from disk.The new statistic therefore provides clear visibility into real
index lookup behavior and working set pressure.

I also performed a simple pgbench comparison between patched and unpatched
builds under similar conditions and did not observe evidence of a
performance regression, suggesting negligible runtime overhead from the
additional statistics tracking.

Overall, the patch applied cleanly, behaved as expected in testing, and the
new counter appears practically useful for diagnosing index I/O patterns.

Thanks for working on improving index observability.

regards
lakshmi

On Tue, Feb 10, 2026 at 12:29 PM Mircea Cadariu <[email-masked]>
wrote:

> Hi, Aleksander!
>
> On 20/11/2025 14:56, Aleksander Alekseev wrote:
> > Hi Mircea,
> >
> >> Rebased and dusted off this patch.
> > Thanks for the patch. Here are my two cents.
> >
> > IMO it would be helpful if you could come up with a few more practical
> > use cases. This change is going to affect pretty much everyone. If
> > only a few users will benefit from it once in several years, the value
> > of the patch is arguably low. As an example, can you think of how the
> > new counters can be used for debugging, checking index integrity,
> > writing new access methods or perhaps writing property-based tests?
> > Just several examples that came to my mind first.
> >
> > Also I'm a bit concerned about the performance impact. It's probably
> > next to nothing, but if you could measure it on a relatively large
> > amount of data that would be great. Note that it's not uncommon to
> > have dozens of different indexes for a single table.
> >
> Thanks! It hasn't caught on, might just stay an educational exercise on
> my side on patch writing and we move on. Thanks for thinking out loud,
> your ideas lead me to this: when working on improving the performance of
> index traversals, it could be a direct way to show improvement and even
> write regression tests: lower amount of internal pages are read than
> before. But, one could also just use the existing stats and still be
> able to track this..
>
> For the performance checks, indeed, I'm using this script, I observe
> no meaningful difference on my laptop:
> https://gist.github.com/mcadariu/fc4a6d4eccd56b4447d1d9d05f9b5d79.
>
> --
> Thanks,
> Mircea Cadariu
>
>
>
>
>
>