queries & joins
EXPLAIN & Query Plans
Read what the optimizer actually does — scan types, join methods, row estimates — so you can target the real bottleneck.
EXPLAIN is the single most important tool for diagnosing a slow query, because it shows what the database's query optimiser actually decided to do rather than what you assumed it would. Optimising by guesswork — adding indexes hopefully, rewriting blindly — wastes effort; reading the plan tells you exactly which operation is expensive and why.
A flight itinerary vs the actual flight. EXPLAIN is the plan; ANALYZE is the flight log.
Key Concepts
1
EXPLAIN returns the execution plan as a tree of operators, read from the innermost/most-indented outward. It reveals the access method for each table — a sequential/full scan versus an index seek — the join algorithm chosen (nested loop, hash join, merge join), the order tables are joined in, and the optimiser's estimated row counts and relative cost for each step. EXPLAIN ANALYZE goes further by actually running the query and reporting the real time and real row counts beside the estimates. That comparison is where the gold is: a large gap between estimated and actual rows usually means stale statistics or a correlation the optimiser cannot see, which is often the root cause of a bad plan. You are hunting for a few telltale signs — an unexpected full scan on a large table, a nested loop over a huge number of rows, an expensive sort or hash that spilled to disk, or a row estimate that is wildly wrong.
EXPLAINEXPLAIN ANALYZE
2
The workflow interviewers expect is to read the plan, find the dominant cost, and then act on that specific operator: add or fix an index to convert a scan into a seek, rewrite a predicate that is defeating an index, update table statistics so estimates improve, or restructure the query to reduce the rows flowing through an expensive join. The discipline is measure-then-fix — let EXPLAIN ANALYZE point at the real bottleneck instead of optimising on intuition.
EXPLAIN ANALYZE