vacuum_block_with_pgstatio.sql

DROP TABLE IF EXISTS vestat;
DROP MATERIALIZED VIEW IF EXISTS starting_table_stats;

CREATE TABLE vestat(id int, ival int) WITH (autovacuum_enabled = off);
create index vestat_idx on vestat(id);
create index on vestat(ival);

INSERT INTO vestat(id) SELECT ival FROM generate_series(1,1000000) As ival;
delete from vestat where id>900000;

-- Save block hits before vacuum
SELECT pg_stat_force_next_flush();
SELECT heap_blks_hit, idx_blks_hit FROM pg_statio_all_tables where relname='vestat' \gset

vacuum (verbose, index_cleanup on) vestat;

-- Check the difference
SELECT pg_stat_force_next_flush();
SELECT heap_blks_hit - :heap_blks_hit as delta_heap_hit,
       idx_blks_hit - :idx_blks_hit as delta_idx_hit,
       heap_blks_hit - :heap_blks_hit + idx_blks_hit - :idx_blks_hit as sum
    FROM pg_statio_all_tables where relname='vestat';