SAOP-saturation-test.sql

-- ============================================================================
-- 1. PREPARATION & CLEANUP
-- ============================================================================
DROP TABLE IF EXISTS partial_index_test;

-- Create a base table with a status column to anchor the partial indexes
CREATE TABLE partial_index_test (
    id SERIAL PRIMARY KEY,
    status_id INT NOT NULL,
    payload TEXT,
    created_at TIMESTAMP DEFAULT NOW()
) WITH (autovacuum_enabled = false);

-- ============================================================================
-- 2. DATA POPULATION
-- ============================================================================
-- Insert 1,000,000 rows with status_ids ranging from 1 to 110.
-- This ensures data spreads across all 99 indexed values and some unindexed values.
INSERT INTO partial_index_test (status_id, payload)
SELECT 
    (random() * 109 + 1)::int,
    md5(random()::text)
FROM generate_series(1, 1000000);

-- ============================================================================
-- 3. DYNAMICALLY GENERATE 49 PARTIAL INDEXES
-- ============================================================================
-- Using a DO block to cleanly generate 49 partial indexes on the 'id' column,
-- filtered by the 'status_id' predicate.
DO $$
BEGIN
    FOR i IN 1..49 LOOP
        EXECUTE format(
            'CREATE INDEX idx_partial_status_%s ON partial_index_test (id) WHERE status_id = %s;', 
            i, i
        );
    END LOOP;
END $$;

-- Gather statistics so the optimizer can make informed decisions
ANALYZE partial_index_test;

-- Disable sequence scan to force the code path...
SET enable_seqscan = OFF;

-- ============================================================================
-- 4. TEST QUERIES (PATCH EVALUATION SUITE)
-- ============================================================================

-- ----------------------------------------------------------------------------
-- Test Suite A: IN Clause (49 elements)
-- ----------------------------------------------------------------------------
-- Tests how the planner scales when evaluating a long standard IN list 
-- against matching partial index predicates.
EXPLAIN (ANALYZE, SUMMARY)
SELECT id, status_id 
FROM partial_index_test 
WHERE status_id IN (
    1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,
    21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,
    41,42,43,44,45,46,47,48,49
);

-- ----------------------------------------------------------------------------
-- Test Suite B: ANY ARRAY Clause (49 elements)
-- ----------------------------------------------------------------------------
-- Targets ScalarArrayOpExpr optimization paths specifically. The planner should 
-- ideally treat this identically to or better than the IN clause variant.
EXPLAIN (ANALYZE, SUMMARY)
SELECT id, status_id 
FROM partial_index_test 
WHERE status_id = ANY(ARRAY[
    1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,
    21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,
    41,42,43,44,45,46,47,48,49
]);

-- ----------------------------------------------------------------------------
-- Test Suite C: Pure OR Chain Equivalent (49 elements)
-- ----------------------------------------------------------------------------
-- Forces the engine down a BoolExpr (OR_EXPR) parsing tree. Useful for checking 
-- if the patch interferes with Postgres' internal convert_ANY_to_OR routines.
EXPLAIN (ANALYZE, SUMMARY)
SELECT id, status_id 
FROM partial_index_test 
WHERE (
    status_id = 1   OR status_id = 2   OR status_id = 3   OR status_id = 4   OR status_id = 5   OR 
    status_id = 6   OR status_id = 7   OR status_id = 8   OR status_id = 9   OR status_id = 10  OR 
    status_id = 11  OR status_id = 12  OR status_id = 13  OR status_id = 14  OR status_id = 15  OR 
    status_id = 16  OR status_id = 17  OR status_id = 18  OR status_id = 19  OR status_id = 20  OR 
    status_id = 21  OR status_id = 22  OR status_id = 23  OR status_id = 24  OR status_id = 25  OR 
    status_id = 26  OR status_id = 27  OR status_id = 28  OR status_id = 29  OR status_id = 30  OR 
    status_id = 31  OR status_id = 32  OR status_id = 33  OR status_id = 34  OR status_id = 35  OR 
    status_id = 36  OR status_id = 37  OR status_id = 38  OR status_id = 39  OR status_id = 40  OR 
    status_id = 41  OR status_id = 42  OR status_id = 43  OR status_id = 44  OR status_id = 45  OR 
    status_id = 46  OR status_id = 47  OR status_id = 48  OR status_id = 49
);
-- ----------------------------------------------------------------------------
-- Test Suite D: Mixed Array / Partial Out-of-Bounds Case
-- ----------------------------------------------------------------------------
-- Mixes values covered by partial indexes (40-49) with values completely 
-- outside of index coverage (50+). Assures the patch successfully ignores 
-- or gracefully passes non-matching elements without breaking index scans.
EXPLAIN (ANALYZE, SUMMARY)
SELECT count(*) 
FROM partial_index_test 
WHERE status_id IN (
    40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60
);