The Slow Query Workflow
Capture the statement, read the plan against reality, find where the estimate diverged, then decide which layer the fix belongs to — index, query, schema or application. Adding an index before reading the plan is guessing with extra steps.
Frame the diagnosis
Performance work starts from a symptom and a signal — never from a resource dashboard.
Capture the statement, not the endpoint
The first mistake is investigating "the slow endpoint". An endpoint issues several statements, and the one that regressed is often not the one anybody expected. Pull the normalized statement text out of the engine's statement view, ordered by total time rather than mean time, because a 4 ms query called 30,000 times per minute costs more than a 900 ms report nobody runs.
Total time is the right sort order for a second reason: it prioritizes by the resource actually consumed, which is what capacity work needs. Mean time surfaces the dramatic outlier; total time surfaces the query that is quietly eating 40% of the database. Both are worth looking at, and only one of them will still matter next quarter.
Capture the real parameter values too, or at least representative ones. A plan for status = 'archived' (2% of rows) and a plan for status = 'active' (71% of rows) can legitimately differ, and reproducing with the wrong value produces a fast plan and a confused engineer. This is the selectivity story that An Index Scan Is Not Automatically Faster is about.
SELECT substring(query, 1, 60) AS statement,
calls, round(mean_exec_time::numeric, 1) AS mean_ms,
round(total_exec_time::numeric / 1000) AS total_s
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 5;
statement | calls | mean_ms | total_s
------------------------------------------+--------+---------+---------
SELECT * FROM orders WHERE customer_id=$1|1284000 | 4.6 | 5906 ← the real cost
SELECT ... FROM order_items JOIN products| 412000 | 9.1 | 3749
UPDATE inventory SET reserved = ... | 890000 | 2.2 | 1958
SELECT ... FROM reports WHERE created_at | 1200 | 912.0 | 1094 ← the dramatic one
INSERT INTO audit_log ... |1900000 | 0.4 | 760
The 912 ms report is what gets reported to you.
The 4.6 ms query is what is actually consuming the database.Read the plan for the divergence, not the total
A plan with actual counts answers one question better than any other tool: *where did the planner's model of the data stop matching the data?* Scan the plan for the first node where rows= (estimated) and actual rows= diverge by an order of magnitude. Everything above that node inherited a bad decision, and optimizing anything above it is wasted work.
The plan below shows the classic shape. The planner expected 12 matching orders and chose a nested loop, which is an excellent choice for 12 rows. There were 41,000. The nested loop then executed the inner index scan 41,000 times, and a plan that should have taken milliseconds took 3.2 seconds. The fix is not "add an index" — the index exists and is being used correctly. The fix is to correct the estimate, which is a statistics problem, or to make the join strategy robust to being wrong, which is a query-shape problem (When the Join Strategy Is the Bottleneck).
Also read the plan for two shapes that indicate a resource limit rather than a logic problem: Sort Method: external merge Disk: 84MB means the sort exceeded working memory and spilled to storage, and Buffers: shared read= counts pages that were not in cache and had to come from disk. Both convert a CPU-bound plan into an I/O-bound one, and both are visible only if you asked for buffers.
Nested Loop (cost=0.86..312.44 rows=12 width=84)
(actual time=0.042..3187.221 rows=41038 loops=1)
Buffers: shared hit=164982 read=8871
-> Index Scan using orders_created_idx on orders o
(cost=0.43..98.12 rows=12 width=40)
(actual time=0.021..48.113 rows=41038 loops=1) ← 12 estimated, 41038 actual
Index Cond: (created_at > now() - interval '7 days')
Filter: (status = 'pending')
Rows Removed by Filter: 903
-> Index Scan using customers_pkey on customers c
(cost=0.43..17.85 rows=1 width=52)
(actual time=0.061..0.071 rows=1 loops=41038) ← executed 41038 times
Index Cond: (id = o.customer_id)
Planning Time: 0.284 ms
Execution Time: 3201.918 ms
The index is correct and used. The estimate is wrong by 3400x,
so the planner picked a strategy that is only good when it is right.Decide which layer the fix belongs to
Once the divergence is located, the remaining question is which layer to change — and this is where most of the value is, because the layers have wildly different costs and half-lives. An index is cheap to add and carries a permanent write cost. A query rewrite is free at runtime and costs review time. A schema change is expensive and usually the most durable. An application change (fetch less, cache it, do it asynchronously) sometimes removes the query entirely, which beats every database-level optimization.
The matrix below maps the evidence to the layer. The rule underneath it: prefer the change that makes the *estimate* right over the change that makes the *symptom* smaller. Bumping statistics targets so the planner sees reality fixes every query over that column; adding an index to force a different plan fixes this one query until the data shifts again.
Finish with the step teams skip. Re-run the plan, confirm the divergence closed, then measure the endpoint's p99 in production over a window comparable to the baseline. A query that got 8× faster in isolation and moved endpoint p99 by 3% means the query was never the constraint — and that is a genuinely useful result, because it sends you back to Which Signal Actually Means "The Database Is Slow" with one hypothesis eliminated.
| What the plan shows | Likely layer | Change | What it costs |
|---|---|---|---|
| Sequential scan, high rows-removed-by-filter, selective predicate | Index | Index the filtered column, composite if the query also sorts | Write amplification on every insert/update; storage |
| Correct index used, estimate wrong by orders of magnitude | Statistics | Raise the statistics target or add extended statistics for correlated columns | Slower analyze; helps every query over those columns |
Sort Method: external merge Disk: … | Query or memory | Reduce sorted rows (filter earlier, paginate by keyset), or raise working memory | Memory per connection multiplies across the pool |
Nested loop with huge loops= count | Query shape | Rewrite so the join is set-based; consider a lateral join or a pre-aggregated CTE | Review effort; risk of a different bad plan |
| Many small identical queries in the trace | Application | Batch them — this is The Comb: N+1 as a Visible Shape, not a database problem at all | Larger single queries; needs eager-loading discipline |
shared read= high, hit ratio dropping | Capacity or model | Working set exceeds memory: more RAM, or partition the hot data out | Cost, or a migration (Partitioning and Sharding) |
| Query is fast, endpoint is slow | Not the database | Return to triage — pool, locks, application, upstream | A meeting, but the right one |
Key points
- Sort candidate statements by total time, not mean time — the query eating the database is rarely the one users complain about.
- Read a plan by finding the first node where estimated and actual row counts diverge; everything above it inherited a bad decision.
- A correct index with a wrong estimate produces a catastrophic plan. The fix is the statistics, not another index.
external merge Disk:and highshared read=turn a CPU plan into an I/O plan, and only appear if you asked for buffers.- The fix belongs to a layer: index, statistics, query shape, schema or application — and removing the query beats optimizing it.
Progressive depth
Overview
A slow query is a claim, not a diagnosis. The workflow: capture the actual statement, get a plan with real row counts, find where the planner's expectation stopped matching reality, then decide which layer to change — and measure the endpoint afterwards, because a faster query that does not move p99 was never the constraint.
Practical
Sort by total time to find what is actually consuming the database. Run EXPLAIN (ANALYZE, BUFFERS) with realistic parameters. Read down to the first node where rows= and actual rows= diverge by 10× or more. Check for external merge Disk: (sort spilled) and high shared read= (cache misses). Then choose: statistics, index, query shape, schema, or remove the query.
Advanced
Estimate errors compound multiplicatively up a plan tree: a 30× underestimate at a scan becomes a catastrophic join strategy two nodes up. Correlated predicates are the usual cause, because most planners assume independence — WHERE city = 'Berlin' AND country = 'DE' is estimated as the product of two selectivities when the second is implied by the first. Extended statistics exist for exactly this. Parameter-sensitive plans are the other trap: one cached plan serving both a 2%-selective and a 71%-selective parameter value is wrong half the time.
Internals
The planner enumerates access paths and join orders, costs each with a model of page reads and CPU tuples, and picks the cheapest — see The Planner: Enumerating Ways to Answer and Cost-Based Optimization. Its cost units assume a page-read cost ratio that may not match your storage. An index scan on a B+ tree costs a root-to-leaf descent plus a heap fetch per row unless the index covers the query (B+ Tree Internals: Pages, Splits, Merges); a sequential scan reads pages in physical order and prefetches well (Sequential Scan, Page by Page). Which wins depends on selectivity, correlation between index order and physical order, and what is already resident in The Buffer Pool.
Query Plan Reader
Change an input and watch which number moves — and which one does not.
PostgreSQL-style output. Other engines format plans differently, but the questions you ask of them are the same.
Follow the diagnosis
The causal chain, hop by hop — and the readings that invite the wrong conclusion.
- 1Trace → statement: the
/ordersendpoint spends 3.2 s in one statement family; the rest of the span is negligible. - 2Statement view → candidate: the statement ranks second by total time and its mean has grown from 40 ms to 3.2 s over two weeks.
- 3Plan → divergence: the planner estimates 12 rows from the date predicate; reality is 41,038 rows after two weeks of accumulated pending orders.
- 4Divergence → strategy: with 12 expected rows a nested loop is optimal, so the planner chose it and then executed the inner scan 41,038 times.
- 5Strategy → root cause: stale statistics on a growing table, not a missing index — the index exists and is used correctly.
- • "The query is slow, add an index." The plan shows the index is already used; the problem is the row estimate feeding the join strategy.
- • "The mean is only 4.6 ms, that query is fine." At 1.28 million calls it is the largest single consumer of database time.
- • "It is fast on my machine." Development datasets produce different plans; a sequential scan over 500 rows is optimal and over 50 million rows is not.
- • "Execution time dropped 8×, we fixed it." If endpoint p99 barely moved, the query was not the constraint and the fix shipped write amplification for nothing.
- • "The plan looks fine." Without
ANALYZEthere are no actual counts, so the plan is the planner's opinion, not evidence.
Measure, fix, validate
An optimization is not finished until the metric that motivated it has moved.
- • Statement-level totals from the engine (`pg_stat_statements.total_exec_time`, MySQL slow log aggregation), sorted by total time over a stated window.
- • `EXPLAIN (ANALYZE, BUFFERS)` with realistic parameter values, captured against production-like data volumes — never against a 500-row development database.
- • Estimated versus actual rows at each plan node, as the primary reading.
- • Endpoint p99 before and after, over a comparable traffic window, to confirm the query was actually the constraint.
- • Correct the model first: refresh or raise statistics targets, add extended statistics for correlated predicates. It fixes every query over those columns, not just this one.
- • Then the index, if a selective predicate is genuinely unsupported — composite and ordered to serve the filter and the sort together.
- • Then the query shape: replace loop-driven access with set-based joins, use keyset pagination instead of deep offsets ([[scan-vs-index-performance]] covers when a scan is right).
- • Then the schema: partition, denormalize the hot read path, or precompute — durable and expensive, so it needs the evidence the earlier steps produced.
- • Consider deleting the query: batching, caching, or moving the work off the request path removes the cost rather than shrinking it.
- • Re-run `EXPLAIN (ANALYZE, BUFFERS)` and confirm the estimate/actual divergence closed and the strategy changed — not merely that the time dropped.
- • Compare endpoint p99 over a traffic window equal to the baseline window, at similar volume and time of day.
- • Check the write path did not regress if an index was added: insert and update latency on the same table, before and after.
- • Confirm total database time for that statement family fell in the statement view, which catches "faster per call, called more often".
- • Every index makes reads faster and writes slower, permanently. On a write-heavy table the cost outlives the incident that motivated it.
- • Raising working memory to stop sorts spilling multiplies across concurrent connections; a pool of 100 can exhaust the host.
- • Query rewrites for a better plan are usually less readable, and the next engineer may "simplify" them back.
- • Plan-shape assertions in CI catch regressions and also fail on harmless planner upgrades, which costs maintenance attention.
- • A CI check that runs
EXPLAINfor critical statements against a production-shaped dataset and fails on plan-node changes for the hot paths. - • An alert on rows-examined-to-returned ratio per statement family — plan flips show up here before users notice.
- • Scheduled statistics maintenance sized to the table's growth rate, with a monitor on statistics age for the largest tables.
Accuracy
Performance numbers are conditional. These are the conditions.
- DATABASE-SPECIFICPlan output, statement views and statistics controls shown here are PostgreSQL 16. MySQL, SQL Server and Oracle expose equivalent information with different names, defaults and failure modes.
- ILLUSTRATIVEThe plans and statement tables are constructed to show a recognizable shape. Row counts, costs and timings are invented, not captured.
- WORKLOAD-SPECIFICWhether a plan is good depends on data distribution and cache residency. The same query against the same schema can deserve different plans in two environments.
Misconceptions
EXPLAIN shows what the planner intends and what it estimates. Only ANALYZE executes it and reports actual rows and timing, which is where the evidence is.