-- Needs the following postgresql.conf configuration:

-- # Use pgss to get statistics on dirtied buffers
-- shared_preload_libraries = 'pg_stat_statements'
-- # Track vacuum and analyze command
-- pg_stat_statements.track_utility = on
-- # Keep shared buffers low to generate buffer reads
-- shared_buffers = 16

CREATE TEMPORARY TABLE vacuum_blks_stat_test(id int, ival int) WITH (autovacuum_enabled = off);
INSERT INTO vacuum_blks_stat_test(id) SELECT ival FROM generate_series(1,10000) As ival;
DELETE from vacuum_blks_stat_test where id>9000;
CHECKPOINT;

-- Reset pgss stats
select pg_stat_statements_reset();

-- Skip database stats as those happen at the end of the vacuum command
-- and is not tracked by the per relation block usage
VACUUM (VERBOSE, SKIP_DATABASE_STATS) vacuum_blks_stat_test;

select query, shared_blks_hit + local_blks_hit as sum_hit,
    shared_blks_read + local_blks_read as sum_read,
    shared_blks_dirtied + local_blks_dirtied as sum_dirtied
FROM pg_stat_statements WHERE query LIKE 'VACUUM (VERBOSE%';

-- Test analyze verbose
ANALYZE (VERBOSE) vacuum_blks_stat_test;
select query, shared_blks_hit + local_blks_hit as sum_hit,
    shared_blks_read + local_blks_read as sum_read,
    shared_blks_dirtied + local_blks_dirtied as sum_dirtied
FROM pg_stat_statements WHERE query LIKE 'ANALYZE (VERBOSE%';
