Alerting on Index-Scan to Sequential-Scan Flips

An index-scan to sequential-scan flip is one of the quietest regressions in a relational system: the query still returns correct rows, no error is logged, and the plan hash may barely change, yet the engine has silently abandoned an index and now reads every heap page on a large table. This runbook shows how to detect the flip from cumulative counters in pg_stat_user_indexes and pg_stat_user_tables, how to separate its four common causes, and how to wire an asyncio poller plus a Prometheus alert that pages before the sequential scan saturates I/O. It is the access-path counterpart to the signals tracked in Monitoring Index Usage Changes for Regression Signals.

Symptom Identification and Production Thresholds #

The flip is invisible to latency-only monitoring until the table grows large enough that a full scan hurts. Watch the counter deltas, not the absolute totals — the counters are monotonic since the last pg_stat_reset(), so every threshold below is expressed as a rate over a fixed window. Treat each as a hard trigger:

1. Index scan stall. The idx_scan delta for a named index falls to 0 over a rolling 10-minute window while the parent relation’s seq_scan delta rises by $\ge 30$ in the same window. A live index that suddenly stops incrementing while table scans climb is the defining signature of a flip.
2. Sequential-scan spike on a large table. seq_scan on a relation with $n_{live,tup} \ge 10,000,000$ rises at more than 6 scans per minute, or seq_tup_read grows by more than 50,000,000 rows over 5 minutes. On a ten-million-row table a single sequential scan reads every heap page, so even a low scan count is expensive.
3. p95 latency jump. The p95 execution time for the affected query fingerprint increases by $\ge 40\%$ versus the trailing 24-hour baseline, and the increase correlates within 2 minutes of the first seq_scan delta breach.
4. Buffer read amplification. heap_blks_read for the relation (from pg_statio_user_tables) grows more than $4\times$ its trailing-hour average while the shared-buffer hit ratio for that relation drops below 0.90.
5. Index ratio collapse. The relation’s index-scan share, idx_scan / (idx_scan + seq_scan) computed on deltas, falls below 0.50 for a table that historically ran above 0.95.

When condition 1 fires together with condition 2 or 3, escalate immediately: that pairing is a confirmed flip on a table large enough to matter, not statistical noise from a low-traffic query.

Root Cause Analysis #

A flip has four dominant causes. Each leaves a distinct fingerprint in the catalog, so diagnose before you remediate.

Stale statistics. After a bulk load, a large DELETE, or partition churn, the planner’s row estimate for the indexed predicate inflates until a sequential scan looks cheaper than an index scan plus heap fetches. This is the most common cause. Find relations whose write volume has outrun the last analyze:

-- PostgreSQL: tables analyzed long ago relative to their write churn
SELECT relname,
       n_live_tup,
       n_mod_since_analyze,
       round(100.0 * n_mod_since_analyze / GREATEST(n_live_tup, 1), 1) AS pct_churn,
       seq_scan, idx_scan,
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup >= 10000000
  AND n_mod_since_analyze > 0.10 * GREATEST(n_live_tup, 1)
ORDER BY pct_churn DESC;

A disabled or invalid index. A concurrent build that failed leaves an INVALID index the planner will not use; a maintenance script may have run UPDATE pg_index SET indisvalid = false or the index may have been dropped in a migration. List every index the planner is currently allowed to use and its live scan count:

SELECT i.relname AS index_name,
       t.relname AS table_name,
       ix.indisvalid,
       ix.indisready,
       s.idx_scan
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_class t ON t.oid = ix.indrelid
JOIN pg_stat_user_indexes s ON s.indexrelid = ix.indexrelid
WHERE t.relname = 'orders_events'
ORDER BY s.idx_scan;

An index with indisvalid = false or a long-lived idx_scan = 0 on a hot table is the smoking gun.

Parameter sniffing across bind ranges. A plan cached for a selective bind value is reused for a non-selective one; above roughly a 0.20 selectivity fraction the optimizer legitimately prefers a sequential scan, so the flip appears only for certain parameters. Segment the fingerprint by bind quartile rather than aggregating, and correlate the operator change with Detecting Join Type Shifts in Execution Plans, which shares the same plan-cache reuse root cause.

Cost-model configuration change. A change to random_page_cost, seq_page_cost, or effective_cache_size — often from a storage migration or a postgresql.conf rollout — reprices index access globally. Compare the running values against your golden config:

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('random_page_cost', 'seq_page_cost',
               'effective_cache_size', 'enable_indexscan', 'enable_seqscan');

If enable_indexscan is off or random_page_cost has drifted above 4.0 on SSD-class storage, the planner was told to prefer scans. When the flip stems from a genuinely missing or dropped index rather than a mispriced one, pivot to Detecting Missing Indexes from pg_stat_user_indexes.

Step-by-Step Remediation #

The following poller samples the catalog on a fixed interval, computes counter deltas per relation, and exports a gauge that the alert rule below evaluates. It is production-shaped: async I/O against a read replica, structured logs, and OpenTelemetry metrics.

1. Deploy the delta poller. Run it against a read replica so the sampling load never touches the primary.

import asyncio
from dataclasses import dataclass

import asyncpg
import structlog
from opentelemetry import metrics

log = structlog.get_logger("index.flip.detector")
meter = metrics.get_meter("index.flip.detector")

RATIO_FLOOR = 0.50          # index-scan share below this on a large table is a flip
LARGE_TABLE_ROWS = 10_000_000
SEQ_SCAN_RATE_TRIGGER = 6.0  # sequential scans per minute
POLL_INTERVAL_S = 30

_ratio_gauge = meter.create_gauge(
    "index_scan_ratio", description="idx_scan / (idx_scan + seq_scan) per relation")
_flip_counter = meter.create_counter(
    "index_flip_detected_total", description="confirmed index-to-seqscan flips")

SAMPLE_SQL = """
SELECT relname, n_live_tup, seq_scan, idx_scan, seq_tup_read
FROM pg_stat_user_tables
WHERE n_live_tup >= $1
"""


@dataclass
class Sample:
    seq_scan: int
    idx_scan: int


async def _fetch(pool: asyncpg.Pool) -> dict[str, Sample]:
    async with pool.acquire() as conn:
        rows = await conn.fetch(SAMPLE_SQL, LARGE_TABLE_ROWS)
    return {r["relname"]: Sample(r["seq_scan"], r["idx_scan"]) for r in rows}


async def poll(dsn: str) -> None:
    pool = await asyncpg.create_pool(dsn, min_size=1, max_size=2)
    prev: dict[str, Sample] = await _fetch(pool)
    try:
        while True:
            await asyncio.sleep(POLL_INTERVAL_S)
            cur = await _fetch(pool)
            for name, now in cur.items():
                was = prev.get(name)
                if was is None:
                    continue
                d_seq = now.seq_scan - was.seq_scan
                d_idx = now.idx_scan - was.idx_scan
                total = d_seq + d_idx
                if total == 0:
                    continue
                ratio = d_idx / total
                seq_rate = d_seq / (POLL_INTERVAL_S / 60.0)
                _ratio_gauge.set(ratio, {"relation": name})
                if ratio < RATIO_FLOOR and seq_rate >= SEQ_SCAN_RATE_TRIGGER:
                    _flip_counter.add(1, {"relation": name})
                    log.warning("index_flip_detected", relation=name,
                                index_ratio=round(ratio, 3),
                                seq_scan_rate=round(seq_rate, 2), d_idx=d_idx)
            prev = cur
    finally:
        await pool.close()


if __name__ == "__main__":
    import os
    asyncio.run(poll(os.environ["QP_REPLICA_DSN"]))

2. Confirm the flip with a warning log line. A real flip emits one structured record naming the relation and the collapsed ratio:

2026-07-18T14:07:52Z [warning] index_flip_detected relation=orders_events index_ratio=0.118 seq_scan_rate=11.4 d_idx=0

3. Wire the Prometheus alert. The gauge crossing the floor for two evaluation periods on a large table pages on-call; the for clause suppresses single-sample noise.

groups:
  - name: index-access-path
    rules:
      - alert: IndexScanToSeqScanFlip
        expr: index_scan_ratio < 0.50 and on(relation) (index_flip_detected_total offset 1m) < index_flip_detected_total
        for: 2m
        labels:
          severity: page
          team: database-sre
        annotations:
          summary: "Index-to-seqscan flip on {{ $labels.relation }}"
          description: "index_scan_ratio={{ $value | printf \"%.3f\" }} sustained below 0.50 on a >=10M-row table."

4. Refresh statistics first. For the stale-stats case, a targeted analyze is the lowest-risk fix and often restores the index plan within one autovacuum cycle:

ANALYZE VERBOSE orders_events;

Expected output confirms the sample size the planner will now use:

INFO:  analyzing "public.orders_events"
INFO:  "orders_events": scanned 30000 of 812444 pages, containing 3690000 live rows
ANALYZE

5. Pin the plan only if analyze does not clear it. Apply a narrow correction — reindex an invalid index with REINDEX INDEX CONCURRENTLY, or attach a per-statement hint or plan guide. Never set enable_seqscan = off globally; that masks the symptom and distorts every other plan on the instance.

Verification Checklist #

Work through each item before you resolve the alert; the fingerprint is not healthy until every box is checked:

• [ ] index_scan_ratio for the affected relation has recovered above 0.90 on the last three poll cycles.
• [ ] The idx_scan counter on the target index is incrementing again (non-zero delta over 10 minutes).
• [ ] seq_scan rate on the relation has fallen back under 6 per minute.
• [ ] EXPLAIN (ANALYZE, BUFFERS) on the replica shows an Index Scan or Index Only Scan node for the query, not a Seq Scan.
• [ ] n_mod_since_analyze for the relation is below 10% of n_live_tup after the targeted ANALYZE.
• [ ] No indisvalid = false index remains on the relation.
• [ ] p95 latency for the fingerprint is within 5% of its pre-flip 24-hour baseline.
• [ ] Any temporary hint or SET has been reverted and the corrected plan survives a fresh connection.

Compatibility and Engine-Specific Notes #

The detection shape — sample cumulative access counters, diff them, alert on a collapsed index ratio — ports across engines, but the source views and field names differ.

MySQL exposes index usage through performance_schema rather than a dedicated stat view, so the poller must join against table_io_waits_summary_by_index_usage and treat a null INDEX_NAME as the sequential path. On distributed engines a single logical table is range-sharded, so a flip can occur on one range while others stay on the index; sample per range and alert on the worst-case ratio rather than a table-wide average.

Related #

• ← Back to Monitoring Index Usage Changes for Regression Signals (parent topic)
• Turn a flip caused by a genuinely absent index into a fix: Detecting Missing Indexes from pg_stat_user_indexes
• Correlate the access-path change with operator shifts: Detecting Join Type Shifts in Execution Plans
• Wider context: Regression Detection & Rule Engines

← Back to Monitoring Index Usage Changes for Regression Signals