The Practical Developer

EXPLAIN ANALYZE Without the Mystery: How to Read a Real Postgres Plan

July 8, 2022 by The Practical Developer

Most developers run EXPLAIN ANALYZE, see a wall of nested operators and millisecond numbers, and either guess or scroll past. Here is the line-by-line read of a real plan, what every node means, and the four numbers that decide whether you need an index or a rewrite.

database

postgres

performance

A laptop screen full of dashboard metrics — the kind of view where one slow query is hiding in plain sight

The number-one reason a slow query stays slow is that the person looking at the plan does not actually know how to read it. They squint at EXPLAIN ANALYZE, see “Seq Scan” highlighted in red on some Twitter screenshot, add an index, and call it a day. Half the time that index never gets used. The other half, the real problem was three nodes deeper.

Reading a Postgres plan is not magic. It is four numbers per node, applied in a fixed order, and a small list of rewrite triggers. This post walks through one realistic plan end to end so you can do the same on your own queries without guessing.

Set the stage with a query that is actually slow

SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= now() - interval '30 days'
GROUP BY u.id, u.email
ORDER BY order_count DESC
LIMIT 50;

Two-million-row users, twenty-million-row orders, no helpful index on users.created_at. The query takes 4.2 seconds. Run this:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;

ANALYZE actually runs the query (yes, including writes — wrap in BEGIN; ... ROLLBACK; if it mutates). BUFFERS adds the cache-hit numbers that tell you whether the slowness is CPU or I/O. VERBOSE adds output column names so you can match nodes to your SELECT list.

The shape of a plan

Postgres prints plans as a tree. The root is at the top. Children are indented underneath. Execution actually flows the opposite direction — leaves run first, parents consume their output. So read top-down to understand intent, bottom-up to understand cost.

Each node line looks like this:

->  Hash Join  (cost=12345..67890 rows=1234 width=64)
                (actual time=120.5..3500.2 rows=900 loops=1)
                Buffers: shared hit=1200 read=80000

Four numbers matter, in this order:

  1. actual time=A..B — A is when the first row was produced, B is when the last row was produced. The gap is how long this node ran. Compare it to the query total: if a single node is 80% of the time, that is your bottleneck.
  2. rows=R vs the planner’s rows= estimate — the row in (cost=... rows=1234 ...) is the estimate; actual ... rows=900 is what really came out. If those numbers are off by 10× or more, the planner is making decisions on a fantasy and you need to fix the statistics or rewrite.
  3. loops=L — the node was executed L times. Total time spent in the node = (B − A) × L. People forget this constantly. A node that says actual time=0.1..0.2 loops=500000 is not fast — it is 50 seconds of work.
  4. Buffers: shared hit=H read=R — hit is from the buffer cache (RAM), read is from disk. A node with read=80000 is doing a lot of physical I/O; an index that converts those into hits is the obvious win.

Memorize that order. Time, estimate vs actual, loops, buffers. Most plan reads end after step two.

Walking the example plan

Here is what EXPLAIN ANALYZE returned for the query above (trimmed):

Limit  (actual time=4180..4180 rows=50 loops=1)
  ->  Sort  (actual time=4180..4180 rows=50 loops=1)
        Sort Key: (count(o.id)) DESC
        Sort Method: top-N heapsort  Memory: 32kB
        ->  HashAggregate  (actual time=4150..4170 rows=42000 loops=1)
              Group Key: u.id, u.email
              ->  Hash Right Join  (actual time=120..3900 rows=98000 loops=1)
                    Hash Cond: (o.user_id = u.id)
                    ->  Seq Scan on orders o  (actual time=0.02..2400 rows=20000000 loops=1)
                          Buffers: shared read=400000
                    ->  Hash  (actual time=110..110 rows=42000 loops=1)
                          ->  Seq Scan on users u  (actual time=0.01..100 rows=42000 loops=1)
                                Filter: (created_at >= now() - interval '30 days')
                                Rows Removed by Filter: 1958000
                                Buffers: shared read=18000

Read it once top-down. The query Limits 50 rows, after Sorting on order_count DESC, after HashAggregating, after a Hash Right Join between orders and users. Both inputs to the join are Seq Scans.

Now read it bottom-up with the four numbers in mind.

Node: Seq Scan on users. Actual time 0.01..100ms, returns 42k rows out of 2M (a 2% selectivity filter), Rows Removed by Filter: 1958000, Buffers: read=18000. Postgres scanned 18,000 disk pages to throw away 1.96M rows. This is the first red flag: an index on users.created_at would let it touch ~600 pages instead.

Node: Seq Scan on orders. Actual time 0..2400ms, 20M rows, Buffers: read=400000. Yikes — 400,000 disk pages, 2.4 seconds. We are scanning every order in the system to join. There is no WHERE on orders, so this scan is logically necessary unless we restructure the join.

Node: Hash Right Join. Actual time 120..3900ms. Almost the entire query lives here. The reason is the right side (Seq Scan on orders) is what feeds it row by row. The hash table itself is small — only 42k rows from users — and is built in 110ms. The expensive part is probing it 20M times.

Node: HashAggregate. 30ms (4150 - 4120). Cheap.

Node: Sort + Limit. 30ms with top-N heapsort. Cheap and correct — Postgres knows we only want 50 rows so it does not sort everything.

The hierarchy of cost is now obvious: 2400ms in Seq Scan on orders, 1500ms in the hash probe, ~100ms everywhere else combined. Adding an index on users.created_at would shave the user-side scan but the orders-side dominates.

What rewrite this plan asks for

Two changes. The first is an index that will pay back instantly:

CREATE INDEX users_created_at_idx ON users (created_at);

That removes the 18,000-buffer Seq Scan on users and replaces it with an Index Scan that touches about 600 buffers. Worth doing — but the orders scan is the bigger problem.

The fix for the orders scan is to push the user filter into the join condition so the join only needs to look at orders for new users:

WITH new_users AS (
  SELECT id, email FROM users
  WHERE created_at >= now() - interval '30 days'
)
SELECT n.id, n.email, COUNT(o.id) AS order_count
FROM new_users n
LEFT JOIN orders o ON o.user_id = n.id
GROUP BY n.id, n.email
ORDER BY order_count DESC
LIMIT 50;

If orders.user_id has an index (it should — it is a foreign key), Postgres will switch from a Hash Right Join over all 20M orders to a Nested Loop with Index Scan that touches only the orders for those 42k new users — perhaps 80k rows total instead of 20M.

Re-run EXPLAIN ANALYZE:

Limit  (actual time=145..145 rows=50 loops=1)
  ->  Sort  (actual time=145..145 rows=50 loops=1)
        ->  HashAggregate  (actual time=140..142 rows=42000 loops=1)
              ->  Nested Loop Left Join  (actual time=0.5..120 rows=98000 loops=1)
                    ->  Index Scan on users_created_at_idx (actual time=0.1..15 rows=42000 loops=1)
                    ->  Index Scan on orders_user_id_idx (actual time=0.001..0.002 rows=2 loops=42000)

4.2s → 145ms. A 29× speedup.

Notice how the bottom Index Scan reads loops=42000. Each loop is microseconds, but multiply through: 42000 × 0.002ms ≈ 84ms. That’s the chunk of total time that is this join. Without the loops field you would think the inner side ran in two microseconds.

The patterns that show up in every plan

Once you have read a few plans, certain shapes start jumping out. Internalize these and you will diagnose 80% of slow queries on first read.

Estimate vs actual mismatch (10×+). The optimizer guessed wrong. Run ANALYZE table_name, increase default_statistics_target for the column, or break the query into pieces. A bad estimate cascades — the planner picks a hash join thinking it has 100 rows, actually has 100k, and now you have a hash table that does not fit in work_mem and spills to disk.

Rows Removed by Filter is large. Postgres found a row, then threw it away because the filter did not match. Means the filter is not in the index or the index is not selective enough. Add an index that includes the filter column, or a partial index.

High loops on an inner Nested Loop side. Multiply (B − A) × loops to see real cost. If it dominates, either the outer side has too many rows (rewrite to reduce them) or the inner side has no usable index.

Disk reads in Buffers. read= numbers in the thousands mean the buffer cache missed. Either the working set does not fit in RAM (raise shared_buffers), or you are reading more pages than necessary (better index, smaller row width via TOAST-aware design).

Sort Method: external merge. The sort spilled to disk because work_mem was too small. Bump work_mem for the session, or rewrite to avoid the sort (e.g., index supports the order, or use an aggregate that does not require a global sort).

Hash Join with a giant outer side. The “big” table should be the inner (probed) side, not the outer (build) side. If the outer side is 20M rows, you will probe a small hash table 20M times, which is fine — but if the build side is 20M, you build a 20M-row hash table in memory or spill it. Postgres usually picks correctly, but bad statistics flip it.

A workflow that actually works

When somebody hands you a slow query:

  1. Run EXPLAIN (ANALYZE, BUFFERS) <query>; and paste the plan into explain.depesz.com — it color-codes the bottleneck node so your eye lands there immediately.
  2. Find the single node that owns the most cumulative time ((B−A) × loops). Ignore everything else.
  3. Check the rows estimate vs actual on that node. If the gap is large, fix statistics first; the planner is not your enemy, it is just misinformed.
  4. Look at Buffers. High read= means I/O. High hit= and still slow means CPU — too many rows, too much sorting, too much hashing.
  5. Pick exactly one change: index, rewrite, statistics, or work_mem. Re-measure. Then move to the next bottleneck.

This loop is boring. That is the point. The flashy version — installing a query analyzer, reading 30-page Postgres internals chapters, arguing about CTE inlining — has its place, but it is not what gets the 4.2s query down to 145ms. The numbered loop above is.

The takeaway

Plan reading is one of those skills that looks specialized and is not. There are four numbers per node — actual time, estimated vs actual rows, loops, buffers — and a short list of rewrite triggers. Apply them in order, top down, until the bottleneck node is obvious. Index it, rewrite it, or fix the statistics behind it. Re-run, repeat.

Most “we need to upgrade the database” conversations turn out to be one missing index and one CTE rewrite away from being closed. The faster the team gets at reading plans, the fewer of those conversations happen.

A note from Yojji

Performance work — reading plans, picking the right index, deciding between a CTE rewrite and a denormalization — is most of what makes a backend feel fast under load, and most of what gets cut when a team is shipping features against a deadline. It is also the kind of unglamorous engineering Yojji has been doing in production for years.

Yojji is an international custom software development company founded in 2016, with offices across Europe, the US, and the UK. Their teams specialize in JavaScript (React, Node.js, TypeScript), cloud platforms (AWS, Azure, GCP), and the kind of long-haul backend work — query tuning, schema design, observability — that decides whether a product still feels good at 100× the original traffic.