Metadata and record block access stats for indexes

Hi,

For the purpose of writing a blog post I was checking the index stats
recorded for a workload, but found them rather confusing. Following
along the code with the debugger it eventually made sense, and I could
eventually understand what's counted.  Looking around a bit, I
discovered an older discussion [1] in the mailing lists and learned that
the issue is known.  The proposal in that thread is to start counting
separate metadata and record stats depending on what type of index block
is retrieved.

I realized those would have helped me better understand the collected
index stats, so I started working on a patch to add these in the system
views. Attached is a WIP patch file with partial coverage of the B-Tree
index code. The implementation follows the existing stats collection
approach and the naming convention proposed in [1].  Let me know if what
I'm doing is feasible and if there's any concerns I could address. Next
steps would be to replace all places where I currently pass in NULL with
proper counting, as well as update tests and docs.

Looking forward to your feedback! Thanks!

Cheers,
Mircea

[1]:
https://www.postgresql.org/message-id/flat/CAH2-WzmdZqxCS1widYzjDAM%2BZ-Jz%3DejJoaWXDVw9Qy1UsK0tLA%40mail.gmail.com

Hi,

Just attaching the complete patch which now covers all index types, docs
and tests.

You can run the following to see it in action:

create table test (id serial primary key);
insert into test select * from generate_series(0,30000);
select pg_stat_reset();
select * from test where id=3000;
select * from pg_statio_all_indexes where indexrelname = 'test_pkey';

This will show that there were 2 index blocks read from shared buffers
(hit): 1 metadata and one record.

Cheers,

Mircea

On 28/02/2025 21:58, Mircea Cadariu wrote:
> Hi,
>
> For the purpose of writing a blog post I was checking the index stats
> recorded for a workload, but found them rather confusing. Following
> along the code with the debugger it eventually made sense, and I could
> eventually understand what's counted.  Looking around a bit, I
> discovered an older discussion [1] in the mailing lists and learned
> that the issue is known.  The proposal in that thread is to start
> counting separate metadata and record stats depending on what type of
> index block is retrieved.
>
> I realized those would have helped me better understand the collected
> index stats, so I started working on a patch to add these in the
> system views. Attached is a WIP patch file with partial coverage of
> the B-Tree index code. The implementation follows the existing stats
> collection approach and the naming convention proposed in [1].  Let me
> know if what I'm doing is feasible and if there's any concerns I could
> address. Next steps would be to replace all places where I currently
> pass in NULL with proper counting, as well as update tests and docs.
>
> Looking forward to your feedback! Thanks!
>
> Cheers,
> Mircea
>
> [1]:
> https://www.postgresql.org/message-id/flat/CAH2-WzmdZqxCS1widYzjDAM%2BZ-Jz%3DejJoaWXDVw9Qy1UsK0tLA%40mail.gmail.com
>