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?
