Thread

Re: Metadata and record block access stats for indexes

Mircea Cadariu <cadariu.mircea@gmail.com> — 2025-04-19T14:28:35Z

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 
>