Reading EXPLAIN Output in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this INNER JOIN, WHERE Clause and Comparison Operators, GROUP BY
Builds toward Query Structure Patterns for Performance, Analyst Debugging Patterns
How do you read EXPLAIN output in SQL?
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.
How do you read an EXPLAIN plan in PostgreSQL?
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;EXPLAIN SELECT c.name, SUM(o.total_amount) AS revenue FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.id, c.name ORDER BY revenue DESC
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.
What is the difference between EXPLAIN and EXPLAIN ANALYZE?
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.
Why is a correct-looking query suddenly slow?
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.
Where do you start when reading a slow query plan?
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.
Practice Reading EXPLAIN Output in SQL
Scenario: Brightlane's data analyst ran EXPLAIN on a fulfillment health report and saw the planner estimating only 5 rows for orders whose status is 'shipped' — a number the analyst suspects is wildly off because table statistics have not been refreshed since a recent data import.
Task: 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.
Assumptions:
- The
orderstable holds one row per placed order, with the order's outcome stored instatus. - A shipped order has
statusequal to'shipped'.
Output:
- One row, holding the shipped-order count.
- Columns in this order:
shipped_order_count.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution10 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.
Start learning to practice all 10 Reading EXPLAIN Output problems, with instant grading and mastery tracking.
Common questions about Reading EXPLAIN Output
Does EXPLAIN run the query?
No. Plain EXPLAIN shows the plan and the estimates without executing anything, which makes it safe on a statement you would not want to run. Adding ANALYZE does execute it, and that is what produces the actual row counts beside the estimates.
What unit are the costs in an EXPLAIN plan?
None you can convert to time. They are arbitrary planner units, useful only for comparing one node or one plan against another. Treat a cost as a relative weight and read the row counts when you want something concrete.
What should you look at first in a plan?
The gap between estimated rows and actual rows on the most expensive node. A node that expected a handful and processed thousands tells you the planner chose on bad information, and that choice shaped everything above it.
