How to Find and Fix Missing Indexes in PostgreSQL

You ship a feature, tests pass, staging looks fine, and then production starts throwing timeouts on a query that worked perfectly two days ago. You check the logs and it's a simple SELECT ... WHERE customer_email = ?. Nothing fancy. But the table has 10 million rows now, and PostgreSQL is reading every single one of them to find a match.

No index. Full sequential scan. 200ms per query on a table that gets hit thousands of times per hour.

This is the most common PostgreSQL performance problem, and what makes it frustrating is how invisible it is during development. Your test database has 500 rows. A sequential scan on 500 rows takes microseconds. The test suite passes. The PR gets merged. The problem only appears when real data volumes meet real concurrency.

Finding the Problem

PostgreSQL tracks scan statistics in pg_stat_user_tables. This is your first stop:

SELECT
    schemaname,
    relname AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    CASE WHEN (seq_scan + idx_scan) > 0
        THEN round(100.0 * seq_scan / (seq_scan + idx_scan), 1)
        ELSE 0
    END AS sequential_scan_percentage,
    pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) AS table_size
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

What you're looking for: tables with a high seq_tup_read and a sequential_scan_percentage above 90%. These are tables where almost every query is doing a full table scan. If idx_scan is zero, no query on that table is using an index at all.

The seq_tup_read column is the total number of rows fetched by sequential scans since the last statistics reset. A table returning millions of rows through sequential scans is either missing an index or has one that the planner is choosing not to use.

Confirming with EXPLAIN

Once you've identified a suspicious table, confirm the sequential scan on a specific query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM sim_customers
WHERE customer_email = '[e-mail]';

Look for Seq Scan in the output. If you see Seq Scan on sim_customers with a high actual time, the planner has no index to work with. The Buffers section tells you exactly how much data was read -- a sequential scan on a 50MB table reads the entire 50MB.

After you add the index, run the same EXPLAIN ANALYZE again. You should see Index Scan or Index Only Scan instead, reading a handful of buffers instead of thousands.

Fixing It

Create the missing index using CONCURRENTLY:

CREATE INDEX CONCURRENTLY idx_sim_customers_email
    ON sim_customers (customer_email);

The CONCURRENTLY keyword is critical. Without it, CREATE INDEX takes an ACCESS EXCLUSIVE lock on the table -- blocking all reads and writes until the index is fully built. On a large table, that can take minutes. With CONCURRENTLY, the index builds in the background while normal operations continue. It takes longer and requires two table scans, but it avoids downtime.

After creating the index, verify it's being used:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM sim_customers
WHERE customer_email = '[e-mail]';

A query that was reading 6,400 buffers (50MB) in a sequential scan might now read 4 buffers with the index. That's the difference between 200ms and sub-millisecond.

Multi-Column Indexes

For queries filtering on multiple columns, a multi-column index is more efficient than separate single-column indexes:

CREATE INDEX CONCURRENTLY idx_orders_customer_date
    ON orders (customer_id, order_date);

Column order matters and it's not arbitrary:

1. Most selective column first -- the column that narrows down the most rows goes in position 1.
2. Range columns last -- columns used with <, >, or BETWEEN should be at the end.
3. Leading prefix rule -- the planner can use this index for queries filtering on customer_id alone, but NOT for queries filtering only on order_date. The first column must appear in the query's WHERE clause.

Getting the column order wrong doesn't cause an error -- the planner just quietly ignores your index and falls back to a sequential scan, which is worse than not having the index at all (because you're paying the write overhead for nothing).

The Compounding Effect

A missing index doesn't just slow down one query. As the table grows, the sequential scan reads more data, which evicts useful pages from the buffer cache, which slows down other queries that were previously running fine on cached data. A single missing index on a high-traffic table can degrade performance across the entire database.

This is why a query that "worked fine for months" suddenly gets slow -- the table crossed a size threshold where the sequential scan's impact on the buffer cache became significant.

Preventing Missing Indexes

The most impactful habit: every new WHERE clause deserves a moment of thought about whether an index exists to serve it. Beyond that:

• Run EXPLAIN against production-sized data before merging PRs that add new queries. If your staging environment has realistic volumes, add a CI check that flags Seq Scan on tables above a certain size.
• Monitor sequential scan ratios continuously, not just at deploy time. A table shifting from mostly index scans to mostly sequential scans means a new query path was deployed without a supporting index.
• Keep statistics current by ensuring autovacuum runs frequently. Stale statistics in pg_statistic cause the planner to underestimate row counts and choose sequential scans even when a good index exists.
• Check for unused indexes too -- they cost write performance and memory. An index that's never scanned is dead weight. Clean them up regularly.

The gap between "works in staging" and "melts in production" is almost always data volume. An index that seems unnecessary on 1,000 rows is essential on 10 million.

---

Originally published at mydba.dev/blog/missing-indexes-seq-scan