Re: Metadata and record block access stats for indexes

On 7/4/25 18:00, Mircea Cadariu wrote:
> Just attaching v2 of the patch.

Hi Mircea,

Your patch applies cleanly and seems to work well.

IIUC, the index hit ratio should be computed with the following formula:

(idx_blks_hit - idx_metadata_blks) / (idx_blks_hit - idx_metadata_blks +
idx_blks_read)

because most of the index non-leaf pages should be in the cache. Right?

This should probably be documented somewhere?

Here is my testing:

# select tree_level, internal_pages, leaf_pages from
pgstatindex('pgbench_accounts_pkey');
tree_level | internal_pages | leaf_pages
------------+----------------+------------
2 | 20 | 5465
(1 row)

# SELECT DISTINCT pg_buffercache_evict(bufferid)
FROM pg_buffercache
WHERE relfilenode = pg_relation_filenode('pgbench_accounts_pkey');
pg_buffercache_evict
----------------------
(t,f)
(1 row)

# SELECT pg_stat_reset();
pg_stat_reset
---------------

(1 row)

# SELECT max(abalance) FROM pgbench_accounts WHERE aid = 100;
max
-----
0
(1 row)

# select idx_blks_read, idx_blks_hit, idx_metadata_blks from
pg_statio_all_indexes where indexrelname = 'pgbench_accounts_pkey';
idx_blks_read | idx_blks_hit | idx_metadata_blks
---------------+--------------+-------------------
3 | 0 | 2
(1 row)

--> 3 pages: the root of the tree, one internal page and one leaf

#
\q
fyhuel(at)framework:~$ psql bench
psql (19devel)
Type "help" for help.

# SELECT max(abalance) FROM pgbench_accounts WHERE aid = 100;
max
-----
0
(1 row)

primary sleaf bench [42323] # select idx_blks_read, idx_blks_hit,
idx_metadata_blks from pg_statio_all_indexes where indexrelname =
'pgbench_accounts_pkey';
idx_blks_read | idx_blks_hit | idx_metadata_blks
---------------+--------------+-------------------
4 | 3 | 5

--> 4 more pages: same as before, already in cache, plus the index meta
page, read outside shared buffers because we started a new session?