A page that loaded instantly in development takes eight seconds in production, and the difference is not the code — it is that production has a million rows and development has fifty. The usual advice is "add an index", which is often right and is not a diagnosis. Adding indexes without reading what the database is actually doing is how a table ends up with fifteen of them and writes that crawl.
The underlying idea is simple enough to hold in your head. An index is a sorted copy of one or more columns, with pointers back to the rows. Sorted data can be searched by halving the range repeatedly instead of reading everything, which is the entire difference between a query that scales and one that does not.
What follows is what a plan is telling you, the specific reasons an index you added is being ignored, and the cost that makes "index everything" a bad default.
Scan versus seek
Every database can show you its plan for a query — the steps it intends to take. You do not need to understand all of it. You need to find, for each table, whether it is scanning or seeking. A scan reads every row and checks each one; the time it takes grows with the size of the table. A seek jumps into a sorted structure and reads only the matching part; the time grows with the size of the result, which is usually tiny by comparison.
This is why a query can be instant on fifty rows and unusable on a million while doing exactly the same work per row. The plan did not change. The number of rows it applies to did, and a full scan is the one shape whose cost is proportional to the whole table.
A scan is not automatically wrong. If a query genuinely needs most of the rows, scanning is the cheaper plan and the database is right to choose it — jumping through an index for eighty per cent of a table costs more than reading it straight through. What you are looking for is a scan that returns a handful of rows out of very many.
Why the index you added is being ignored
The most common reason is that the query wraps the column in a function or a calculation. Comparing the lowercased form of a column, or the date part of a timestamp, means the stored sorted values no longer match what is being compared, so the index cannot be used. The fix is either to move the transformation to the other side of the comparison, or to build the index on the expression itself where your database supports that.
The second is column order in a composite index. An index on two columns is sorted by the first, then by the second within it — like a phone book by surname then first name. It helps a query filtering on the first column, or on both, and does not help one filtering only on the second, exactly as a phone book cannot find everyone called Ahmad. Ordering a composite index is a decision about which queries it is for.
The third is type mismatch: comparing a text column against a number, or two columns with different collations, can force a conversion that disables the index. This one is easy to miss because the query returns correct results — only slowly.
What every index costs
An index is a second copy of the data that has to be kept sorted. Every insert, update and delete on the table must also update every index that covers the affected columns. So indexes are paid for on writes, forever, in exchange for faster reads. On a table that is written far more than it is read, an index can genuinely make the system slower overall.
They also cost space, which matters more than it sounds when your database has a size ceiling. And they cost planning time: the more indexes exist, the more options the query planner has to consider, and occasionally the more chances it has to choose a worse one on a query you were not thinking about.
This is why the useful posture is one index at a time, driven by a specific slow query, and verified by re-reading the plan afterwards to confirm the scan became a seek. An index added on suspicion and never verified is a permanent write cost bought for an unknown read benefit.
The problems no index will solve
Some slow queries are slow for reasons indexing cannot touch. Fetching every row to count them in application code is slow no matter how the rows are found; the database should do the counting. Running one query per item in a list — the classic N+1 — makes hundreds of fast queries that add up to one slow page, and the fix is to fetch the set in one query rather than to index harder.
Returning far more columns or rows than the page displays is another. Time spent serialising and transferring data the user never sees is invisible in the plan and very visible in the page load, and it is fixed by asking for less.
The order that saves the most time is: find the slow query, read its plan, and only then decide whether the answer is an index, a rewrite, or asking for less data. Roughly half the time it is one of the latter two, which is precisely why reaching for an index first is a habit worth breaking.
Reading an EXPLAIN plan: what to actually look for first
Running `EXPLAIN` (or `EXPLAIN ANALYZE` for real, measured timing rather than an estimate) in front of a slow query reveals exactly which strategy the database's query planner actually chose — a full table scan reading every single row, or an index lookup reading only the rows that actually matter — and the very first thing worth checking in that output is whether a full table scan appears anywhere the query's own filter conditions should have allowed an index to be used instead, since that specific gap is the most common, most fixable cause of an unexpectedly slow query.
Why wrapping an indexed column in a function silently defeats the index
A query filtering with `WHERE LOWER(email) = 'user@example.com'` cannot use a plain index on the `email` column at all, because the index stores the column's raw, unmodified values while the query is actually filtering on a computed, function-wrapped version of it that the index has no way to match against directly — this is one of the most common, easy-to-miss ways a seemingly reasonable query silently defeats an index that should otherwise have made it fast, and the fix is either rewriting the query to avoid wrapping the column, or creating a dedicated expression index built specifically on that exact computed value.
Why an index becomes counterproductive when the query returns most of the table anyway
A query expected to return the majority of a table's rows is frequently faster served by a full table scan than by an index lookup, since following an index pointer back to each individual row scattered across disk is more expensive, row by row, than simply reading the table sequentially from start to finish — this is precisely why a query planner sometimes deliberately ignores an available, seemingly relevant index, and that choice is often the query planner correctly optimizing rather than a bug or misconfiguration worth fighting against.
Why stale statistics can mislead a query planner into a genuinely bad decision
A query planner's decision about whether to use an index relies on statistics about the actual distribution of data in a table, and those statistics can go stale after a large volume of inserts, updates, or deletes if the database has not recently recalculated them — a query that suddenly becomes slow with no code or schema change at all is worth checking against this specific possibility, since manually triggering a statistics update can sometimes resolve a performance regression that otherwise looks mysterious and unexplained.
Why comparing two query plans side by side is more instructive than reading one in isolation
Running `EXPLAIN` against both a slow version of a query and a rewritten, faster version and comparing the two plans directly reveals exactly which specific change in the query actually caused the planner to choose a different, better strategy — this comparative approach builds considerably more useful, transferable intuition than reading a single plan in isolation, since it directly ties a specific query change to a specific, observable difference in the resulting execution strategy.
Why index usage should be periodically re-verified, not assumed to stay correct forever
A query that correctly used an index when the schema was first designed can silently stop doing so after an unrelated schema change, a shift in the actual distribution of the underlying data, or a database version upgrade that changed the query planner's own internal decision logic — periodically re-running `EXPLAIN` against a system's known-important, frequently-run queries catches this kind of silent regression before it manifests as a mysterious, unexplained slowdown users notice before the team does.
Why this article's practical lesson is to verify with EXPLAIN rather than guess from intuition alone
Every specific mechanism covered throughout this article — function-wrapped columns, low selectivity, stale statistics — can be reasoned about in the abstract, but the actual, reliable way to know which one, if any, applies to a specific slow query in front of a developer right now is running `EXPLAIN` and reading the real output, rather than guessing from general principles alone; this article's mechanisms explain what to look for once the plan is in front of someone, they do not substitute for actually looking.
Why pairing this article with the broader indexing mental model covered elsewhere in this library completes the picture
This article has focused specifically on diagnosing why an existing index is not being used for a specific slow query, while this library's companion article on the underlying indexing mental model covers the structural reasoning behind why indexes work at all in the first place — reading both together gives a considerably more complete picture than either one provides in isolation, since diagnosis and underlying mechanism are two genuinely different, complementary halves of the same overall subject.
Why this article's final point is that reading a query plan is a learnable, improvable skill
Reading an `EXPLAIN` plan fluently is not an innate talent some developers simply have and others lack; it is a skill that improves directly and predictably with repeated, deliberate practice against real, varied slow queries, and a developer who has worked through even a modest number of real query plans develops a working, pattern-matching fluency that lets them diagnose the next unfamiliar slow query considerably faster than someone encountering a query plan for the very first time.