Read the Query Plan Before You Add Another Index

Running EXPLAIN ANALYZE against a slow query shows you what the planner actually chose: a sequential scan across a large table, a nested loop that's running far more times than expected, a sort spilling to disk because it didn't fit in memory. Each of those points at a different fix, and only one of them is usually solved by adding an index.

A sequential scan on a large table, filtered on a column with no index, is the classic case an index fixes cleanly. A nested loop running more iterations than the row estimates suggested is often a sign the planner's statistics are stale, which no new index will fix on its own, running ANALYZE on the table first will.

An index makes the query it targets faster to read and makes every INSERT, UPDATE, or DELETE that touches the indexed columns slightly slower, since the database has to maintain that index alongside the table itself. One index is a small cost. Ten indexes on a heavily written table is a real one, and it's easy to arrive there one well-intentioned addition at a time.

Before adding an index to fix a slow read, check how write-heavy the table already is. On a table that's read far more than it's written, the tradeoff usually favors adding the index. On a table under heavy write load, it's worth confirming the read the index would help is actually worth that ongoing write cost.

A composite index on two or more columns only helps a query efficiently when that query filters on a leftmost prefix of the index's column order. An index on (customer_id, created_at) speeds up a query filtering on customer_id alone, and one filtering on both columns together, but does nothing useful for a query filtering on created_at by itself.

Getting the column order wrong is one of the most common indexing mistakes, because the index still exists, still gets maintained on every write, and still doesn't help the query it was meant for. Match the column order to how the query actually filters, most selective and most commonly used column first, and check the plan afterward to confirm the index is actually being used the way you intended.

No index fixes a query that's structurally doing too much work. An N+1 pattern, one query per row from an earlier result set, stays slow no matter how well-indexed each individual query is, because the fix is combining them into one, not speeding up the many. A correlated subquery re-executed for every outer row has the same problem: the shape of the query is the bottleneck, not a missing index.

If a query plan shows reasonable index usage on every step and it's still slow, look at how many times each step is actually running before reaching for another index. Sometimes the honest answer is that the query is asking for more work than it needs to.

Indexes accumulate and rarely get removed, since nobody wants to be the one who drops an index and breaks a query they didn't know depended on it. Postgres tracks index usage statistics, and checking them periodically, rather than never, is how you find the ones that are costing writes without paying for themselves on any read.

An automated index advisor can surface candidates worth reviewing on both sides of this, indexes that would help a slow query and indexes that appear unused, but treat its suggestions as a starting point for review, not something to apply directly to a production table. It can't see every query pattern your application runs, only the ones it was told to analyze.

Work through a slow query in this order:

1. Run EXPLAIN ANALYZE and read what the planner actually chose, such as a sequential scan, an over-repeated nested loop, or a sort spilling to disk.
2. If row estimates look wrong, run ANALYZE on the table first, because stale statistics cause bad plans that no new index will fix.
3. Add an index only when the plan shows a scan an index would change, and check the column order of any composite index against the query's filters.
4. Rewrite the query instead when its shape is the problem, as with an N+1 pattern or a correlated subquery.
5. Check index usage statistics periodically and drop indexes that cost writes without serving any reads.