Missing Index Query Degradation

partial

Summary

A query executes without an appropriate index, causing a sequential scan that is orders of magnitude slower than an indexed lookup and saturates CPU and I/O for all other queries on the same database instance.

Description

Index-driven query performance relies on the database optimizer choosing an index scan over a sequential scan. When an appropriate index is absent, the optimizer defaults to a sequential scan: reading every row in the table from disk, filtering for matching rows. On a 100M-row table, a sequential scan that could have been a millisecond index lookup instead takes 30–120 seconds and reads gigabytes of data from disk.

PostgreSQL optimizer threshold: for small tables, the optimizer correctly chooses a sequential scan (cheaper than index overhead). As a table grows past ~1000 rows, indexed access becomes faster for selective queries. The optimizer makes this decision based on cost estimates using table statistics. When statistics are stale (ANALYZE not run recently), the optimizer may choose a sequential scan even for large tables.

Index regression (most dangerous variant): an index that was present is dropped. All queries that relied on it silently degrade. This occurs from: - Schema migration that drops an index by mistake (DROP INDEX executed on

wrong index name or wrong environment)

- Rails / Alembic migration that removes an index during a rename - Manual index removal as a "quick fix" for index bloat without understanding

which queries use it

Index regression is insidious because: the query continues to return correct results (just slowly), no error is produced, and the degradation may not be noticed until the table has grown large enough for the sequential scan to become visibly slow.

Table growth threshold: PostgreSQL's cost model may favor an index scan at 10M rows but switch to a sequential scan at 100M rows if the table statistics suggest the query is not selective enough. A query that performed well for a year may degrade after a table growth event without any code change.

Diagnostic tools: - EXPLAIN (ANALYZE, BUFFERS): shows the actual query plan including whether

an index was used, and the buffer hit/read ratio.

- pg_stat_statements: tracks queries by their normalized form; identifies

high total_time queries.

- pg_stat_user_tables: seq_scan and seq_tup_read columns identify tables

with high sequential scan rates.

- auto_explain: logs the query plan for all queries exceeding a threshold.

Characteristics

Propagationisolated

Time to detectIf pg_stat_statements or auto_explain is configured with slow query logging, detectable within seconds of the first slow query. Without tooling, detected through user-visible latency spikes or alert firing: typically minutes.

Blast radiusThe slow query holds a connection for its full duration, consuming connection pool capacity. On a busy system, multiple concurrent slow queries can exhaust the connection pool, causing all other queries to queue at the pool boundary regardless of whether they use the missing index. Sequential scans also saturate shared_buffers and disk I/O, increasing latency for all concurrent queries. Blast radius can expand from the specific query to the full application.

Triggers

·New query pattern introduced without a corresponding index
·Table grows past optimizer threshold causing switch from index scan to sequential scan
·Index dropped accidentally during schema migration
·ANALYZE not run after bulk data load, causing stale statistics
·Query parameter makes the query non-selective, causing index to be skipped

Detection Signals

latency spikecpu saturation

Mitigation Strategies

Create the missing indexpreventscomplexity: low

EXPLAIN (ANALYZE) the slow query to confirm it is doing a sequential scan. CREATE INDEX CONCURRENTLY does not block reads or writes and is safe for production. For multi-column indexes, column order matters: put equality conditions before range conditions.

Enable auto_explain with log_min_duration for slow query detectioncomplexity: low

SET auto_explain.log_min_duration = '1s'; logs the full query plan for any query exceeding 1 second. This identifies missing indexes before they become severe by catching slow queries early when tables are smaller.

Use pg_stat_statements to identify high total_time queriescomplexity: low

SELECT query, total_exec_time, calls, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; identifies the queries consuming the most cumulative time: these are the candidates for index optimization. Reset stats after index changes to measure improvement.

Review all migrations that drop indexes before deployingpreventscomplexity: medium

Add a step in the migration review process that lists all indexes being dropped. For each dropped index, verify which queries use it (pg_stat_statements, code search) before proceeding. Index renames must be handled as add-then-drop.

Recovery Steps

1.Run EXPLAIN (ANALYZE, BUFFERS) on the slow query to confirm sequential scan
2.Check pg_stat_user_tables for the table: high seq_scan rate confirms the pattern
3.CREATE INDEX CONCURRENTLY on the relevant column(s): safe to run in production
4.Verify with EXPLAIN that the optimizer now uses the new index
5.Check pg_stat_statements to confirm query execution time dropped
6.If index was dropped by accident, identify the migration and re-add the index

Estimated recovery time: Index creation via CREATE INDEX CONCURRENTLY: minutes to hours depending on table size. A 100M-row table may take 30–60 minutes. Queries will continue to be slow during index creation. After creation, performance improves immediately.

Affected Systems

Patterns

index tablecqrsmaterialized view

Technologies

postgresql

Basis

One of the most common production database performance failures; PostgreSQL diagnostic tooling is well-documented

Run This Failure

Blast radius analysis for this failure mode within each scenario that carries it.

Content Management Platform →

Used In Architecture Scenarios

Content Management Platformmoderate

Read-Heavy Application

A CMS for publishing and serving structured content: articles, documentation, product pages, and localized variants: where read APIs serve 50–100x more traffic than editorial write APIs. PostgreSQL stores the content graph (articles, authors, categories, taxonomy) and workflow state (draft, in-review, scheduled, published). Redis caches published content objects for read APIs. Elasticsearch powers full-text content search with faceting and relevance ranking. Cache invalidation on publish must be fast and complete; N+1 query patterns on content relationship traversal are the dominant database performance risk during reads.