test_memory_tracking.sql

-- Test to verify VACUUM memory usage tracking functionality
-- Test cases: serial vacuum and parallel vacuum, with and without resets

-- Limit parallel workers to avoid slowdown
SET max_parallel_maintenance_workers = 1;

-- Clean up
DROP TABLE IF EXISTS serial_test CASCADE;
DROP TABLE IF EXISTS parallel_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;

-- Clean up
DROP TABLE serial_test;
DROP TABLE parallel_test;

\echo ''
\echo '=== Expected Results ==='
\echo 'Test 1 (Serial, 64KB): Should show "total memory usage: X.XX MB of 0.06 MB" where X > 0.06'
\echo '                       Multiple index scans due to memory constraints'
\echo 'Test 2 (Serial, 64MB): Should show "total memory usage: X.XX MB of 64.00 MB" where X < 64'
\echo '                       Single index scan, no memory resets'
\echo 'Test 3 (Parallel, 64KB): Should show "total memory usage: X.XX MB of 0.06 MB" where X > 0.06'
\echo '                         "launched X parallel vacuum worker", multiple index scans'
\echo 'Test 4 (Parallel, 64MB): Should show "total memory usage: X.XX MB of 64.00 MB" where X < 64'
\echo '                         May launch parallel worker, single index scan'