Reading a Query Plan Before You Add Another Index

Run the actual slow query through EXPLAIN ANALYZE before touching the schema. The output tells you whether the planner is doing a sequential scan across the whole table, whether its row estimate is wildly off from what it actually returned, and where the time is genuinely going. An index only helps if the plan shows a scan pattern an index would actually change.

A wildly wrong row estimate is often a sign the table's statistics are stale, not that an index is missing. Refreshing statistics is a smaller fix than adding an index, and worth ruling out first.

Pay attention to the difference between the planner's estimated rows and the actual rows returned, shown side by side in the output. A close match means the planner had good information to work with; a wide gap means whatever conclusion it reached about the best plan is built on a bad guess, and fixing the estimate can matter more than any index you'd add on top of it.

Say a query filters on a status column and then sorts by created_at, and the plan shows a sequential scan followed by an explicit sort step. A composite index on status and created_at, in that column order, lets the planner satisfy both the filter and the sort from the index directly, and the plan afterward should show an index scan with no separate sort step.

If the sort step is still there after adding the index, the column order or the index type is wrong, not the idea. Check the plan again after the change instead of assuming the index did what you intended.

Every index the table has gets updated on every insert and update that touches its columns, so an index that speeds up one report query has a cost on every write to that table, whether or not that write ever touches the report.

A partial index, scoped to only the rows a query actually filters for, or a covering index that includes the columns a query selects so it never has to touch the table's main storage at all, often gets you the read speedup with a smaller write-side cost than a blanket index across the whole table.

Tools that watch real query patterns and surface candidate indexes are useful for finding what you'd otherwise miss, but they don't know your write volume or which of your existing indexes already overlap the one they're suggesting.

Treat their output as a shortlist to check against EXPLAIN ANALYZE and your existing index list, not as something to apply automatically in production. A candidate that overlaps an existing index by its leading column usually isn't worth adding.

These tools are strongest at surfacing queries you didn't know were slow in the first place, because they watch everything running against the database rather than only the queries someone happened to complain about. Run them periodically even when nothing is currently on fire.

An index on created_at alone doesn't help the query above nearly as much as the composite index does, because the planner still has to scan every matching status row before it can apply the sort benefit.

Column order in a composite index matters: put the columns used in equality filters first and the column used for sorting or range filtering last, matching the order the query actually needs, not the order the columns happen to appear in the table definition.

Check these before you create the index:

• Put equality-filter columns first in a composite index and the sort or range column last, matching how the query is written.
• Look for an existing index with the same leading column that already makes the new one redundant.
• Consider a partial index scoped to the rows the query filters for, since it costs less on every write.
• Consider a covering index that includes the selected columns, so the query avoids the extra table lookup.
• After creating it, confirm with EXPLAIN ANALYZE that the planner actually uses the new index.

An index on a column that gets updated frequently accumulates dead entries as rows change, the same way a table does, and its size can grow well past what the data it covers would suggest. A bloated index still works, but it's slower to scan and slower to maintain than a freshly built one covering the same columns.

Check index size against row count periodically for your highest-churn tables, and rebuild an index that's grown disproportionately rather than assuming it's still the same efficient structure you created.