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
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
