Query Plans
Reading execution plans across engines, and the estimate errors that cause most bad ones.
A plan tells you what the engine intends to do. An analysed plan tells you what it actually did, and the gap between the two is where the answer usually is.
Getting a plan
What to look at, in order
1. Estimated versus actual rows. This is the first and most valuable check. An estimate of 200 against an actual of 2,000,000 explains almost every bad plan: the engine chose a nested loop because it expected a handful of rows.
2. The access method. A sequential or collection scan on a large table with a selective predicate means no usable index. A scan on a small table is fine and often optimal.
3. Rows discarded after reading. Rows Removed by Filter, rows_examined far above
rows_sent, docsExamined far above nreturned — all the same signal.
4. Sorts and spills. An explicit sort that an index could have provided, or a hash that spilled to disk, is work you can remove.
5. Buffers and I/O. In PostgreSQL, shared read versus shared hit distinguishes a plan
problem from a memory problem — the same plan on a cold cache looks entirely different.
6. Loops. A node showing loops=5000 costs five thousand times its per-loop time. This is how
an innocuous-looking nested loop becomes the whole query.
Why estimates go wrong
- Stale statistics after a bulk load. Run
ANALYZE/ANALYZE TABLE. - Correlated columns. Engines assume independence, so
city = 'Paris' AND country = 'France'is estimated as the product of two selectivities and comes out far too low. PostgreSQL's extended statistics fix this case specifically. - Skewed distributions. Increase the histogram resolution for that column.
- Expressions the planner cannot see through.
WHERE date_trunc('day', ts) = …has no statistics; an expression index gives it some. - Parameter sniffing. A plan cached for one parameter value can be wrong for another.
Plan stability
A query that was fast yesterday and slow today usually did not change — its plan did, because the data crossed a threshold or statistics were refreshed.
-- PostgreSQL: has this statement's average time shifted?
SELECT calls, mean_exec_time, stddev_exec_time, left(query, 60)
FROM pg_stat_statements ORDER BY stddev_exec_time DESC LIMIT 10;A high standard deviation relative to the mean is the signature of a plan that varies by parameter.