test.sql

DROP TABLE IF EXISTS test_io_strategy;

SELECT sum(alloc) AS io_sum_strategy_allocs_before FROM pg_stat_io WHERE io_context = 'Strategy' \gset
SELECT sum(read) AS io_sum_strategy_reads_before FROM pg_stat_io WHERE io_context = 'Strategy' \gset
CREATE TABLE test_io_strategy(a INT, b INT);
ALTER TABLE test_io_strategy SET (autovacuum_enabled = 'false');
INSERT INTO test_io_strategy SELECT i, i from generate_series(1, 8000)i;
-- Ensure that the next VACUUM will need to perform IO by rewriting the table
-- first with VACUUM (FULL).
SELECT relforknumber, count(*) AS bufcount FROM pg_buffercache WHERE relfilenode = (SELECT relfilenode FROM pg_class WHERE oid  = 'test_io_strategy'::regclass) GROUP BY relforknumber;
--SELECT pg_relation_size('test_io_strategy'::regclass) / 8192 AS relsize;
SELECT relfilenode FROM pg_class WHERE oid  = 'test_io_strategy'::regclass;
VACUUM (FULL) test_io_strategy;
--SELECT relfilenode FROM pg_class WHERE oid  = 'test_io_strategy'::regclass;
SELECT relforknumber, count(*) AS bufcount FROM pg_buffercache WHERE relfilenode = (SELECT relfilenode FROM pg_class WHERE oid  = 'test_io_strategy'::regclass) GROUP BY relforknumber;
SELECT pg_relation_size('test_io_strategy'::regclass) / 8192 AS relsize;
VACUUM (PARALLEL 0) test_io_strategy;
SELECT relforknumber, count(*) AS bufcount FROM pg_buffercache WHERE relfilenode = (SELECT relfilenode FROM pg_class WHERE oid  = 'test_io_strategy'::regclass) GROUP BY relforknumber;
SELECT pg_relation_size('test_io_strategy'::regclass) / 8192 AS relsize;
SELECT pg_stat_force_next_flush();
SELECT sum(alloc) AS io_sum_strategy_allocs_after FROM pg_stat_io WHERE io_context = 'Strategy' \gset
SELECT sum(read) AS io_sum_strategy_reads_after FROM pg_stat_io WHERE io_context = 'Strategy' \gset
SELECT :io_sum_strategy_allocs_after > :io_sum_strategy_allocs_before;
SELECT :io_sum_strategy_allocs_after, :io_sum_strategy_allocs_before;
SELECT :io_sum_strategy_reads_after > :io_sum_strategy_reads_before;
SELECT :io_sum_strategy_reads_after, :io_sum_strategy_reads_before;
--DROP TABLE test_io_strategy;