Guessing Is Slower Than Reading
A query is slow. The usual response is a sequence of guesses: add an index on the column in the WHERE clause, rewrite the join, try a subquery instead, maybe increase the instance size. Sometimes one of these works, and because nobody knows why, the same guessing happens again next month.
The plan tells you what the database is actually doing. Reading it takes a few minutes and replaces the entire guessing loop with a diagnosis. The output looks intimidating, but you only need to extract three things from it, and everything else is detail you can ignore until those three are addressed.
Use the Form That Includes Real Measurements
Every engine offers two modes, and the distinction matters more than anything else in this article.
Plain EXPLAIN shows the plan the optimiser intends to use, with estimated costs and row counts. It does not run the query. This is useful for checking access paths quickly, and useless for diagnosing why something is slow, because the estimates may be wrong and their wrongness is usually the actual problem.
The analysing form actually executes the query and reports real timings and real row counts alongside the estimates. In PostgreSQL that is EXPLAIN (ANALYZE, BUFFERS). In MySQL, EXPLAIN ANALYZE. In SQL Server, enabling the actual execution plan.
Always use the analysing form for diagnosis. One caveat: it runs the query, so be careful with statements that modify data — wrap them in a transaction you roll back.
The Three Things to Look For
Plans are trees. Read them from the innermost or most indented node outward, because that is execution order: leaves produce rows, parents consume them. Then look for these three things, in this order.
1. Where Is the Time Actually Spent?
Find the node with the largest share of total time. This sounds obvious but people routinely optimise the wrong node because it looked suspicious.
Beware two traps. First, in most formats a node's reported time is cumulative, including its children, so a top node always looks expensive. What you want is the node whose own contribution is large. Second, some nodes execute many times — once per row of a parent loop — and the displayed time may be per execution. A node taking 0.3 ms that runs 40,000 times is your problem, and it does not look like it at a glance.
2. Where Are the Estimates Wrong?
This is the highest-value signal in the entire plan and the one most often skipped.
Compare estimated rows against actual rows at each node. A discrepancy of an order of magnitude or more means the optimiser was working from bad information, and every decision it made above that node was based on a false premise. It chose a nested loop because it expected 5 rows; 50,000 arrived; the loop now runs 50,000 times.
When you find a large misestimate, fix that rather than the plan shape. Forcing a different join method treats the symptom. Common causes are stale statistics, correlated predicates the optimiser assumes are independent, a function wrapping a column so no statistics apply, and parameter values unrepresentative of the cached plan.
3. What Access Method Was Chosen, and Was It Reasonable?
Only now look at scans and joins. The question is never "is a sequential scan bad" — it is "is this the right choice given how many rows are involved".
Access Methods in Context
| Operation | Appropriate When | A Problem When |
|---|---|---|
| Sequential / full scan | Reading a large fraction of a table, or the table is small. | A selective filter should have found a few rows in a large table. |
| Index scan / seek | Retrieving a small fraction of rows. | Fetching most of the table one row at a time; a scan would be cheaper. |
| Index-only / covering | Always good. All needed columns came from the index. | Not a problem. This is the target state for hot queries. |
| Nested loop join | Outer side is genuinely small and inner side is indexed. | Outer side turned out large. This is the classic misestimate symptom. |
| Hash join | Joining two large unsorted inputs on equality. | The hash table spills to disk because the work memory is too small. |
| Sort | Small result, or no usable ordered index exists. | Large external sort spilling to disk, or avoidable via an index. |
Two of these are worth calling out because they are the most common real findings. A nested loop with a large outer side is almost always a row-estimate failure, not a bad join choice in itself. A spilling sort or hash is a memory configuration issue and often fixable without touching the query at all.
The Frequent Causes, and What to Do
A Function Around an Indexed Column
Applying a function to a column in a predicate prevents the index on that column from being used, because the index stores the raw values. The fix is to rewrite the predicate so the column appears bare — a date range instead of extracting the year, for instance — or to create an index on the expression itself.
Implicit type conversion causes the same problem invisibly. Comparing a text column against a numeric literal may force a conversion on the column side, quietly disabling the index. The plan shows a scan and the query looks like it should have used an index, which is a confusing combination until you spot the type mismatch.
Leading Column Missing From a Composite Index
A composite index can only be used from its leftmost column onward. An index on three columns does not help a query filtering only on the second. Check the index definition against the actual predicate order.
Stale Statistics
After a bulk load or a large delete, statistics may describe a table that no longer exists. The optimiser then makes confident decisions from stale distributions. Refresh statistics before concluding anything else, since it is cheap and it invalidates a lot of otherwise-plausible theories.
Over-Fetching
Selecting every column prevents index-only access and inflates the data transferred. This is not glamorous, but restricting the select list is frequently the single cheapest improvement available, particularly on tables with wide text columns.
An Unintended Cross Product
A missing join condition produces a Cartesian product. The signature is unmistakable once you know it: an actual row count that is roughly the product of two inputs. A formatted query makes a missing ON clause obvious, whereas a query written as one long line hides it easily.
A Workable Method
- Format the query first. Before analysing anything, make it readable. Plan nodes map to clauses, and matching them up in an unformatted query is needlessly hard. The Develop Box Utilities SQL formatter does this locally, which matters when the query embeds real identifiers or literal values you would rather not paste into a hosted tool.
- Capture the analysing plan with buffer information. Estimates alone will mislead you.
- Find the dominant node by self time, accounting for loop counts.
- Check estimate versus actual at that node and below it. Fix misestimates before touching plan shape.
- Evaluate the access method against the real row counts.
- Make one change, then re-measure. Changing several things at once means you learn nothing about which one mattered.
- Record the before and after plan. Six months later, when it regresses, the old plan is the fastest way to see what changed.
Frequently Asked Questions
Is a sequential scan always something to fix?
No, and treating it that way causes real harm. When a query touches a large share of a table, a sequential scan is genuinely cheaper than random index lookups, and the optimiser is right to choose it. A scan is only a problem when a selective predicate should have narrowed the result to a small number of rows and did not.
Why does the same query use different plans at different times?
Because plans depend on inputs that change: table statistics, data volume, parameter values, available memory, and cache state. Parameter-sensitive plans are a particularly common cause — a plan cached for a rare value performs badly for a common one. This is why capturing the plan at the time of the slowdown matters more than reproducing it later.
Should I use query hints to force a better plan?
As a last resort. A hint freezes a decision that was correct for today's data distribution, and it will still be frozen when the data changes. Since bad plans usually come from bad row estimates, fixing the estimate addresses the cause and keeps the optimiser adaptive. Reach for hints only after you have understood why the estimate is wrong and cannot repair it.
Conclusion: Read Before Changing
The most valuable habit in query tuning is looking at the plan before making a change. It converts an open-ended search into a directed one, and it usually points somewhere different from where intuition pointed.
Get the analysing plan, find where time is really going, check estimates against actuals, and only then evaluate the access method. Most slow queries have one dominant cause, and that sequence finds it faster than any amount of speculative index creation.