How to Read an EXPLAIN Plan

Quick answer
Run EXPLAIN ANALYZE, not plain EXPLAIN: the plan shows what the planner estimated, while ANALYZE shows what actually happened.
Read the plan inside out — the innermost, most-indented nodes execute first — and follow the rows upward.
Two numbers matter most: the estimated rows versus the actual rows at each node, and which node consumes the most time.
A sequential scan is not automatically bad; it is the problem when it happens on a large table with a selective filter.
Look for a big gap between estimated and actual rows — a bad estimate is what makes the planner choose a poor strategy.

What does an EXPLAIN plan actually show?

It shows the order of operations the planner chose to produce your result, one node per operation. Each node reports a cost estimate, a row estimate, and — if you asked for ANALYZE — the real row count and real time. The plan is a tree: nodes feed their output to the node above them, so the leaves are where rows enter and the root is your final result.

The most useful habit is to treat it as a list of claims the planner made and check them against reality. The planner chose this strategy because it believed certain numbers about your data; if those estimates are wrong, the strategy is wrong too. Reading a plan is therefore less about memorising node names and more about comparing the estimate with the measurement.

Step 1 — run the right command

Plain EXPLAIN gives you the plan without running the query, which is useful for a dangerous statement but useless for finding real cost. EXPLAIN ANALYZE runs the statement and reports actual timings and rows. Add BUFFERS to see how much data was read from cache versus disk, which is how you tell a disk-bound query from a CPU-bound one.

Wrap writes in a transaction you roll back so ANALYZE can run against them safely, or run the SELECT form on a replica. For a parameterised query, execute with realistic parameter values rather than nulls, because the plan for a selective value is not the plan for a broad one.

-- The form worth using: real rows, real time, real I/O.
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, c.email, count(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.created_at > now() - interval '90 days'
GROUP BY c.id, c.email
ORDER BY orders DESC
LIMIT 20;

-- Writing a statement? Wrap it so the changes are discarded.
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'shipped' WHERE id = 42;
ROLLBACK;

Step 2 — read the nodes from the inside out

The innermost, most-indented node runs first. Follow the arrows upward: a scan produces rows, a join combines two inputs, a sort or aggregate reduces them, and a limit cuts the output. The root node’s total time is the query’s time, and child times are included in their parents’ — so do not add the numbers up, read them as nested.

Assign each node a role. A scan is how rows enter; if it is a sequential scan on a large table with a selective WHERE clause, that is usually the finding. A join is how two inputs are combined: a nested loop is efficient only when the outer side is small, a hash join suits larger inputs, and a merge join suits pre-sorted data. A sort at the top of a large result means the planner could not use an index order.

Step 3 — compare estimates with reality

This is where the plan tells you the truth. Each node has an estimated row count, chosen before execution, and — with ANALYZE — an actual row count. A ratio of ten or a hundred between them indicates statistics that do not describe the data, such as after a bulk load, a skewed distribution, or a value that falls outside the histogram. When the estimate is badly wrong, the planner can pick a nested loop for what turns out to be a large outer set and run forever in effect.

Refreshing statistics is the first remedy — PostgreSQL’s ANALYZE, MySQL’s ANALYZE TABLE — and increasing the statistics target or creating extended statistics helps for skewed columns. The second is to make the query’s shape legible to the planner: a function wrapping a column in the WHERE clause hides the index, so WHERE date(created_at) > x prevents the planner from using an index on created_at, while WHERE created_at > x allows it.

Step 4 — find the one line to change

Pick the node with the highest self time — the part not accounted for by its children — or the node where estimated and actual rows diverge most. Fix that one thing, then re-read the plan. Changing three things at once leaves you unable to say which mattered, and the plan is cheap enough to run repeatedly.

The common fixes are an index that matches the filter and the order, a rewrite that removes a function wrapping an indexed column, a refresh of statistics, or reducing the rows early with a tighter predicate. If the plan is already reasonable and the query is still slow, the cost is elsewhere — returning far more rows than the application uses is a design problem, not an indexing one.

Where this bites vibecoders
Generated queries tend to be shaped for readability rather than for the planner: functions applied to indexed columns, SELECT * where a couple of columns would do, and joins that pull every row before filtering. Each of those is invisible in development and expensive at scale. Running EXPLAIN ANALYZE on the three slowest queries in an app is usually the highest-yield half hour available, and the plan points at the line to change rather than at the query as a whole.

Where AI coding assistants get this wrong

Using plain EXPLAIN and reading only the estimated cost.
Adding an index without confirming the planner uses it.
Wrapping an indexed column in a function inside the WHERE clause.
Reading the plan top-down and concluding the last operation is the expensive one.
Ignoring the estimated-versus-actual row gap, which explains most bad plans.
Tuning several things at once and re-measuring without a baseline.

Checklist

Run EXPLAIN (ANALYZE, BUFFERS) with realistic parameter values.
Read the plan inside out and identify the scans, joins, and sorts.
Find the node with the largest self time.
Compare estimated and actual rows at every node.
Refresh statistics if the estimates are badly wrong.
Change one thing, re-run the plan, and record the difference.

FAQ

Is a sequential scan always bad?

No. For a small table, or a query that legitimately needs most of the rows, a sequential scan is the cheapest option and the planner is right to choose it. It is a finding when the table is large and the filter is selective, because then an index should be doing the work instead.

What is the difference between cost and time?

Cost is an abstract unit the planner uses to compare strategies, based on page reads and CPU estimates; it is not milliseconds and not comparable across queries. Time only appears with ANALYZE and is what actually happened. Use cost to understand the choice and time to judge the result.

Why does my query use an index in development and not in production?

Usually statistics or data volume. With few rows the planner correctly prefers a sequential scan; as the table grows the index becomes cheaper, but only after statistics are refreshed. In production, run ANALYZE after a bulk load and check whether the plan changes.

How often should I look at plans?

Whenever a query is slow, and once after any change that alters data volume or shape significantly. Plans change as the data does, so a query that was fine last quarter can become a sequential scan later. Keeping the slow-query log on means you find out from the log rather than from users.