Databases
Query execution plans: interview questions and how to answer them
An execution plan is the tree of operations the database chose; reading it well means comparing what it expected against what actually happened.
Written and reviewed by Sahil Srivastav
What it actually is
A plan is a tree. Each node consumes rows from its children and produces rows for its parent, and execution flows from the leaves upward. Reading it inside-out rather than top-down is the first adjustment people have to make — the first line is the last operation, not the first.
EXPLAIN alone shows the plan and the estimates. EXPLAIN ANALYZE actually runs the query and adds what happened: real row counts, real timings, real loop counts. The gap between estimate and actual is the single most diagnostic signal available, because the planner only makes bad choices when its estimates are wrong.
The number that misleads most often is rows on a node executed in a loop. It is the average per iteration, not the total. A node showing rows=1 loops=40000 touched forty thousand rows, and this is exactly how an N+1-shaped plan hides from someone skimming for a big number.
Why it matters in production
Because every other database performance decision depends on reading this correctly. Whether to add an index, whether the index you added is working, whether the problem is the query or the data volume, whether the planner has stale statistics — all of it is answered by the plan, and none of it is answered by timing the query.
It is also the fastest way to separate "slow because it is doing too much work" from "slow because it is waiting". The plan tells you about work. If the plan looks cheap and the query is still slow, you are blocked on a lock or starved of I/O, and no amount of query tuning will help. Candidates who check this distinction first are visibly more experienced than those who start adding indexes.
How it works
Estimated versus actual rows is the primary signal
The planner chooses join strategies and access paths from its row estimates. When the estimate says 10 and the actual is 400,000, it has probably chosen a nested loop that is now catastrophic. An order-of-magnitude gap means the statistics are stale, the column has skew the default statistics target cannot capture, or the predicate involves correlated columns the planner assumes are independent.
Loops multiply everything beneath them
Under a nested loop, the inner node runs once per outer row. Its reported rows and actual time are per-iteration averages, so total cost is roughly rows × loops. A node reporting 0.05ms with 40,000 loops consumed two seconds. Missing this is the most common misreading of a plan.
Join strategies and when each is right
A nested loop is ideal when the outer side is small and the inner side is indexed — and disastrous when the outer side turns out to be large. A hash join builds a hash table from one side and is strong for large unsorted joins, but needs memory and spills to disk when work_mem is insufficient. A merge join needs both sides sorted and is efficient when they already are. A plan that chose the wrong one almost always traces back to a row estimate.
Rows Removed by Filter tells you what was wasted
This counts rows the node read and then discarded. A large value under an index scan means the index located a broad range and the real narrowing happened afterwards — usually a composite index with the wrong column order, or a predicate the index cannot serve. The plan still says "index scan", which is why this line matters more than the node name.
BUFFERS distinguishes CPU from I/O
EXPLAIN (ANALYZE, BUFFERS) reports pages hit in cache versus read from disk. High shared read means the working set does not fit in cache and the fix may be memory or a narrower index rather than a different plan. High shared hit with high time means the cost is CPU — often a large sort, a complex expression, or sheer row volume.
Implementing it
Always use real production parameter values. A plan for a cheap parameter tells you nothing about the expensive one, and parameterised queries can have a cached generic plan that differs from what literals produce.
Read inside-out and find the node where actual time jumps. The expensive node is rarely the top one, and the top one is usually just reporting the sum of its children.
Check estimate versus actual at each level before concluding anything. If estimates are badly wrong, fix statistics first — ANALYZE, a higher statistics target on a skewed column, or extended statistics for correlated columns — because every plan choice downstream rests on them.
When the plan looks reasonable but the query is slow, stop tuning and check pg_stat_activity.wait_event_type. A lock wait produces a cheap-looking plan and a long wall-clock time.
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;
-- The shape that hides an N+1: per-iteration rows look trivial
-- -> Index Scan on order_items (actual time=0.004..0.012 rows=3 loops=40000)
-- 40,000 iterations x 3 rows = 120,000 rows and ~0.5s, not 0.012ms
-- The shape that means a bad estimate drove a bad join
-- -> Nested Loop (cost rows=12) (actual rows=418233 loops=1)
-- Estimate off by 4 orders of magnitude; the planner would have
-- chosen a hash join had it known.
-- Fix statistics before touching the query
ANALYZE orders;
ALTER TABLE orders ALTER COLUMN tenant_id SET STATISTICS 1000;Interview questions and how to answer them
Estimated rows is 12, actual is 418,000. What does that tell you?
That the planner made every downstream decision on bad information — most likely choosing a nested loop that is now four hundred thousand iterations. The query is probably not the bug. Look at statistics freshness after a bulk load, skew on the filtered column, or correlated predicates the planner assumes are independent. Fix the estimate and the plan usually fixes itself.
How do you spot an N+1 in a plan?
A node with a high loops count and small per-iteration rows, under a nested loop. The per-loop numbers look negligible, so skimming for a large value finds nothing. Multiply rows × loops and compare against what the query should logically touch.
The plan uses my index and the query is still slow. Where do you look?
Rows Removed by Filter on that node. If it is large, the index located a broad range and the actual selectivity came from a predicate it could not serve — a column-order problem in a composite index. Also check whether a Sort node sits above it, which means the index did not satisfy the ordering.
When is a nested loop the wrong choice?
When the outer side is large, because the inner side runs once per outer row. It is chosen when the planner expects a small outer side, so a nested loop over a huge actual row count is nearly always a symptom of a row-estimate error rather than a bad strategy in itself.
What does BUFFERS add that timing does not?
It separates cache hits from disk reads, which tells you whether the fix is a better plan, more memory, or a narrower index. Two queries with identical wall-clock time can be CPU-bound and I/O-bound respectively, and they need opposite remedies.
Answers that lose the round
- Reading the plan top-down and blaming the outermost node
- Treating per-loop rows as totals, so an N+1 shape looks cheap
- Adding indexes without checking estimated versus actual rows first
- Ignoring Rows Removed by Filter because the node already says "index scan"
- Using EXPLAIN without ANALYZE and reasoning from estimates alone
- Running EXPLAIN with a cheap parameter and generalising to the expensive case
- Tuning a plan that is actually fine while the query waits on a lock
Practise query execution plans in a real repository
Gronex ships a repository where several individually familiar mistakes compound — a missing access path, repeated per-row queries, an unbounded fetch — so fixing only one leaves the invariant broken. The tests assert database work per request, which is what the plan measures.
FAQ
Is EXPLAIN ANALYZE safe to run in production?
It executes the query, so a SELECT is generally fine but a write statement will actually write. Wrap writes in a transaction you roll back. Be aware it also adds timing overhead, which can make very fast nodes look relatively more expensive than they are.
Why does the same query get a different plan in production?
Different data volume and distribution, different statistics, different cache state, and possibly a cached generic plan for a prepared statement rather than the plan literals would produce. This is why reproducing a plan locally proves very little.
What does "cost" actually mean?
An arbitrary unit the planner uses to compare alternatives, not milliseconds. It is calibrated against configuration parameters like random_page_cost, so it is useful for understanding why one plan was chosen over another and useless as an absolute performance measure.
Should I use planner hints to force a plan?
PostgreSQL deliberately has none, and that is usually the right constraint — a forced plan hides the estimate problem that caused the bad choice and becomes wrong as data changes. Fix the statistics or the index instead. Disabling a node type with enable_* is a diagnostic tool, not a production fix.