Why does pg_statio_user_tables report heap_blks_hit after index only scan?

From: Aleksander Łukasz <allllllx(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Why does pg_statio_user_tables report heap_blks_hit after index only scan?
Date: 2022-07-06 16:33:08
Message-ID: dc1119c9-5f4c-891c-6700-d0451f353dd7@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hey pgsql-general,

while debugging some performance issues, I've noticed that index only
scan (with number of heap fetches reported as 0) can still cause
heap_blks_hit as reported by pg_statio_user_tables to increment (in my
case by 1).

For example, on 14.4:

  create table test (a int);
  CREATE TABLE
  insert into test (a) select i from generate_series(1, 100000) i;
  INSERT 0 100000
  create index ix_test on test (a);
  CREATE INDEX
  vacuum analyze test;
  VACUUM
  select pg_stat_reset();
  pg_stat_reset
  ---------------

  (1 row)

  select pg_sleep(1);
  pg_sleep
  ----------

  (1 row)

  Expanded display is on.
  select * from pg_statio_user_tables;
  -[ RECORD 1 ]---+-------
  relid           | 16384
  schemaname      | public
  relname         | test
  heap_blks_read  | 447
  heap_blks_hit   | 102657
  idx_blks_read   | 1
  idx_blks_hit    | 0
  toast_blks_read |
  toast_blks_hit  |
  tidx_blks_read  |
  tidx_blks_hit   |

  Expanded display is off.
  explain analyze
  select a from test where a = 1;
                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
  Index Only Scan using ix_test on test  (cost=0.29..4.31 rows=1
width=4) (actual time=0.043..0.045 rows=1 loops=1)
    Index Cond: (a = 1)
    Heap Fetches: 0
  Planning Time: 0.146 ms
  Execution Time: 0.063 ms
  (5 rows)

  select pg_sleep(1);
  pg_sleep
  ----------

  (1 row)

  Expanded display is on.
  select * from pg_statio_user_tables;
  -[ RECORD 1 ]---+-------
  relid           | 16384
  schemaname      | public
  relname         | test
  heap_blks_read  | 447
  heap_blks_hit   | 102658
  idx_blks_read   | 3
  idx_blks_hit    | 1
  toast_blks_read |
  toast_blks_hit  |
  tidx_blks_read  |
  tidx_blks_hit   |

That is heap_blks_hit incremented by one after index only scan that
supposedly did not fetch anything from the table.

Am I right assuming this is due to some non data related read, like
checking visibility map, and this will always happen? Or maybe my
methodology or other assumptions are wrong?

Thanks for your feedback.

PS: I'm doing those pg_sleeps because I've noticed those are (usually)
needed for stats to refresh. Also, not sure why first pg_reset is not
clearing pg_statio table (as this works for me in many different
contexts)... Apparently I'm not fully understanding when those stats are
affected and maybe this is the reason I've trouble interpreting this
increment?

Browse pgsql-general by date

  From Date Subject
Next Message Bryn Llewellyn 2022-07-06 16:48:59 Re: Seems to be impossible to set a NULL search_path
Previous Message DAVID ROTH 2022-07-06 16:06:52 Multiple Indexes