Missing Index Detection

Find missing indexes.

Overview

Missing index detection finds indexes the database needs but does not have. Sequential scans on large tables are the obvious symptom; subtle ones include index-only scans that fall back to heap fetches because the right covering index is missing. The discipline is in systematic detection (pg_stat_user_indexes, RDS Performance Insights, EXPLAIN ANALYZE on top slow queries) rather than waiting for slow-query incidents to surface the gaps.

• Find missing indexes. Per-query opportunity detection; the database tells you which queries are scanning when they should be seeking.
• Sequential scan detection. Large Seq Scans on columns that could be indexed; pg_stat_user_tables surfaces the ratio of seq_scan to idx_scan.
• pg_stat_user_indexes. Postgres index usage statistics; reveals which indexes are unused (waste) and which are missing (opportunity).
• Performance Insights plus EXPLAIN ANALYZE. RDS Performance Insights surfaces top SQL by load; EXPLAIN ANALYZE on those queries produces evidence of plan choice.

The approach

The practical approach is to monitor seq_scan vs idx_scan ratios on every table (the metric is in pg_stat_user_tables), EXPLAIN ANALYZE the top slow queries from Performance Insights or pg_stat_statements, propose candidate indexes against the WHERE/JOIN/ORDER BY columns, validate with add-then-re-EXPLAIN to measure actual impact, and document each new index with its supporting query so the index inventory stays reviewable.

• Monitor sequential scans. pg_stat_user_tables seq_scan vs idx_scan; the ratio surfaces tables that need indexing attention.
• EXPLAIN slow queries. Read plans for top slow queries from pg_stat_statements; the plan tells you which step is the bottleneck.
• Validate with add. Add candidate index, re-EXPLAIN, measure the actual improvement; the index either earns its keep or gets dropped.
• Per-query candidate plus documented addition. WHERE, JOIN, ORDER BY columns drive the candidate; per-index supporting query committed to the schema documentation.

Why this compounds

Missing-index discipline compounds across queries and tables. Each detected and added index produces ongoing query-time savings; each pg_stat_user_indexes review teaches the team where their query patterns evolved past the original index design; the team builds intuition for which queries deserve indexes versus which deserve query-shape changes.

• Query performance. Right indexes produce fast queries; the user-facing latency drops where the index covers the access pattern.
• Resource utilization. Less Seq Scan means less IO; the database serves more queries per IOPS unit.
• Engineering culture. Evidence-based optimization replaces guesswork; the team adds indexes with data, not gut feel.
• Institutional knowledge. Each detection teaches database patterns; the team learns which query shapes need which index types.

Missing-index discipline is a database discipline that pays off across years. Nova AI Ops integrates with database telemetry, surfaces index patterns, and supports the team’s database engineering discipline.