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