Detecting Missing Indexes from pg_stat_user_indexes

Missing indexes announce themselves in catalog statistics long before they show up as a page in an incident channel: a table accumulates sequential scans, its index scans stay flat, and seq_tup_read climbs faster than the table grows. This runbook shows how to read pg_stat_user_tables and pg_stat_user_indexes to isolate genuinely under-indexed relations, separate them from tables that are simply small or write-heavy, and hand a clean candidate to the recommendation workflow — with exact numeric breach conditions, diagnostic SQL, a runnable async detector, and a verification checklist.

This guide is the catalog-stats entry point for Index Recommendation Workflows from Plan Regressions; the candidate it produces is the artifact that stage consumes. It reads the same access-path signals tracked by Monitoring Index Usage Changes for Regression Signals and correlates them with the structural evidence in Detecting Join Type Shifts in Execution Plans.

Symptom Identification and Production Thresholds #

Catalog counters are cumulative since the last pg_stat_reset, so every threshold below is expressed as a rate or a ratio, never a raw lifetime count. Treat each as a hard numeric breach condition, not a guideline:

1. High sequential-scan share on a large table. seq_scan exceeds idx_scan by more than $10\times$ on a relation whose n_live_tup is above 500000. A million-row table that the planner reaches by sequential scan ten times more often than by index is under-indexed for its hottest predicate.
2. A dead index. An existing index shows idx_scan = 0 for a continuous 14 days since the last stats_reset. It costs write amplification and buys nothing; flag it for removal, not addition.
3. Runaway rows read per scan. seq_tup_read / seq_scan exceeds 50000 — each sequential scan is dragging fifty thousand rows through the buffer cache to satisfy a predicate that an index could answer in tens of rows.
4. Read-growth outpacing table growth. Week-over-week seq_tup_read grows more than 20% while n_live_tup grows less than 5%. The workload is scanning the same data harder, which is the signature of a predicate whose index was dropped or never existed.
5. Cache pressure from scan volume. The table’s heap_blks_read (from pg_statio_user_tables) exceeds heap_blks_hit on a relation above 500000 rows — sequential scans are evicting the working set from shared_buffers.

When condition 1 fires together with condition 3 or 5 on the same relation, escalate to candidate synthesis immediately; that pair is a near-certain missing index rather than statistical noise.

Root Cause Analysis #

Two failure domains produce most missing-index signals, and telling them apart is what stops the workflow from recommending an index that will never be used.

A hot predicate with no supporting index. The workload filters on a column the planner cannot reach except by full scan. Confirm which large tables are scan-dominated and how much data each scan drags:

SELECT relname,
       n_live_tup,
       seq_scan,
       idx_scan,
       seq_tup_read,
       round(seq_scan::numeric / GREATEST(idx_scan, 1), 1) AS seq_idx_ratio,
       round(seq_tup_read::numeric / GREATEST(seq_scan, 1), 0) AS rows_per_seq_scan
FROM pg_stat_user_tables
WHERE n_live_tup > 500000
  AND seq_scan > idx_scan * 10
ORDER BY rows_per_seq_scan DESC
LIMIT 20;

Any row with rows_per_seq_scan above 50000 is a strong candidate; the relname plus the filter column from the regressed plan is everything the recommendation stage needs.

A dead or redundant index masquerading as coverage. An index exists but the planner never chooses it — because it leads with the wrong column, its statistics are stale, or the query shape changed. Confirm which indexes earn their write cost:

SELECT s.relname,
       s.indexrelname,
       s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset
FROM pg_stat_user_indexes s
JOIN pg_database d ON d.datname = current_database()
WHERE s.idx_scan = 0
  AND pg_relation_size(s.indexrelid) > 8 * 1024 * 1024
ORDER BY pg_relation_size(s.indexrelid) DESC;

An index at idx_scan = 0 that is larger than 8 MB and has been zero since a stats_reset more than 14 days ago is dead weight; recommending a new index on the same relation without dropping the dead one only compounds write amplification.

Step-by-Step Remediation #

Work through these in order. The goal is a validated candidate, not an applied index — DDL synthesis and application happen in the companion runbooks.

1. Snapshot the catalog and compute breach flags. Run the async detector below against a read replica. It joins table and index stats, applies the numeric thresholds, and emits one structured candidate per breaching relation.

from __future__ import annotations

import os
from dataclasses import dataclass

import asyncpg
import structlog
from opentelemetry import metrics, trace

log = structlog.get_logger("missing_index.detector")
tracer = trace.get_tracer("missing_index.detector")
meter = metrics.get_meter("missing_index.detector")

CANDIDATES = meter.create_counter("missing_index_candidates_total", unit="1")

SEQ_IDX_RATIO_MIN = 10.0
MIN_LIVE_TUP = 500_000
MIN_ROWS_PER_SEQ_SCAN = 50_000

DETECT_SQL = """
SELECT relname,
       n_live_tup,
       seq_scan,
       idx_scan,
       seq_tup_read,
       round(seq_scan::numeric / GREATEST(idx_scan, 1), 2) AS seq_idx_ratio,
       round(seq_tup_read::numeric / GREATEST(seq_scan, 1), 0) AS rows_per_seq_scan
FROM pg_stat_user_tables
WHERE n_live_tup > $1
  AND seq_scan > idx_scan * $2
ORDER BY rows_per_seq_scan DESC
"""


@dataclass(frozen=True)
class MissingIndexCandidate:
    relation: str
    n_live_tup: int
    seq_idx_ratio: float
    rows_per_seq_scan: int


async def detect(pool: asyncpg.Pool) -> list[MissingIndexCandidate]:
    with tracer.start_as_current_span("missing_index.detect") as span:
        rows = await pool.fetch(DETECT_SQL, MIN_LIVE_TUP, SEQ_IDX_RATIO_MIN)
        out: list[MissingIndexCandidate] = []
        for r in rows:
            if r["rows_per_seq_scan"] < MIN_ROWS_PER_SEQ_SCAN:
                continue
            cand = MissingIndexCandidate(
                relation=r["relname"],
                n_live_tup=int(r["n_live_tup"]),
                seq_idx_ratio=float(r["seq_idx_ratio"]),
                rows_per_seq_scan=int(r["rows_per_seq_scan"]),
            )
            CANDIDATES.add(1, {"relation": cand.relation})
            log.info("missing_index_candidate", relation=cand.relation,
                     seq_idx_ratio=cand.seq_idx_ratio,
                     rows_per_seq_scan=cand.rows_per_seq_scan)
            out.append(cand)
        span.set_attribute("candidate_count", len(out))
        return out


async def main() -> None:
    pool = await asyncpg.create_pool(
        dsn=os.environ["DETECT_REPLICA_DSN"], min_size=1, max_size=4, command_timeout=8.0,
    )
    try:
        for c in await detect(pool):
            log.info("emit", relation=c.relation, rows=c.n_live_tup)
    finally:
        await pool.close()

Expected output on a scan-dominated orders table:

2026-07-18T09:14:02Z [info] missing_index_candidate relation=orders seq_idx_ratio=41.7 rows_per_seq_scan=88214
2026-07-18T09:14:02Z [info] emit relation=orders rows=6120334

2. Confirm the driving predicate. Pull the regressed query’s filter columns from the plan artifact and confirm they match a column with no leading index. Do not synthesize a candidate on a column the workload does not actually filter on.

3. Rule out a dead existing index. Run the dead-index query from the previous section. If a zero-scan index already leads with the target column, the fix is to rebuild or reorder it, not to add another.

4. Hand off the candidate. Emit the relation, filter columns, and the three ratio metrics as the artifact consumed by Generating CREATE INDEX DDL from Regressed Plans. Detection stops here — it never applies DDL.

Verification Checklist #

Work through each item before promoting a candidate; every box must be checked.

• [ ] The detector was run against a read replica, never the primary.
• [ ] seq_idx_ratio for the relation exceeds 10.0 on the latest snapshot.
• [ ] rows_per_seq_scan for the relation exceeds 50000.
• [ ] The relation’s n_live_tup is above 500000 so the index is worth its write cost.
• [ ] The catalog counters were not reset within the observation window (stats_reset is older than the sampling period).
• [ ] No existing index already leads with the candidate’s target column.
• [ ] The candidate’s filter columns match the predicate in the regressed-plan artifact.
• [ ] Any dead index found on the same relation has been noted for separate removal.

Compatibility and Engine-Specific Notes #

The detection shape is portable, but the catalog surface that exposes scan counters differs sharply per engine. Map the fields before wiring a detector.

MySQL exposes unused indexes as a first-class sys.schema_unused_indexes view, so the dead-index branch is a direct query rather than a computed ratio; its performance_schema also drives per-digest rows_examined, the closest analog to seq_tup_read. On distributed engines the scan counters are per-node, so aggregate across nodes before applying the size filter or a hot range on one node will hide behind a cold cluster-wide average. Ground the cross-engine cost translation in Cost Estimation Mapping Across PostgreSQL and MySQL.

Related #

• ← Back to Index Recommendation Workflows from Plan Regressions
• Turn a candidate into DDL: Generating CREATE INDEX DDL from Regressed Plans
• Correlate with access-path drift: Monitoring Index Usage Changes for Regression Signals
• Separate index regressions from statistics regressions: Detecting Join Type Shifts in Execution Plans
• Cross-engine cost mapping: Cost Estimation Mapping Across PostgreSQL and MySQL