EXPLAIN ANALYZE: Reading and Optimising Query Plans

Every SQL statement you send is compiled by the query planner into an execution plan — a tree of operations describing how PostgreSQL will actually get your data. EXPLAIN shows you that plan; EXPLAIN ANALYZE runs the query and shows the plan annotated with real timings and row counts. Reading these plans is the master skill of query optimisation, because it replaces guesswork with evidence about what the database is really doing.

A plan is read as a tree, with execution flowing from the innermost, most-indented nodes outward to the top. Each node is an operation — a scan, a join, a sort, an aggregate — labelled with the planner's cost estimate and, under ANALYZE, the actual time and rows. The art is comparing estimates to reality and spotting the node where time is truly spent.

Once you can read a plan you can answer the questions that matter: is this query using my index or scanning the whole table? Why is this join slow? Where did the planner badly misjudge the row count? Optimisation then becomes targeted — fix the one node that dominates — rather than randomly adding indexes and hoping.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.