test_memory_tracking.sql

-- Test to verify VACUUM memory usage tracking functionality
-- Test cases: serial vacuum, parallel vacuum, no indexes, and edge cases

-- Limit parallel workers to avoid slowdown
SET max_parallel_maintenance_workers = 1;
SET client_min_messages = WARNING;  -- Reduce noise

-- Clean up
DROP TABLE IF EXISTS serial_test CASCADE;
DROP TABLE IF EXISTS parallel_test CASCADE;
DROP TABLE IF EXISTS no_index_test CASCADE;
DROP TABLE IF EXISTS empty_test CASCADE;
DROP TABLE IF EXISTS index_cleanup_off_test CASCADE;

-- ===== Serial VACUUM Tests (1 index only) =====
CREATE TABLE serial_test (
    id int,
    data text
);

-- Only 1 index - parallel vacuum will NOT be used
CREATE INDEX idx_serial_test ON serial_test(id);

-- Test 1: Serial VACUUM with small memory - expect multiple index scans
\echo '=== Test 1: Serial VACUUM, 64KB limit, 100,000 dead tuples (expect multiple index scans) ==='
INSERT INTO serial_test SELECT i, 'data_' || i FROM generate_series(1, 100000) i;
DELETE FROM serial_test WHERE id % 2 = 1;  -- 50,000 dead tuples
SET maintenance_work_mem = '64kB';
VACUUM VERBOSE serial_test;

-- Test 2: Serial VACUUM with large memory - expect single index scan
\echo ''
\echo '=== Test 2: Serial VACUUM, 64MB limit, 100,000 dead tuples (expect single index scan) ==='
TRUNCATE serial_test;
INSERT INTO serial_test SELECT i, 'data_' || i FROM generate_series(1, 100000) i;
DELETE FROM serial_test WHERE id % 2 = 1;  -- 50,000 dead tuples
SET maintenance_work_mem = '64MB';
VACUUM VERBOSE serial_test;

-- ===== Parallel VACUUM Tests (2+ indexes) =====
CREATE TABLE parallel_test (
    id int,
    data text
);

-- 2 indexes - parallel vacuum WILL be used
CREATE INDEX idx1_parallel_test ON parallel_test(id);
CREATE INDEX idx2_parallel_test ON parallel_test(id);

-- Test 3: Parallel VACUUM with small memory - expect multiple index scans
\echo ''
\echo '=== Test 3: Parallel VACUUM, 64KB limit, 100,000 dead tuples (expect multiple index scans) ==='
INSERT INTO parallel_test SELECT i, 'data_' || i FROM generate_series(1, 100000) i;
DELETE FROM parallel_test WHERE id % 2 = 1;  -- 50,000 dead tuples
SET maintenance_work_mem = '64kB';
VACUUM VERBOSE parallel_test;

-- Test 4: Parallel VACUUM with large memory - expect single index scan
\echo ''
\echo '=== Test 4: Parallel VACUUM, 64MB limit, 100,000 dead tuples (expect single index scan) ==='
TRUNCATE parallel_test;
INSERT INTO parallel_test SELECT i, 'data_' || i FROM generate_series(1, 100000) i;
DELETE FROM parallel_test WHERE id % 2 = 1;  -- 50,000 dead tuples
SET maintenance_work_mem = '64MB';
VACUUM VERBOSE parallel_test;

-- ===== No Index Tests =====
\echo ''
\echo '=== Test 5: VACUUM on table with NO indexes (one-pass strategy) ==='
CREATE TABLE no_index_test (
    id int,
    data text
);
INSERT INTO no_index_test SELECT i, 'data_' || i FROM generate_series(1, 100000) i;
DELETE FROM no_index_test WHERE id % 2 = 1;  -- 50,000 dead tuples
SET maintenance_work_mem = '64kB';
VACUUM VERBOSE no_index_test;

-- ===== Empty/Small Table Tests =====
\echo ''
\echo '=== Test 6: VACUUM on empty table ==='
CREATE TABLE empty_test (
    id int,
    data text
);
CREATE INDEX idx_empty_test ON empty_test(id);
SET maintenance_work_mem = '64MB';
VACUUM VERBOSE empty_test;

-- ===== Index Cleanup Disabled Tests =====
\echo ''
\echo '=== Test 7: VACUUM with INDEX_CLEANUP = off ==='
CREATE TABLE index_cleanup_off_test (
    id int,
    data text
);
CREATE INDEX idx_cleanup_off ON index_cleanup_off_test(id);
INSERT INTO index_cleanup_off_test SELECT i, 'data_' || i FROM generate_series(1, 100000) i;
DELETE FROM index_cleanup_off_test WHERE id % 2 = 1;  -- 50,000 dead tuples
SET maintenance_work_mem = '64kB';
VACUUM (VERBOSE, INDEX_CLEANUP off) index_cleanup_off_test;

-- Clean up
DROP TABLE serial_test;
DROP TABLE parallel_test;
DROP TABLE no_index_test;
DROP TABLE empty_test;
DROP TABLE index_cleanup_off_test;

\echo ''
\echo '=== Expected Results Summary ==='
\echo 'Test 1 (Serial, 64KB):        Multiple index scans (num_dead_items_resets > 1)'
\echo 'Test 2 (Serial, 64MB):        Single index scan (num_dead_items_resets = 1)'
\echo 'Test 3 (Parallel, 64KB):      Multiple index scans with parallel worker'
\echo 'Test 4 (Parallel, 64MB):      Single index scan, may use parallel worker'
\echo 'Test 5 (No indexes):          No index scans (num_dead_items_resets = 0, but memory line still shown)'
\echo 'Test 6 (Empty table):         No index scans, minimal or zero memory usage'
\echo 'Test 7 (INDEX_CLEANUP off):   num_dead_items_resets = 1, memory usage shown'
\echo ''
\echo 'All tests should display: "memory usage: total X.XX MB used across N index scan(s) (max X.XX MB at once)"'
\echo 'Note: num_dead_items_resets counts how many times dead_items storage was reset (filled up)'