Reading PostgreSQL EXPLAIN ANALYZE Output, Node by Node

To read a PostgreSQL EXPLAIN ANALYZE plan, start at the innermost node and work outward. For each node, compare estimated rows with actual rows, multiply per-loop time by loops, and check Buffers and Rows Removed by Filter. The node where estimates first diverge from reality, or where most time is spent, tells you whether to fix statistics, the query, or an index.

Quick answer

• Run EXPLAIN (ANALYZE, BUFFERS) on the slow query. In PostgreSQL 18, BUFFERS is included automatically with ANALYZE.
• Compare rows= (estimate) with actual ... rows= on each node. Large gaps usually mean stale or insufficient statistics.
• actual time and rows are per-loop averages. Multiply by loops to get the real total for the node.
• A Seq Scan with a large Rows Removed by Filter is the classic sign that an index on the filter columns may help.
• If columns in a WHERE clause are correlated and the estimate is too low, try extended statistics before adding indexes.

Assumptions and a safe way to run it

Everything here is based on the PostgreSQL 18 documentation for EXPLAIN and the EXPLAIN command reference. Older versions print the same structure, but some details differ: for example, version 18 shows fractional row counts such as rows=10.00 in actual output and an Index Searches line on index scans.

The plans below are illustrative. I wrote the numbers to show how to read a plan, and they do not come from a real system.

EXPLAIN ANALYZE executes the statement. For INSERT, UPDATE, DELETE, MERGE or CREATE TABLE AS, wrap it so the side effects are discarded:

-- PostgreSQL 18 assumed; works the same in psql on Linux, macOS or Windows
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'archived' WHERE created_at < now() - interval '2 years';
ROLLBACK;

Also remember that the timing instrumentation itself has a cost. The docs note that overhead can be significant for nodes that run very briefly many times, and TIMING OFF keeps row counts while dropping per-node clock reads.

The anatomy of one node

Every line has the same shape:

Seq Scan on orders o  (cost=0.00..8500.00 rows=40 width=24) (actual time=0.05..120.30 rows=18250.00 loops=1)

The first parenthesis is the planner's prediction, the second is what happened:

• cost=startup..total is in arbitrary units derived from planner cost settings, conventionally page fetches. Startup is work before the first row (a sort, for instance). Total assumes the node runs to completion. A parent's cost includes its children.
• rows in the estimate is the number of rows the node emits, not the number it scans. A filter reduces it.
• width is the estimated average row size in bytes.
• actual time is in milliseconds, so you cannot compare it directly with cost units.
• loops is how many times the node ran. Per the docs, the actual time and rows are averages per execution, so multiply by loops for the total.

Plans are trees printed with children indented under parents. Execution is driven from the top, but your reading order should be bottom-up: leaf scans first, then joins, then sorts and limits.

Reading a plan node by node

Here is an illustrative plan for a "pending orders in the last week" query on a hypothetical orders table joined to customers:

-- Illustrative schema, PostgreSQL 18
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.created_at, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at >= now() - interval '7 days';

Nested Loop  (cost=0.29..8820.00 rows=40 width=80) (actual time=0.90..268.40 rows=18250.00 loops=1)
  Buffers: shared hit=61200 read=8400
  ->  Seq Scan on orders o  (cost=0.00..8500.00 rows=40 width=24) (actual time=0.05..120.30 rows=18250.00 loops=1)
        Filter: ((status = 'pending'::text) AND (created_at >= (now() - '7 days'::interval)))
        Rows Removed by Filter: 981750
        Buffers: shared hit=2100 read=8400
  ->  Index Scan using customers_pkey on customers c  (cost=0.29..8.00 rows=1 width=56) (actual time=0.008..0.008 rows=1.00 loops=18250)
        Index Cond: (id = o.customer_id)
        Buffers: shared hit=59100
Planning Time: 0.4 ms
Execution Time: 270.1 ms

Step 1: the leaf scan on orders

The scan estimated 40 rows and returned 18,250. That is a misestimate of more than 400 times. It also read a million rows to keep 18,250 (Rows Removed by Filter: 981750), and most of its buffers were read rather than hit, so it touched pages that were not in cache.

Step 2: the join that trusted the estimate

A nested loop is a sensible choice for 40 outer rows. With 18,250 outer rows it means 18,250 index lookups on customers. Look at the inner node: 0.008 ms looks free, but 0.008 * 18250 is about 146 ms, over half of the total. That is why you multiply by loops.

Step 3: decide where the problem is

The join method is a symptom. The cause is the estimate on the first scan. Fixing the bad row estimate lets the planner pick a different join, such as a hash join, by itself.

Spotting misestimates, and not over-reading them

The docs call the row estimate comparison the most important thing to check. Two cases look like misestimates but are not:

• LIMIT. A node under a Limit may stop early, so its actual rows are lower than its estimate, which describes a full run.
• Merge joins. Duplicate outer values can cause inner rescans, which inflate the inner child's reported actual rows.

Other nodes worth recognizing:

Check the statistics before touching anything else. The planner statistics chapter explains that pg_class.reltuples and the per-column data in pg_stats are only refreshed by VACUUM, ANALYZE and some DDL. After a bulk load or large delete, run ANALYZE yourself instead of waiting for autovacuum, then re-run the plan:

-- PostgreSQL 18 assumed
ANALYZE orders;

SELECT attname, n_distinct, most_common_vals
FROM pg_stats
WHERE tablename = 'orders' AND attname IN ('status', 'created_at');

If the data is skewed, raise the target for that column with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS n (the global default is default_statistics_target = 100). A higher target costs space and planning time, so apply it to specific columns.

When the columns are correlated

The planner assumes conditions on different columns are independent and multiplies their selectivities. If the columns are related, it underestimates. The docs give an example where independence-based estimates were roughly two orders of magnitude too low for a city and state pair. Extended statistics address this:

-- PostgreSQL 12+ for the mcv kind; check the docs for your version
CREATE STATISTICS orders_status_customer (dependencies, mcv)
  ON status, customer_id FROM orders;
ANALYZE orders;

The docs warn to create these only for column groups that are queried together and produce bad plans, because each one adds ANALYZE and planning cost. Functional dependencies also apply only to equality comparisons with constants and IN lists, not to ranges or LIKE.

Deciding on an index

Only consider an index once the estimate is sane, otherwise you may fix the wrong thing. Then ask:

1. Does the slow node filter most rows away? A scan that keeps 18,250 of a million rows (under 2 percent) is a good candidate.
2. Is the filter stable? If the query always asks for status = 'pending', a partial index is smaller than a full one.
3. Do you also need an order? An index matching the ORDER BY can remove a sort node.

For the illustrative query, a partial index could look like this:

-- PostgreSQL 18 assumed; run outside a transaction block
CREATE INDEX CONCURRENTLY orders_pending_created_idx
  ON orders (created_at)
  WHERE status = 'pending';

CONCURRENTLY avoids blocking writes during the build but takes longer and cannot run inside a transaction. After creating it, run ANALYZE orders and EXPLAIN (ANALYZE, BUFFERS) again. Confirm that the scan changed and that Buffers dropped. If the plan did not change, do not assume the index is useless: check that the predicate matches the query exactly and that the estimate is now closer.

An index is not free. It slows writes and takes disk space. If a query reads a large fraction of the table, a sequential scan or bitmap scan can be the right plan, and forcing an index will make it slower. Test on data with production-like volume, since the docs caution that results from small tables should not be extrapolated to large ones.

Where this fits in product work

At Legacies, Alexandru Talnaci is co-founder and technical lead, responsible for backend architecture, integrations and product stability. Plan reading is a small part of that kind of work: slow queries tend to show up as unstable behavior in integrations long before they show up as database alerts. If you maintain a PostgreSQL-backed product and want help with architecture or custom development, see Legacies, the company site at legacies.ro, or selected work.

Frequently Asked Questions

Does EXPLAIN ANALYZE change my data?

It executes the statement, so side effects of INSERT, UPDATE, DELETE and MERGE really happen, although query output is discarded. Wrap those statements in BEGIN and ROLLBACK when you only want the plan. A plain SELECT has no data side effects, but it still consumes resources.

What is the difference between cost and actual time?

Cost is the planner's estimate in arbitrary units based on cost parameters, conventionally page fetches. Actual time is measured in milliseconds during execution. They are not directly comparable, so use cost to understand why the planner chose a plan and actual time to see where the work really went.

Why do estimated and actual rows differ?

The planner works from sampled statistics, and ANALYZE uses random samples, so estimates are approximate. Gaps get large when statistics are stale, when data is skewed, or when correlated columns are treated as independent. A LIMIT can also make a node's actual rows smaller than the estimate without anything being wrong.

Should I add an index every time I see a Seq Scan?

No. A sequential scan is often the cheapest plan when a query needs a large share of a table, and small tables are usually scanned sequentially. Look at Rows Removed by Filter and the estimate quality first, and add an index only when the scan discards most of what it reads.

About the author

Alexandru Talnaci

Full-stack and systems engineer in Bucharest. Co-founder and technical lead at Legacies, working across web applications, integrations and C++ systems.

Explore the projects behind the notes