Reading EXPLAIN ANALYZE without guessing

2 Jul 2025 · postgres, databases, performance

Most advice about EXPLAIN stops at "look for Seq Scan, add an index." That's occasionally right and often wrong, and it doesn't teach you to read the thing. Here's the mental model I actually use, built around one query that was taking 900 ms when it should have taken single digits.

First rule: use EXPLAIN (ANALYZE, BUFFERS), not plain EXPLAIN. Plain EXPLAIN only prints the planner's estimates. ANALYZE actually runs the query and prints what really happened, and BUFFERS tells you how much data it touched. Estimates lie; measurements don't.

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'DE'
  AND o.created_at >= now() - interval '30 days';

The plan came back like this (trimmed):

Hash Join  (cost=... rows=512 width=16)
           (actual time=812..898 rows=48911 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o
        (actual time=0.1..live 612 rows=2.4M loops=1)
        Filter: (created_at >= now() - '30 days')
        Rows Removed by Filter: 9100000
  ->  Hash  (actual time=71..71 rows=81000 loops=1)
        ->  Seq Scan on customers c
              Filter: (country = 'DE')
Planning Time: 0.4 ms
Execution Time: 902 ms

Read it inside-out, bottom to top

A plan is a tree. The deepest, most-indented nodes run first and feed their parents. So I read from the most indented lines upward. Two things jump out.

The estimate is wildly off. The top node estimated rows=512 but actual ... rows=48911. A gap of two orders of magnitude means the planner is working with bad statistics and may well be choosing the wrong strategy as a result. When estimate and actual disagree this much, that's your first lead, not the scan type.

The Seq Scan on orders is doing real work. Look at Rows Removed by Filter: 9100000. Postgres read 11.5 million rows off disk and threw away 9.1 million of them to keep 2.4 million. That's where the 612 ms lives. A Seq Scan isn't automatically bad — if you need most of a table, scanning it sequentially is the fastest way — but reading 11M rows to use a fifth of them is wasteful.

The fix, and why

There was no index on orders.created_at, so the date predicate couldn't be used to skip rows; the only way to apply it was to read everything and filter. Adding the index let Postgres jump straight to the recent slice:

CREATE INDEX CONCURRENTLY idx_orders_created_at
  ON orders (created_at);

I use CONCURRENTLY on any table that's taking live traffic — it builds without holding a lock that blocks writes, at the cost of being slower and needing to run outside a transaction block.

After that, the orders node became an Index Scan, read ~2.4M rows instead of 11.5M, and execution dropped to about 70 ms. Still not instant, because 2.4M rows genuinely is a lot to join, but the waste was gone.

The checklist I run through

Execution Time, not Planning Time. Planning is almost always sub-millisecond; the time you care about is at the bottom.
Estimate vs. actual rows. A big gap means stale stats — try ANALYZE <table> — or a correlation the planner can't see.
Rows Removed by Filter. High numbers mean you're reading data only to discard it. That's the index-shaped hole.
loops. A fast node with loops=50000 is a nested-loop join running 50,000 times; multiply before you trust the per-loop time.
BUFFERS. shared read is cache misses going to disk; shared hit is cache. A query that's all reads on the first run and all hits on the second was just cold.

The scan type is the symptom. The row counts and the estimate gap are the diagnosis. Once I started reading the numbers instead of pattern-matching on "Seq Scan," the plans stopped being intimidating.