Reading EXPLAIN Output in SQL
`EXPLAIN` shows the execution plan PostgreSQL's query planner chose for a query before running it. Reading that plan tells an analyst where the database will spend its time, how many rows it expects to process at each step, and whether the structure of the query is causing the planner to make expensive choices.
Before this INNER JOIN, WHERE Clause and Comparison Operators, GROUP BY
Builds toward Query Structure Patterns for Performance, Analyst Debugging Patterns
EXPLAIN shows you the execution plan PostgreSQL chose for a query before running it. Reading that plan tells you where the database will spend its time, how many rows it expects to process at each step, and whether your query structure is causing expensive choices.
PostgreSQL doesn't execute SQL text directly. Before any query runs, the planner reads the SQL, looks at statistics about table sizes and column distributions, evaluates possible execution strategies, and picks the one it estimates to be cheapest. EXPLAIN makes that chosen plan visible.
The plan is a tree of operations — scans, joins, sorts, aggregations. Read it from the innermost nodes outward, because inner nodes feed rows to the nodes above them.
EXPLAIN
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;The output shows a tree: scan nodes at the bottom (one for customers, one for orders), a join node above them, an aggregation node at the top. Each node carries two estimates in parentheses: cost=start..total and rows=N. Cost numbers are in arbitrary planner units — meaningful only relative to each other, not as absolute values. The rows estimate is how many rows the planner expects that node to produce.
EXPLAIN ANALYZE
EXPLAIN ANALYZE actually runs the query and shows both estimated and actual values. This is where the real information lives:
EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;The output now shows actual rows=N alongside the estimates. The most useful thing to look for is the gap between estimated rows and actual rows. A node that estimated 10 rows but actually processed 100,000 is a signal: the planner made a bad choice based on wrong statistics, and that choice propagated through every node above it.
The one thing that trips people up
Unexpectedly slow queries that look structurally correct are often statistics problems. PostgreSQL's planner relies on statistics maintained by autovacuum. After a large data load without a subsequent ANALYZE, statistics may be badly stale — the planner estimates far fewer rows than exist and chooses a plan optimized for a small table. Running ANALYZE table_name refreshes the statistics and often resolves the issue without changing the query.
How to read a plan
Start at the highest-cost node. Check whether its row estimate matches the actual count from EXPLAIN ANALYZE. If they diverge significantly, that's where the planner went wrong. Trace why — stale statistics, missing index, or a join that produced more rows than expected. Understanding the reason makes any fix meaningful rather than speculative.
10 Reading EXPLAIN Output practice problems
Write a query to return the actual count of `orders` whose `status` is `'shipped'`, so the analyst can compare the real number against the planner's estimate.
Write a query to return the actual count of `customers` whose `country` is `'US'`, so the analyst can confirm the gap between the planner's estimate and reality.
Write a query to return the actual count of `employees` recorded in the system.
Write a query to return each `status` value and the number of `orders` recorded with that status, so the analyst can see how skewed the distribution actually is.
Write a query to return each customer country and the number of `orders` placed by `customers` from that country, so the analyst can see the actual group count.
Write a query to return the actual count of shipped `orders` represented across the customer base.
Write a query to return each department name and the number of `employees` assigned to it, so the analyst can compare the real group count against the planner's estimate.
Write a query to return each `category_id` and the total number of line items associated with `products` in that category, so the analyst can see the actual per-category contribution.
Write a query to return each `customer_id` and the combined `total_amount` across all of their `orders`, so the analyst can see the actual group count and revenue distribution.
Write a query to return the actual count of current salary records on file.
These problems are part of the Reading EXPLAIN Output lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.