Reading a Query Plan: Why Your SQL Is Slow

The query is slow. Now what?

Most advice about slow SQL is folklore: add an index, avoid select *, don't use subqueries. Sometimes it helps. Often it doesn't, because the advice was never connected to what your database is actually doing with the query.

The database will tell you what it's doing. You just have to ask.

explain analyze
select c.country, count(*) as orders
from orders o
join customers c on c.id = o.customer_id
where o.created_at >= date '2025-01-01'
group by c.country;

explain shows the plan the optimizer chose. analyze actually runs the query and reports what happened. The gap between those two — what the planner expected versus what it got — is where almost every real performance bug lives.

Callout: explain analyze executes the query. On a select that's fine. On an insert, update, or delete it will really modify your data, so wrap it in a transaction you roll back.

Read it inside out

Plans are printed as a tree, and the indentation is the whole story. The most indented nodes run first; their output feeds the node above them. So you read a plan bottom-up and inside-out, not top-down.

HashAggregate  (cost=2431.19..2436.44 rows=210) (actual time=48.117..48.203 rows=94 loops=1)
  ->  Hash Join  (cost=389.00..2337.44 rows=18750) (actual time=6.402..39.118 rows=18492 loops=1)
        Hash Cond: (o.customer_id = c.id)
        ->  Seq Scan on orders o  (cost=0.00..1698.00 rows=18750) (actual time=0.011..21.006 rows=18492 loops=1)
              Filter: (created_at >= '2025-01-01'::date)
              Rows Removed by Filter: 31508
        ->  Hash  (cost=264.00..264.00 rows=10000) (actual time=6.283..6.284 rows=10000 loops=1)
              ->  Seq Scan on customers c  (cost=0.00..264.00 rows=10000)

Read that as: scan orders, filtering by date. Separately scan customers and build a hash table from it. Join the two on customer_id. Aggregate the result. Four steps, in that order.

The four numbers that matter

Every node carries the same annotations, and you only need to understand four of them.

cost is the planner's estimate in arbitrary units. The second number is what matters — total cost including everything beneath it. Costs are only useful for comparing nodes, never as an absolute measure. A cost of 2431 means nothing on its own.

rows appears twice: once in the estimate, once in actual. This pair is the single most valuable thing in the plan. When they diverge badly — estimated 200, actual 2,000,000 — the planner made its decisions on bad information, and every choice above that node is suspect.

actual time is also a pair: time to produce the first row, then time to produce all rows. A large gap between them means the node is blocking — it has to consume everything below it before emitting anything. Sorts and hash builds do this.

loops is how many times the node executed. And here is the trap that catches everyone: actual time is per loop, not total. A node showing actual time=0.8..0.9 rows=1 loops=40000 did not take 0.9 milliseconds. It took roughly 0.9 × 40,000 = 36 seconds. Always multiply.

Scans: the bottom of every plan

Everything starts by reading data, and there are three common ways to do it.

A sequential scan reads the whole table. This is not automatically bad. If you're touching most of the rows anyway, reading them in physical order beats jumping around an index. Planners choose it deliberately.

An index scan walks the index, then fetches each matching row from the table. Great when you want a small fraction of rows, expensive when you want many — because each fetch is a random read.

A bitmap heap scan is the compromise: collect all matching locations from the index first, sort them into physical order, then read the table once in a single ordered pass. You'll see it when the planner wants more rows than an index scan handles well but fewer than a full table.

So "it's doing a Seq Scan" is not a diagnosis. The question is whether the row counts justify it.

Joins: where the time actually goes

Nested loop — for each row on the left, look up matches on the right. Excellent when the left side is tiny. Catastrophic when it isn't: that loops=40000 example above is a nested loop that shouldn't have been one.

Hash join — build a hash table from the smaller side, stream the larger side through it. The workhorse for joining two large tables on equality.

Merge join — sort both sides, walk them in lockstep. Wins when both inputs are already sorted, typically because they came off an index in the right order.

Nearly every "my query got slow overnight" story is the planner switching from a hash join to a nested loop because its row estimate collapsed. Which brings us to the actual root cause.

Bad estimates are the real bug

The planner picks a strategy based on how many rows it thinks each step will produce. Feed it wrong numbers and it makes a reasonable decision from bad premises.

Estimates go wrong for a few recurring reasons.

Stale statistics. The planner samples your tables periodically. After a bulk load, that sample is fiction. Fix it directly:

analyze orders;

Correlated columns. The planner assumes conditions are independent. Given where city = 'Dhaka' and country = 'Bangladesh', it multiplies the two selectivities and concludes almost no rows match — when in reality every Dhaka row is a Bangladesh row. Tell it the truth:

create statistics orders_geo (dependencies)
  on city, country from orders;
 
analyze orders;

Expressions the planner can't see through. where date(created_at) = '2025-01-01' hides the column inside a function call, so no column statistics apply and no plain index can be used. Rewrite it as a range and both problems disappear:

where created_at >= date '2025-01-01'
  and created_at <  date '2025-01-02'

That last rewrite is worth internalizing. Any time you wrap an indexed column in a function on the left side of a comparison, you have opted out of both the index and the statistics.

A worked diagnosis

Suppose a dashboard query degrades from 200ms to 40 seconds. The plan shows:

Nested Loop  (actual time=0.05..39221.7 rows=118400 loops=1)
  ->  Seq Scan on campaigns  (estimated rows=1 actual rows=1184 loops=1)
  ->  Index Scan on events  (actual time=0.02..0.03 rows=100 loops=1184)

The chain of causation reads bottom-up. The planner estimated one campaign, so a nested loop looked free — one lookup. Actually there were 1,184, each triggering an index scan returning 100 rows. Multiply it out and you have your 39 seconds.

The nested loop is the symptom. The estimated rows=1 actual rows=1184 line is the disease. Fix the estimate — refresh statistics, or unwrap whatever expression is hiding the filter — and the planner will pick a hash join on its own.

What to do with this

When a query is slow, resist the urge to start adding indexes. Instead:

1. Run explain analyze.
2. Find the node where estimated and actual rows diverge the most. Start there, not at the slowest node.
3. Check loops on anything that looks fast — multiply before you trust it.
4. Ask why the estimate was wrong. Stale stats, a correlation, or a hidden expression covers most cases.
5. Only then consider an index, and check the plan again afterward to confirm it's actually being used.

The plan turns performance work from guessing into reading. It takes an afternoon to get comfortable with, and it pays for itself the first time it saves you from adding an index that would never have been used.