Reading a Query Plan Well Enough to Know Which Index You Actually Need

EXPLAIN shows you the planner's estimated plan; EXPLAIN ANALYZE actually runs the query and shows you what really happened, including real row counts and real timing per step. The gap between the planner's estimate and reality is itself diagnostic: a large gap often means your table statistics are stale, which can cause the planner to pick a bad plan even when a good index already exists. Always read the analyzed version before deciding a query needs a new index at all.

A sequential scan, reading every row in a table, isn't automatically bad; on a small table it's often faster than the overhead of using an index at all. The sequential scan worth fixing is the one on a large table, filtering down to a small fraction of rows, taking a meaningful share of the query's total time. Look at the actual row counts in the plan: a sequential scan reading a million rows to return twenty is a strong signal an index on the filtered column would help. A sequential scan reading a million rows to return eight hundred thousand often isn't, since an index wouldn't meaningfully narrow that down anyway.

An index on a single column helps a query that filters on exactly that column, but many real queries filter on a combination of columns, or filter on one column while sorting by another. A composite index, covering the columns in the order your query actually uses them, often outperforms two separate single-column indexes for a query that needs both. Get the column order in a composite index wrong, and the index may not get used at all for a filter that doesn't lead with its first column.

Every index speeds up the specific reads it's built for and slows down every write to that table, since the database has to update the index alongside the row itself. A table with heavy write traffic and a dozen indexes, several unused, pays that cost on every insert and update without getting anything back for the unused ones. Periodically check which indexes are actually being used by your query planner, most databases expose this, and drop the ones that aren't earning their write overhead.

Say a report page that filters orders by customer and sorts by date takes four seconds to load. EXPLAIN ANALYZE shows a sequential scan reading two million order rows to return the forty that belong to one customer, with almost the entire four seconds spent in that scan. A composite index on customer ID and order date, in that order, turns that same query into an index scan reading roughly forty rows directly, and the report page drops to double-digit milliseconds. The fix wasn't guessed, it came directly from reading which step in the plan actually consumed the time.

Several databases and third-party tools can suggest indexes automatically based on observed query patterns, and these suggestions are a reasonable starting point, especially on a schema too large to review by hand. Treat the suggestion as a hypothesis, not a final answer: apply it in a staging environment, run EXPLAIN ANALYZE on the actual queries it's meant to help, and confirm the planner is choosing to use it before rolling it out to production. An automated suggestion applied blindly carries the same write-overhead risk as a manually guessed index, since the tool doesn't know your actual write volume or how many other indexes are already competing for the same benefit.

A short checklist before you add any index:

• Run EXPLAIN ANALYZE rather than plain EXPLAIN, and compare the planner's estimates against the real row counts and timing.
• Fix only sequential scans on large tables that filter down to a small fraction of rows and take a meaningful share of query time.
• Build composite indexes with columns in the order the query actually filters and sorts, instead of stacking single-column indexes.
• Periodically drop indexes that are never used, since each one slows every write to the table.
• Test any advisory tool suggestion in staging with EXPLAIN ANALYZE before applying it to production.