Why Is This Query Slow? A Query Plan Reading Guide

Start from the bottom of the plan tree, that's where execution actually begins, and work up. Look for a sequential scan on a table with a large row count where you expected an index scan; that's the clearest signal something's missing. Compare the planner's estimated row count against the actual row count at each step; a large gap between estimate and actual usually means outdated table statistics, which is a different fix, running an analyze, not a missing index.

If a query's filter matches a large fraction of the table's rows, a sequential scan can genuinely be cheaper than an index scan, since jumping between the index and the table's actual rows for a large fraction of the table costs more than reading the table straight through. This is the most common reason engineers see a sequential scan and assume the index is broken, when the planner is actually making a reasonable cost-based choice for a low-selectivity filter. Check the filter's selectivity, roughly what fraction of rows match, before concluding the index needs fixing.

A standard B-tree index handles equality and range queries on ordered data well and covers most cases by default. A partial index, one that only covers rows matching a condition like `WHERE status = 'active'`, is worth it when queries consistently filter to a small, well-defined subset of a much larger table. A covering index, one that includes every column a query needs so the planner never has to touch the table itself, is worth the extra storage for a genuinely hot, narrow query path. Picking B-tree by default and reaching for the others only when a specific query pattern justifies it keeps index maintenance manageable.

Most databases expose index usage statistics, a count of how many times an index has actually been used to satisfy a query since the counter was last reset. An index with a genuinely zero count over a full, representative traffic cycle, not just a quiet weekend, is a real candidate for removal; one that's used rarely but for a critical, if infrequent, query, a monthly billing job, a rare admin action, should stay even though its usage count looks low. Review index usage quarterly, not reactively only when someone notices write performance has degraded.

Every index has to be updated on every write to the table it covers, so an unused index isn't free, it's a tax on every insert, update, and delete that provides no corresponding read benefit. On a high-write table, several unused indexes can measurably slow down write throughput, and it's a cost that's easy to overlook because it shows up as gradually degraded write latency rather than an obvious, attributable spike tied to a specific change.

Automated tools that suggest indexes based on observed query patterns are useful as a starting point, especially for surfacing candidates on a table nobody's looked closely at in a while, but they still need a human to confirm the suggestion against actual query plans and to weigh the write-amplification tradeoff for that specific table's traffic pattern. Treat an automated suggestion the same way you'd treat a junior engineer's first pass, a reasonable starting point that still needs review, not something to apply automatically to a production table without checking.

Run through these checks before adding or dropping an index:

• Read the EXPLAIN ANALYZE output from the bottom up, looking for sequential scans on large tables where you expected an index scan.
• Confirm the filter is selective enough that the planner would actually choose the index.
• Pick the index type that fits the query, such as a B-tree for equality and ranges or a partial index for a consistent filter.
• Check usage statistics over a full, representative traffic cycle before dropping an index that looks unused.
• Confirm any automated index suggestion against your actual query patterns, and watch for bloat on indexes that are used.

An index that's genuinely useful and actively used can still degrade over time through bloat, dead entries from updated or deleted rows accumulating faster than routine maintenance cleans them up, which slows down both the reads it's supposed to speed up and the writes that maintain it. This is a maintenance problem, not a design problem, and it needs its own periodic check, rebuilding or reindexing on a schedule, separate from the question of whether the index should exist in the first place.