Reading EXPLAIN ANALYZE Output in PostgreSQL Step by Step

A query is slow. Someone says "look at the plan", and a wall of indented text appears: Hash Join, Index Scan, cost=0.43..8.45, rows=1, loops=1000. The text is dense because it carries a lot of information, and nearly all of it falls into a small number of patterns once you know where to look. Reading plans is the skill that separates guessing at indexes from fixing a query on purpose. This guide builds the reading method from a sample plan, then explains the nodes, the numbers and the common failures. It is the hub for the series on plans: reading a plan step by step, sequential, index and bitmap scans, nested loop, hash and merge joins, why the planner estimates the wrong number of rows, reading BUFFERS output, sorts that spill to disk, auto_explain, parallel plans, comparing plans before and after a change and visualizing plans.

EXPLAIN, ANALYZE and what each does

EXPLAIN alone asks the planner for its chosen plan and prints it, without running the query:

EXPLAIN SELECT * FROM app_order WHERE customer_id = 42;

The numbers in that output are estimates. EXPLAIN ANALYZE executes the statement for real and adds measured values beside the estimates:

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

Because it executes, EXPLAIN ANALYZE on an INSERT, UPDATE or DELETE performs the change. Wrap those in a transaction and roll back:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE app_order SET status = 'paid' WHERE id = 1001;
ROLLBACK;

Useful options: BUFFERS (block counts, recommended every time), VERBOSE (output columns and schema names), SETTINGS (non-default parameters that affected the plan), WAL (write-ahead log volume for writes), TIMING OFF (cheaper on systems where the clock call is slow) and FORMAT JSON (for tools). From PostgreSQL 18, BUFFERS is on by default with ANALYZE. Measuring adds overhead from timing every node, so absolute times in an analyzed run are slightly higher than in normal execution; compare relative sizes.

A first plan, read from the inside out

Here is a plan for a query that joins orders to customers:

Sort  (cost=1890.12..1890.62 rows=200 width=44) (actual time=24.5..24.6 rows=187 loops=1)
  Sort Key: (sum(o.total)) DESC
  Sort Method: quicksort  Memory: 40kB
  Buffers: shared hit=1450 read=210
  ->  HashAggregate  (cost=1880.00..1882.00 rows=200 width=44) (actual time=24.3..24.4 rows=187 loops=1)
        Group Key: c.name
        Buffers: shared hit=1450 read=210
        ->  Hash Join  (cost=35.50..1830.00 rows=10000 width=40) (actual time=0.9..19.8 rows=9650 loops=1)
              Hash Cond: (o.customer_id = c.id)
              Buffers: shared hit=1450 read=210
              ->  Seq Scan on app_order o  (cost=0.00..1500.00 rows=10000 width=12) (actual time=0.02..11.5 rows=9650 loops=1)
                    Filter: (placed_at >= '2026-03-01'::timestamptz)
                    Rows Removed by Filter: 90350
                    Buffers: shared hit=1250 read=210
              ->  Hash  (cost=20.00..20.00 rows=1000 width=36) (actual time=0.8..0.8 rows=1000 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 70kB
                    ->  Seq Scan on customer c  (cost=0.00..20.00 rows=1000 width=36) (actual time=0.01..0.4 rows=1000 loops=1)
Planning Time: 0.4 ms
Execution Time: 24.9 ms

The indentation is the tree. Each -> is a child of the node above it. Execution flows from the leaves up: the two Seq Scan nodes at the bottom read tables, the Hash node builds a hash table from the customers, the Hash Join probes it with order rows, HashAggregate groups, Sort orders the result. To read it, start at the deepest-indented nodes and ask of each: what did it do, how many rows did it produce, how long did it take and how much did the planner expect?

Each node has two parenthesized groups. The first is the planner's estimate: cost=startup..total, rows (estimated output rows) and width (average row size in bytes). The second, present with ANALYZE, is the measurement: actual time=first..last in milliseconds, rows (actual output rows) and loops (how many times the node ran).

The numbers, one by one

cost. An abstract unit where one sequential page fetch costs 1.0 by default (seq_page_cost) and a random page costs 4.0 (random_page_cost). Startup cost is the work before the first row; total cost is the work for all rows. A parent's cost includes its children. Costs are only comparable inside one query's plan; they are not milliseconds.

rows (estimated). The planner's prediction of how many rows the node outputs. It is built from table statistics. The estimate drives every decision: join order, join method, scan type.

actual time. Two numbers: the time to return the first row and the time to return all rows, averaged per loop and including the time of the child nodes. Subtract the children's time to find a node's own cost.

actual rows. The measured output of the node, per loop.

loops. How many times the node executed. A node on the inner side of a nested loop runs once per outer row. For such a node, actual time and rows are per execution, so the total time is actual time × loops and the total rows rows × loops. This is the single most missed detail: an index scan showing actual time=0.02..0.03 loops=50000 costs about a second in total.

Rows Removed by Filter. The rows the node read and then threw away. A large number next to a small output means an index could have avoided the work.

Buffers. shared hit is blocks found in PostgreSQL's cache, read is blocks fetched from the operating system (which may be in its cache or on disk), dirtied and written are blocks the node modified or flushed, and temp read/written is spill to temporary files. shared hit=1450 read=210 says that most pages were cached and a few came from outside. Reading BUFFERS output in EXPLAIN goes deeper.

The node types you will meet

Scans read tables: Seq Scan reads every page; Index Scan walks an index and fetches matching rows from the heap; Index Only Scan answers from the index when the visibility map allows; Bitmap Index Scan followed by Bitmap Heap Scan collects row locations from one or more indexes, sorts them by page and reads the heap in order. Sequential, index and bitmap scans explains how the planner picks.

Joins combine inputs: Nested Loop (for each outer row, probe the inner), Hash Join (build a hash table on one side, probe from the other), Merge Join (walk two sorted inputs together). See nested loop, hash join and merge join compared.

Aggregates group: HashAggregate uses a hash table, GroupAggregate needs sorted input, Aggregate computes a single result, WindowAgg computes window functions.

Sorts and limits: Sort orders rows (memory, top-N heapsort for a LIMIT, or external merge on disk), Incremental Sort sorts within already ordered prefixes, Limit stops early, Unique removes duplicates from sorted input.

Parallel nodes: Gather and Gather Merge collect rows from worker processes, Parallel Seq Scan splits a table among workers. See making sense of parallel query plans.

Others: Materialize caches a subplan's result, Memoize caches inner lookups by parameter, CTE Scan, Subquery Scan, Append for unions and partitions, ModifyTable for writes, Result for constant expressions.

Estimated versus actual: the first thing to check

In the example, the Seq Scan on app_order estimated 10,000 rows and produced 9,650, a good estimate. The decisions that follow are sound. Now consider a different plan:

Nested Loop  (cost=0.85..120.40 rows=5 width=48) (actual time=0.05..1850.2 rows=120000 loops=1)
  ->  Index Scan using customer_country_idx on customer c  (cost=0.42..8.44 rows=1 width=36) (actual time=0.02..12.5 rows=4000 loops=1)
        Index Cond: (country = 'FR')
  ->  Index Scan using app_order_customer_idx on app_order o  (cost=0.43..95.0 rows=5 width=12) (actual time=0.01..0.4 rows=30 loops=4000)
        Index Cond: (customer_id = c.id)

The planner expected one French customer and five orders; reality was 4,000 customers and 120,000 rows. With an expected single outer row, a nested loop is the cheapest plan, and with 4,000 outer rows it is slow. The estimate was wrong by three orders of magnitude, and everything downstream follows from it. The fix is not to rewrite the join. It is to find out why the planner believed there was one French customer: stale statistics (ANALYZE), a skewed distribution that needs more detail (ALTER TABLE ... SET STATISTICS), or two correlated columns that need extended statistics. Why the planner estimates the wrong number of rows covers each cause.

A good habit: before looking at anything else, scan the plan for nodes where rows= differs from the actual rows by a factor of ten or more. The first such node, from the bottom, is usually where the trouble starts.

A method for finding the slow step

1. **Run EXPLAIN (ANALYZE, BUFFERS) on realistic data.** A plan on a ten-row test table is not the plan production gets.

2. Read the last lines first. Execution Time says how slow the whole statement is, and Planning Time says whether planning is a factor (it matters for tiny queries run thousands of times).

3. Find the node with the largest actual time that is not explained by its children. Compute each node's own time as its actual time times loops minus the child's. The jump is where the work happens.

4. Check that node's estimate against its actual rows. A mismatch redirects you to statistics. A good match means the planner chose well given the available paths, and the fix is a better path: an index, a rewrite, a different join order.

5. Read the node's extras. Rows Removed by Filter, Heap Fetches, Sort Method, Batches in a hash, Buffers: temp.

6. Change one thing, rerun and compare. An index, a statistic, a rewrite, never three at once. Comparing plans before and after a change shows how.

This method treats the plan as evidence, not as a verdict.

Common patterns and what they mean

A Seq Scan on a big table with a selective filter and a high Rows Removed by Filter means no usable index for that condition, or the planner thinks the filter is not selective. Check whether an index exists on the filtered columns, whether the filter wraps the column in a function and whether statistics are current.

An Index Scan with a huge loops count means a nested loop driven by many outer rows. Each lookup is cheap and the total is not. Fix the outer estimate, or make the planner choose a hash join by correcting the estimate that made the nested loop look cheap.

**A Sort with Sort Method: external merge Disk: 80000kB** spilled to disk because work_mem was too small for it. Raise work_mem for the session after checking concurrency, add an index that supplies the order, or reduce the rows sorted. See spotting sorts that spill to disk.

**A Hash with Batches: 8** means the hash table did not fit in work_mem and was split into batches written to temp files. The same remedy applies.

**An Index Only Scan with large Heap Fetches** means the visibility map is not up to date, so the scan keeps visiting the heap; VACUUM fixes it.

**A CTE Scan with a big Rows Removed by Filter** shows a materialized CTE being filtered after the fact; see common table expressions and when they help readability.

Planning Time larger than Execution Time suggests a many-way join or a heavily partitioned table, where planning dominates for a short query. Prepared statements and a pooler change the trade-off.

**A big gap between the top node's time and the total Execution Time** is trigger time, deferred constraints or ModifyTable work, listed after the plan as Trigger ... time.

Reading plans for writes

INSERT, UPDATE and DELETE show a ModifyTable node, with the read plan beneath it. After the plan, EXPLAIN ANALYZE prints time spent in triggers and foreign key checks:

Trigger for constraint order_item_order_id_fkey: time=185.3 calls=50000

A write that is slow because of a missing index on a referencing column shows up there. The WAL option reports records, full-page images and bytes written, which explains write-heavy statements. Always roll back after analyzing a write.

Tools around the text

Plain text works and large plans are hard to scan. FORMAT JSON output feeds visualizers that highlight the slowest nodes and the worst estimate errors; visualizing plans with explain.depesz.com and others compares them and warns about what pasting a plan into a public site reveals. For plans you cannot capture by hand, such as queries that run inside the application, auto_explain logs the plan of slow statements automatically, as in using auto_explain to catch slow queries. pg_stat_statements finds which statements deserve a plan in the first place, and the extension is covered in pg_stat_statements for finding slow queries.

What the planner knows and does not

The planner chooses among plans using statistics gathered by ANALYZE: row counts, the fraction of nulls, the number of distinct values, the most common values with frequencies, a histogram of the rest and the correlation between physical order and value order. From these it estimates how many rows each condition keeps. It assumes columns are independent unless you create extended statistics, it knows nothing about values it has not sampled, and it cannot estimate the effect of arbitrary functions or expressions without an expression statistic. It also uses cost settings (random_page_cost, cpu_tuple_cost, effective_cache_size) that describe your hardware only if you set them to, so default random_page_cost = 4 on an SSD makes index scans look more expensive than they are, as covered in tuning PostgreSQL for SSDs with random_page_cost.

Most plan problems fall into three families. The planner's information is wrong (statistics). The planner's costs are wrong (settings). The planner has no good path (missing index, unfortunate query shape). The method above sorts a given plan into one of the three.

The same query after the fix

Seeing a plan improve teaches as much as seeing one fail. Take the earlier nested loop that probed orders for 100,000 customers. After an index on app_order (placed_at) INCLUDE (customer_id, total), the plan might become:

HashAggregate  (cost=9100.0..9400.0 rows=42000 width=48) (actual time=70.1..78.0 rows=42000 loops=1)
  Group Key: c.name
  ->  Hash Join  (cost=2600.0..8000.0 rows=410000 width=20) (actual time=22.0..52.0 rows=410000 loops=1)
        Hash Cond: (o.customer_id = c.id)
        ->  Index Only Scan using app_order_placed_idx on app_order o  (actual time=0.03..14.0 rows=410000 loops=1)
              Index Cond: ((placed_at >= '2026-03-01') AND (placed_at < '2026-04-01'))
              Heap Fetches: 0
        ->  Hash  (actual time=21.0..21.0 rows=100000 loops=1)
              ->  Seq Scan on customer c  (actual time=0.01..9.0 rows=100000 loops=1)

Compare it with the failing plan point by point. The scan that matters is now an Index Only Scan that reads the 410,000 March rows once, with Heap Fetches: 0 showing the visibility map is current. The join is a Hash Join with loops=1 everywhere, so there is no multiplication. The estimate for the join, 410,000 rows, matches the actual. The total time fell from seconds to roughly 80 ms. Each change in the plan has a cause you can name: a new index gave the planner a cheap way to start from the date range, and once it had that path, the cost of the nested loop looked worse than the hash join, so it switched.

Two lessons recur. The fix was an access path, and the join method followed by itself. And the proof is in the plan, not in the feeling that the page loads faster: the block counts and the loops tell you why.

Settings that shape plans

A few parameters change which plan the planner picks, and they belong in your mental model when you read one.

SETTINGS in EXPLAIN lists those that differ from the default in the session. Defaults are conservative and tuned for modest hardware. On an SSD server with plenty of memory, random_page_cost near 1.1 and a realistic effective_cache_size often change plans for the better. Change settings with measurements, in a session first, and keep the plan comparison, as in tuning PostgreSQL settings from real numbers.

JIT deserves a mention because it appears in plans as a JIT: section with timing for generation, inlining, optimization and emission. For a query that runs in 50 ms, spending 30 ms compiling is a loss; JIT can add noticeable overhead to medium queries with a high cost estimate. If you see a JIT section whose time is a large share of the total, test with SET jit = off.

A checklist for any plan

When you open a plan, run through it in this order and write down one line for each:

1. How long is the whole statement, and how long was planning?

2. Which node has the largest own time, after multiplying by loops?

3. Does that node's estimate match its actual rows?

4. What does its filter or condition line say about wasted work?

5. Does anything spill: Sort Method with Disk, Batches above 1, temp buffers?

6. Are there nodes with a large loops count, and what is their per-loop cost?

7. Is the join method sensible for the row counts on both sides?

8. Are buffers dominated by read, by hit or by temp?

9. Is there a JIT section, a trigger time line or a Workers Launched shortfall?

10. What is the single change that would remove the most work?

The answers usually fit in ten lines, and the tenth line is the hypothesis to test. Having a fixed list prevents the common failure of staring at the plan and fixating on whatever looks unusual, which is often harmless.

Reading plans for different SQL shapes

Plans for common query shapes have recognizable forms. A primary key lookup is an Index Scan with one row and a handful of buffers. A range report is an index or bitmap scan feeding an aggregate. A top-N query is an index scan feeding a Limit, with no sort if the index matches the order; seeing a Sort above a big scan under a Limit means the index does not match the ORDER BY. A DISTINCT ON appears as a Unique over a sort or an ordered index scan. A window function appears as a WindowAgg over a sort. An EXISTS subquery appears as a Semi Join, and NOT EXISTS as an Anti Join, both normally hash or merge joins. A recursive CTE appears as a Recursive Union with a WorkTable Scan. Learning these forms lets you see at a glance when the plan is of the expected shape, and when it has quietly turned into something else.

Myths that mislead plan readers

A few beliefs about plans cause repeated wrong turns, and naming them helps avoid them.

"A sequential scan means a missing index." It means the planner judged a scan cheaper. For a query that returns a large share of a table, or a small table, it is correct. The check is the filter: a scan that discards 99.9 percent of what it reads is the problem, and one that keeps half is not.

"Higher cost means slower." Cost is a prediction in abstract units. Two plans with equal cost can differ ten times in time if the estimates behind one are wrong. Use actual time from ANALYZE for decisions, and use cost only to see why the planner preferred one path.

"The top node's time is the problem." The top node includes everything beneath it. The problem is the node with the largest own time, or the first node with a bad estimate.

"Adding an index always helps." An index that the planner cannot use for the query changes nothing, an index on a low-selectivity column may not be chosen, and every index slows writes and takes space. Confirm with a plan, then keep only the indexes that earn their cost, as finding unused indexes safely describes.

"The plan is the same, so the performance is the same." Two runs with the same plan can differ by orders of magnitude because of cache state, concurrent load or parameter values. The plan is the shape, and the execution is the weather.

"EXPLAIN ANALYZE shows production behavior." It shows one execution, with timing overhead, on the data and settings of the session that ran it. Treat it as a sample.

Dropping these beliefs makes plan reading shorter: you look at own time, estimate errors, loops and spills, and you stop chasing scan types for their own sake.

Keeping the skill sharp

Plan reading improves with repetition on your own system. Pick a handful of queries that matter, such as the home page query, the search, the heaviest report and the checkout write, and keep their analyzed plans in the repository next to the code with the date and data size. When a deployment changes a query, update the plan file. When performance drifts, diff the current plan against the stored one. Over a few months you build a reference of what normal looks like, which is what makes an abnormal plan obvious in seconds. Teams that do this find problems earlier, because the diff names the change: a new join order, a lost index scan, a spill that did not exist. Share the habit by reviewing plans in pull requests that touch queries, the same way you review the SQL itself.

Safe practice in production

Running EXPLAIN ANALYZE on a production system executes the query, with all its load and locks. Take care with long queries, writes and statements that take locks. Use a read replica for heavy analysis, set statement_timeout for the session, and avoid analyzing a statement you would not be willing to run. Plain EXPLAIN is safe and instant. For parameterized queries, the plan for a prepared statement can differ from the plan for the same text with literals, because generic plans do not see the values; use EXPLAIN (GENERIC_PLAN) (version 16 and later) to see the generic plan for a statement with $1 placeholders, or PREPARE and EXPLAIN EXECUTE to see what the application gets.

When the plan is not where the problem lives

Plans explain how the database executed a query. Some slow-query problems sit elsewhere. If the plan is fast and the application is slow, the cost is in the network, in row transfer (selecting too many columns or rows), in the driver's handling or in the number of round trips: a thousand fast queries are slow. If execution time varies wildly between runs of the same plan, suspect locks (waits do not appear in the plan), cache state or contention with other load, and check pg_stat_activity and pg_locks, as in finding who is blocking a query. If planning is slow, look at the number of tables and partitions. If the plan changes at random, suspect parameter sniffing in prepared statements, statistics sampling noise or a threshold between two nearly equal costs. And if everything is fast alone and slow together, the problem is capacity: connections, I/O or memory, which are the topics of tuning PostgreSQL settings from real numbers and connection pooling for PostgreSQL.

Where to go next

Start with how to read a PostgreSQL query plan step by step, which walks one plan line by line, then why the planner estimates the wrong number of rows. The manual's pages on using EXPLAIN and on planner statistics are the primary sources, and the EXPLAIN command reference lists every option. For a quick look at a JSON plan or sample payload, the JSON formatter helps.