Reading a Query Plan Before You Add an Index

A query plan shows whether the database is doing a sequential scan across the whole table or an index scan targeting specific rows, the planner's estimated row count versus what it actually found, and where the actual time is being spent. A large gap between estimated and actual row counts usually points to stale table statistics, a separate problem an index alone won't fix.

A composite index's column order matters: put the most selective equality filter first, then range filters, then any columns used purely for sorting. The leftmost-prefix rule governs whether the planner can even use it at all: an index on two columns in a specific order doesn't help a query that only filters on the second column alone.

Every index adds write overhead, since it has to be updated on every insert, update, and delete that touches the indexed columns, along with its own storage cost. An index that your database's own usage counters mark as never touched by the planner is a strong candidate to drop, not to keep just in case someday it might be.

• Run EXPLAIN ANALYZE on the actual slow query against representative data volume, not a nearly-empty development database where every query looks fast.
• Build the index concurrently on a production table where that option exists, so the build itself doesn't lock out writes.
• Re-run EXPLAIN ANALYZE after adding the index to confirm the planner is actually choosing to use it, since it sometimes decides not to.

A managed database's automated index recommendation feature is a reasonable source of candidates to investigate, since it can surface a pattern a human hasn't noticed yet. It doesn't know your actual traffic growth trajectory or which queries matter most to your business the way a human reviewing the real query plan does, so treat its suggestions as a hypothesis worth verifying with EXPLAIN ANALYZE, not an instruction to apply automatically.

A plan that made a sensible choice at a hundred thousand rows can make a completely different, worse choice at ten million, because the planner's cost model shifts with table size and the distribution of values in each column. An index that looked unnecessary early on can become genuinely necessary later, and one that helped early can stop mattering once a different access pattern dominates.

Re-run EXPLAIN ANALYZE on your highest-traffic queries periodically as tables grow, rather than treating an indexing decision made at launch as permanent. What was the right call for the data you had a year ago isn't automatically still the right call for the data you have now.

Tie this review to a natural trigger rather than an arbitrary calendar reminder: a table crossing an order of magnitude in row count, or a query that used to be fast showing up in a slow query log for the first time, are both better signals that it's time to look again than a fixed schedule that may or may not line up with when anything actually changed.

Keep a short note next to each nonobvious index explaining which query it was added for. Six months later, nobody remembers the reasoning behind an index with a generic name, and that missing context is exactly why teams end up afraid to drop anything, even things the usage counters clearly mark as dead weight.