Thread: Why does pg_statio_user_tables report heap_blks_hit after index only scan?
Why does pg_statio_user_tables report heap_blks_hit after index only scan?
From
Aleksander Łukasz
Date:
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?