Reading EXPLAIN ANALYZE: a practical guide to Postgres query plans

Every team has a query that was fast in January and slow in November. Nobody changed it. The table just grew, or the data shifted, and the database quietly picked a different way to answer. Postgres will tell you exactly which way it picked, if you ask. The tool for asking is EXPLAIN, and its more honest sibling is EXPLAIN ANALYZE.

This guide covers how to read the output without getting lost, what the numbers mean, and the habits that keep it safe.

Two commands, one big difference

EXPLAIN shows the plan the planner chose, with its estimates. It does not run your query. EXPLAIN ANALYZE runs the query for real and adds what actually happened next to what was expected.

That second point matters more than it looks. The PostgreSQL documentation is direct about it: because the statement really executes, any side effects happen as usual. An UPDATE under EXPLAIN ANALYZE updates rows.

Warning

Never run EXPLAIN ANALYZE on an INSERT, UPDATE or DELETE without a safety net. Wrap it in a transaction and roll back.

BEGIN;
EXPLAIN ANALYZE
UPDATE orders SET status = 'archived' WHERE created_at < now() - interval '2 years';
ROLLBACK;

You still get the timings, and the data stays as it was. Keep in mind that the work was done, so locks were taken and the machine was busy for the duration.

Reading a plan from the inside out

A plan is a tree. Each line is a node, and the indented lines beneath it are its children. Data flows upward: the innermost, most indented nodes run first and feed their parents. So read from the bottom of the deepest branch toward the top line, which is the final result.

Each node carries a group of numbers in parentheses. For the estimate they are, in order:

1. Start-up cost: the effort before the first row can come out.
2. Total cost: the effort to produce all rows.
3. Estimated rows the node will output.
4. Estimated average row width in bytes.

Costs are in arbitrary units. They are built from planner settings, where reading one sequential page is set to 1.0 and other operations are priced relative to it. The docs walk through a small table of 345 pages and 10,000 rows, where the sequential scan comes to (345 x 1.0) + (10,000 x 0.01) = 445. The unit has no meaning in seconds. Use cost to compare plans for the same query, not to predict time.

With ANALYZE, a second group appears: actual time in milliseconds, actual rows, and loops. Time and rows are averages per loop, so a node that ran 500 times with actual time=0.02 has spent about ten milliseconds in total. People miss this all the time.

The comparison that finds most problems

Put estimated rows next to actual rows. When they are close, the planner understood your data and its choices are probably sensible. When they are off by a factor of ten or a thousand, the planner is working from a bad picture, and that is where odd plans come from.

Common causes of a bad picture:

• Statistics are stale. Run ANALYZE on the table, or check that autovacuum is keeping up.
• Two columns are correlated, such as city and postal code, and the planner treats them as independent.
• A condition wraps a column in a function, so the planner cannot use its statistics for that column.

One exception is worth knowing. A LIMIT can stop a node early, so its actual row count falls below the estimate. The docs point out that this is not an estimation error, just a difference in how the two numbers are displayed.

The scan types you will meet

How a table is read tells you a lot.

Node	What it does	Typical when
Seq Scan	Reads every row in the table	Small tables, or most rows qualify
Index Scan	Walks an index, fetches matching table rows	A few rows match
Index Only Scan	Answers from the index alone	All needed columns are in the index
Bitmap Heap Scan	Collects matches from an index, then reads pages in physical order	Many rows match, but not most

A sequential scan is not a bug. On a table that fits in a page or two, or when you want most of the rows anyway, it is the cheapest honest answer. It becomes a problem when it appears on a large table for a query that returns a handful of rows. Then ask whether an index would help; the post on how indexes speed up reads and slow down writes covers that trade-off.

Two lines worth scanning for: Rows Removed by Filter tells you how much work was thrown away after reading it, and Heap Fetches on an index-only scan tells you how often Postgres still had to visit the table.

Add BUFFERS to see where the time goes

Time alone does not say whether a node was slow because of the CPU or the disk. BUFFERS adds counts of pages found in cache (hit), read from outside it (read), dirtied and written. The numbers include a node's children, so subtract to find the node's own share. Recent Postgres versions turn this on with ANALYZE, and you can write it explicitly to be sure:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 4821 ORDER BY created_at DESC LIMIT 20;

Lots of read on a first run and mostly hit on the second is the classic cold-cache pattern. Run important comparisons twice so you do not blame a plan for a cold disk.

A few habits that save time

• Test on realistic data. A plan on a thousand rows says little about a hundred million. A recent copy of production, with personal data removed, is best.
• Change one thing at a time: add an index, rewrite the filter, refresh statistics. Then compare plans.
• Remember what the timings leave out. They do not include network transfer to the client, and on machines where reading the clock is slow, the measuring itself adds overhead.
• Save the plan text next to the ticket. Next year's question, "why did this change?", becomes a comparison instead of a guess.

If you work with multi-tenant data, plans also reveal how row-level security conditions get applied; our guide to row-level security in Postgres shows the policies those plans are filtering with.

A small routine to try tomorrow

Take your slowest known query. Run it with EXPLAIN (ANALYZE, BUFFERS). Find the node with the biggest actual time after multiplying by loops. Compare its estimated and actual rows. If they disagree, refresh statistics and run again. If they agree and it is still slow, look at the scan type and ask what the query really needs.

You will not memorise every node type, and you do not have to. The skill is a short loop: read the plan, find the surprise, form one hypothesis, test it.

Sources

• PostgreSQL documentation: Using EXPLAIN