PostgreSQL EXPLAIN ANALYZE: Read Query Plans Correctly

Your query was fast on Friday. On Monday it takes four seconds and the on-call page wakes you up. You add an index, nothing changes. You add another index, still four seconds. At this point most people start guessing: rewrite the WHERE clause, throw a materialized view at it, blame the ORM.

The boring truth is that the database already told you exactly what is wrong. You just did not read the plan.

EXPLAIN ANALYZE is the single most useful performance tool in PostgreSQL, and most developers use it wrong. They run it once, stare at the wall of text, and go back to guessing. This guide is about reading that wall of text properly: what each line means, which patterns signal real trouble, and the timing traps that mislead people into optimizing the wrong thing.

EXPLAIN Estimates. EXPLAIN ANALYZE Measures.

First, the distinction that matters.

EXPLAIN shows you the planner's prediction. It never runs the query. The row counts and costs are estimates based on table statistics. If your statistics are stale, the estimates are fiction, and you are debugging a fantasy.

EXPLAIN ANALYZE actually runs the query and reports real numbers next to the estimates: actual rows returned, actual loop counts, actual time spent per node. That is the version you want in almost every real debugging session.

The habit to build: run both, mentally. The plan shape comes from EXPLAIN, but the truth comes from the actuals. When the estimate says 10 rows and the actual is 2 million, you have found your problem, and it is a statistics problem, not a query problem.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
AND created_at > now() - interval '30 days';

Reading a Plan: The Four Numbers That Matter

A plan node looks like this:

Index Scan using idx_orders_customer on orders  (cost=0.42..8.44 rows=12 width=128) (actual time=0.031..0.058 rows=12 loops=1)

Every node has four things worth your attention:

Cost is the planner's internal unit, not milliseconds. It is only useful for comparing nodes within the same plan. The startup cost is before the first row comes out; the total cost is when the last row is done. Do not try to convert cost to time.

Rows is the estimate unless ANALYZE ran, in which case you also get actual rows. The gap between estimate and actual is the single most informative number in the whole output. A 100x gap means the planner is flying blind on that node.

Loops multiplies everything. An inner node with loops=10000 ran ten thousand times, once per row from the outer side of the join. A cheap node becomes an expensive node when it loops. This is where the 2am disasters hide.

Actual time is per loop, in milliseconds, and it is exclusive to that node plus its children in most readings. To get a node's own cost, you subtract the children's times. People forget this and blame the parent node for time its children spent.

That is the whole reading skill. Everything else is pattern matching on top of these four numbers.

The Three Scans That Decide Your Latency

Most of the plan's wall time lives in scan nodes. Learn to recognize these three.

Seq Scan

Seq Scan on orders  (cost=0.00..18342.00 rows=1000000 width=128) (actual time=0.015..412.033 rows=998432 loops=1)

The database read every row in the table. On a 200-row lookup table, fine. On a million-row table with a WHERE clause that matches twelve rows, this is your bug. The fix is almost always a missing or unused index, or a WHERE clause written in a way the index cannot serve (a function call on the column, a leading wildcard LIKE, a type mismatch that defeats the index).

Index Scan

Index Scan using idx_orders_customer on orders  (cost=0.42..24.10 rows=12 width=128) (actual time=0.028..0.041 rows=12 loops=1)

The planner walked the index and fetched matching rows from the table. This is the happy path for selective filters. Note that it still does heap fetches for each row, which matters when the table is wide or the rows are scattered.

Index Only Scan

Index Only Scan using idx_orders_customer_created on orders  (cost=0.42..18.77 rows=12 width=36) (actual time=0.019..0.029 rows=12 loops=1)

The index contained every column the query needed, so PostgreSQL never touched the table. This is why covering indexes matter for hot read paths. If your query selects three columns and the index holds all three, the table read disappears. When I see a slow query that only needs a few columns, a covering index is the first thing I try.

There is also the Bitmap Heap Scan, which shows up when the planner needs to combine multiple indexes or fetch a moderate number of scattered rows. It is not bad by itself. It becomes suspicious when the bitmap is built over most of the table, which is just a seq scan wearing a costume.

Nested Loops: The Classic 2am Killer

Joins are where small mistakes become large outages. The nested loop is the most dangerous join type because it looks cheap in the estimate and explodes in the actuals.

Nested Loop  (cost=0.84..12410.55 rows=1 width=200) (actual time=0.044..1892.311 rows=48312 loops=1)
  ->  Index Scan using idx_orders_customer on orders  (cost=0.42..8.44 rows=12 width=128) (actual time=0.031..0.058 rows=12 loops=1)
  ->  Index Scan using idx_items_order on order_items  (cost=0.42..8.31 rows=4 width=72) (actual time=0.012..0.019 rows=4026 loops=12)

Read it carefully. The inner side estimated 4 rows per loop but actually returned 4,026 rows per loop, and it ran 12 times. That is roughly 48,000 index lookups instead of 48. The estimate was wrong, so the planner picked a nested loop, and the actual work was a thousand times heavier than planned.

The root cause is almost never the join itself. It is the bad row estimate, which is usually stale or missing statistics. Before you rewrite the query, run:

ANALYZE orders;
ANALYZE order_items;

Then re-run EXPLAIN ANALYZE. If the estimates now match reality and the planner picks a hash join, you are done, and the fix took thirty seconds. If the statistics are fresh and the estimate is still wrong, check for skewed data or correlated columns, and consider whether the query's filter is something the statistics cannot model, like a join on an expression.

The rule I follow: nested loops are fine when the inner side is genuinely small and indexed. They are a fire when the inner side is large and the estimate said otherwise.

The ANALYZE Timing Traps

EXPLAIN ANALYZE measures real execution, but its measurements come with two traps that regularly mislead people.

Trap one: the timing overhead. Each node gets timed with a system call. On plans with tens of thousands of nodes, the overhead itself distorts the numbers. For a first pass at a big plan, run EXPLAIN (ANALYZE, TIMING OFF). You lose per-node times but keep the actual row counts and loop counts, which are usually what you need anyway. Re-enable timing when you are zooming into a specific slow node.

Trap two: caching. The first run reads from disk; the second run reads from shared buffers or the OS page cache. If you run the query twice and compare, you are comparing cold storage against warm memory. Always add BUFFERS to see what actually happened:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer on orders  (cost=0.42..8.44 rows=12 width=128) (actual time=0.031..0.058 rows=12 loops=1)
  Buffers: shared hit=14

shared hit=14 means 14 pages came from memory. If instead you see shared read=1400, those pages came from disk on this run, and the timing includes disk latency that will not be there on the next run. People regularly "fix" a query that was just cold. Check the buffers before you celebrate or panic.

A third, smaller trap: EXPLAIN ANALYZE on INSERT, UPDATE, or DELETE actually executes the write. On a production database, wrap it in a transaction and roll back:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'shipped' WHERE id = 101;
ROLLBACK;

Forgetting the ROLLBACK is the kind of mistake you make exactly once.

A Full Walkthrough: Before and After

Here is the pattern I run through on every slow query. A reporting query against an orders table started timing out. The plan:

Seq Scan on orders  (cost=0.00..28410.00 rows=50000 width=96) (actual time=0.020..890.114 rows=48210 loops=1)
  Filter: (status = 'pending' AND created_at > '2026-09-01'::date)
  Rows Removed by Filter: 951790

Rows Removed by Filter: 951,790. The database read a million rows and threw away 95 percent of them. That line alone tells you the whole story. The fix is an index that serves the filter, with the selective column first:

CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders (status, created_at);

(Use CONCURRENTLY on any table that matters. A regular CREATE INDEX takes a write lock and your deploy becomes an outage.)

After the index:

Index Scan using idx_orders_status_created on orders  (cost=0.43..1240.10 rows=48210 width=96) (actual time=0.035..96.412 rows=48210 loops=1)
  Index Cond: ((status = 'pending'::text) AND (created_at > '2026-09-01'::date))

The filter became an index condition. No rows removed by filter, because the index only visited matching rows. Notice the estimate now says 48,210 and the actual is 48,210: with the right access path, the planner's math lines up.

Then verify with BUFFERS that the remaining time is real work and not cold cache, and check whether a covering index would remove the heap fetches if this query runs constantly. That is the full loop: read the plan, find the removed-rows line or the loop explosion, fix the access path, verify the actuals.

Practical Takeaway

Keep a short checklist taped to your monitor for the next slow query:

Run EXPLAIN (ANALYZE, BUFFERS), not bare EXPLAIN. Estimates without actuals are guesses.
Find the biggest gap between estimated and actual rows. Fix statistics with ANALYZE before you touch the query.
Look for Rows Removed by Filter with a large number next to a Seq Scan. That is a missing index.
Check loops on nested loop joins. A cheap inner node times ten thousand loops is not cheap.
Read the Buffers line before trusting the timing. Cold cache lies.
Wrap writes in BEGIN / ROLLBACK when analyzing them.

You do not need to memorize every node type. Most slow queries in PostgreSQL are one of three things: a missing index, stale statistics, or a join the planner misjudged because of the first two. EXPLAIN ANALYZE shows you which one in under a minute, if you read the actuals instead of the estimates.