reproduce.sql

application/octet-stream

Download

\set ON_ERROR_STOP on
\timing on

\echo ''
\echo '============================================================'
\echo 'REPRO TEST SUITE: planner choice for partitioned table + LIMIT 1'
\echo '============================================================'
\echo ''

\echo 'SET synchronize_seqscans=off; for more determenistic behaviour'
SET synchronize_seqscans=off;
select version();
\echo '------------------------------------------------------------'
\echo 'TEST 1: Check correlation(ts_col) on child partitions'
\echo 'Meaning: high correlation means ts_col is close to heap order'
\echo 'SQL:'
\echo 'SELECT tablename, attname, correlation'
\echo 'FROM pg_stats'
\echo 'WHERE tablename IN (''fact_events_p2026w13'', ''fact_events_p2026w14'')'
\echo '  AND attname = ''ts_col'''
\echo 'ORDER BY tablename;'
\echo '------------------------------------------------------------'
SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename IN ('fact_events_p2026w13', 'fact_events_p2026w14')
  AND attname = 'ts_col'
ORDER BY tablename;

\echo ''
\echo '------------------------------------------------------------'
\echo 'TEST 2: Check estimated partition sizes'
\echo 'Meaning: reltuples/relpages help explain planner cost choices'
\echo 'SQL:'
\echo 'SELECT relname, reltuples, relpages'
\echo 'FROM pg_class'
\echo 'WHERE relname IN (''fact_events_p2026w13'', ''fact_events_p2026w14'')'
\echo 'ORDER BY relname;'
\echo '------------------------------------------------------------'
SELECT
    relname,
    reltuples,
    relpages
FROM pg_class
WHERE relname IN (
    'fact_events_p2026w13',
    'fact_events_p2026w14'
)
ORDER BY relname;

\echo ''
\echo '------------------------------------------------------------'
\echo 'TEST 3: Parent query with OR-ed timestamp ranges and LIMIT 1'
\echo 'Meaning: main regression/corner-case candidate'
\echo 'Expectation: PG17 may choose a bad Seq Scan here'
\echo 'SQL:'
\echo 'EXPLAIN (ANALYZE, BUFFERS, SETTINGS)'
\echo 'SELECT 1'
\echo 'FROM fact_events'
\echo 'WHERE kind_col IN (''k_a'', ''k_b'', ''k_c'', ''k_d'', ''k_e'')'
\echo 'AND ('
\echo '       (ts_col >= ''2026-03-28 07:42:30.405987'' AND ts_col < ''2026-03-28 08:00:00'')'
\echo '    OR (ts_col >= ''2026-03-31 12:00:00''        AND ts_col < ''2026-03-31 12:10:25.420965'')'
\echo ')'
\echo 'LIMIT 1;'
\echo '------------------------------------------------------------'
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT 1
FROM fact_events
WHERE kind_col IN (
    'k_a', 'k_b', 'k_c', 'k_d', 'k_e'
)
AND (
       (ts_col >= '2026-03-28 07:42:30.405987' AND ts_col < '2026-03-28 08:00:00')
    OR (ts_col >= '2026-03-31 12:00:00'        AND ts_col < '2026-03-31 12:10:25.420965')
)
LIMIT 1;

\echo ''
\echo '------------------------------------------------------------'
\echo 'TEST 4: Parent query with a single timestamp range and LIMIT 1'
\echo 'Meaning: control case; planner should usually use ts_col index'
\echo 'SQL:'
\echo 'EXPLAIN (ANALYZE, BUFFERS, SETTINGS)'
\echo 'SELECT 1'
\echo 'FROM fact_events'
\echo 'WHERE kind_col IN (''k_a'', ''k_b'', ''k_c'', ''k_d'', ''k_e'')'
\echo 'AND ts_col >= ''2026-03-28 07:42:30.405987'''
\echo 'AND ts_col <  ''2026-03-28 08:00:00'''
\echo 'LIMIT 1;'
\echo '------------------------------------------------------------'
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT 1
FROM fact_events
WHERE kind_col IN (
    'k_a', 'k_b', 'k_c', 'k_d', 'k_e'
)
AND ts_col >= '2026-03-28 07:42:30.405987'
AND ts_col <  '2026-03-28 08:00:00'
LIMIT 1;

\echo ''
\echo '------------------------------------------------------------'
\echo 'TEST 5: Force bitmap path on child partition'
\echo 'Meaning: disable seqscan and plain indexscan to force bitmap path'
\echo 'Expectation: proves ts_col-based bitmap path is viable'
\echo 'SQL:'
\echo 'SET enable_seqscan = off;'
\echo 'SET enable_indexscan = off;'
\echo ''
\echo 'EXPLAIN (ANALYZE, BUFFERS, SETTINGS)'
\echo 'SELECT 1'
\echo 'FROM fact_events_p2026w13'
\echo 'WHERE kind_col IN (''k_a'', ''k_b'', ''k_c'', ''k_d'', ''k_e'')'
\echo 'AND ('
\echo '       (ts_col >= ''2026-03-28 07:42:30.405987'' AND ts_col < ''2026-03-28 08:00:00'')'
\echo '    OR (ts_col >= ''2026-03-31 12:00:00''        AND ts_col < ''2026-03-31 12:10:25.420965'')'
\echo ')'
\echo 'LIMIT 1;'
\echo '------------------------------------------------------------'
SET enable_seqscan = off;
SET enable_indexscan = off;

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT 1
FROM fact_events_p2026w13
WHERE kind_col IN (
    'k_a', 'k_b', 'k_c', 'k_d', 'k_e'
)
AND (
       (ts_col >= '2026-03-28 07:42:30.405987' AND ts_col < '2026-03-28 08:00:00')
    OR (ts_col >= '2026-03-31 12:00:00'        AND ts_col < '2026-03-31 12:10:25.420965')
)
LIMIT 1;

\echo ''
\echo '------------------------------------------------------------'
\echo 'TEST 6: Reset planner GUCs back to defaults'
\echo 'SQL:'
\echo 'RESET enable_seqscan;'
\echo 'RESET enable_indexscan;'
\echo '------------------------------------------------------------'
RESET enable_seqscan;
RESET enable_indexscan;

\echo ''
\echo '------------------------------------------------------------'
\echo 'TEST 7: Default child-partition query with OR-ed ranges and LIMIT 1'
\echo 'Meaning: compare default child plan vs forced bitmap plan'
\echo 'SQL:'
\echo 'EXPLAIN (ANALYZE, BUFFERS, SETTINGS)'
\echo 'SELECT 1'
\echo 'FROM fact_events_p2026w13'
\echo 'WHERE kind_col IN (''k_a'', ''k_b'', ''k_c'', ''k_d'', ''k_e'')'
\echo 'AND ('
\echo '       (ts_col >= ''2026-03-28 07:42:30.405987'' AND ts_col < ''2026-03-28 08:00:00'')'
\echo '    OR (ts_col >= ''2026-03-31 12:00:00''        AND ts_col < ''2026-03-31 12:10:25.420965'')'
\echo ')'
\echo 'LIMIT 1;'
\echo '------------------------------------------------------------'
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT 1
FROM fact_events_p2026w13
WHERE kind_col IN (
    'k_a', 'k_b', 'k_c', 'k_d', 'k_e'
)
AND (
       (ts_col >= '2026-03-28 07:42:30.405987' AND ts_col < '2026-03-28 08:00:00')
    OR (ts_col >= '2026-03-31 12:00:00'        AND ts_col < '2026-03-31 12:10:25.420965')
)
LIMIT 1;

\echo ''
\echo '============================================================'
\echo 'END OF REPRO TEST SUITE'
\echo '============================================================'
\echo ''