Menu

PostgreSQL EXPLAIN ANALYZE: How to Read a Query Plan

EXPLAIN shows the plan PostgreSQL picks for a query, and EXPLAIN ANALYZE runs the query and adds the real row counts and times of every step. Learn to read the plan tree and the common node names.

This page includes runnable editors. Edit, run, and see output instantly.

EXPLAIN query shows the plan PostgreSQL chooses for a query: a tree of steps (nodes) with estimated costs and row counts. EXPLAIN ANALYZE query also runs the query and adds the actual time, rows and loops of every node. Read the tree from the most indented node outward: inner nodes feed their rows to the node above them.

There is no index on email, so the plan is a Seq Scan: PostgreSQL reads every row and keeps the ones that pass the Filter. Rows Removed by Filter shows the wasted work. Timings differ on every run; the shape of the plan matters more than the numbers.

Read one plan line

Each node prints a line in this shape:

Seq Scan on users  (cost=STARTUP..TOTAL rows=N width=W) (actual time=FIRST..LAST rows=N loops=L)
PartMeaning
cost=STARTUP..TOTALestimated work before the first row, and for all rows, in planner units (not milliseconds)
rows (first)estimated number of rows the node returns
widthestimated average row size in bytes
actual timemilliseconds to the first row and to the last row, per loop
rows (actual)rows returned, per loop
loopshow many times the node ran; multiply time and rows by it

The costs of a parent include its children. Planning Time and Execution Time at the bottom are totals for the whole statement.

Recognize the common plan nodes

EXPLAIN (COSTS OFF) hides the numbers, so the shape of the plan is easier to see:

Read from the inside. Both tables are scanned and the filtered customers go into a Hash. The Hash Join probes it with each order, and the GroupAggregate on top counts the result. The nodes you will meet most:

NodeWhat it does
Seq Scanreads the whole table
Index Scanfinds rows through an index, then reads each row from the table
Index Only Scananswers from the index without visiting the table
Bitmap Index Scan + Bitmap Heap Scancollects matching row locations from an index, then reads them in table order
Nested Loopfor each outer row, looks up matches in the inner input; good when the outer side is small
Hash Join + Hashbuilds a hash table from one input, probes it with the other
Merge Joinjoins two inputs already sorted on the join key
Sort, Incremental Sortsorts rows for ORDER BY, DISTINCT or a merge join
HashAggregate, GroupAggregateGROUP BY with a hash table, or over sorted input
Limitstops after N rows
Gathercollects rows from parallel workers

Find bad row estimates

The most useful check in EXPLAIN ANALYZE is estimated rows against actual rows on the same node. When they are far apart, the planner chose its plan from wrong numbers, often because statistics are missing or stale:

The scan returns 10 actual rows, but the estimate is far higher because the table has not been analyzed yet. Add ANALYZE tasks; before the EXPLAIN and the estimate moves close to 10. Autovacuum analyzes tables in the background, but run ANALYZE yourself after a bulk load (see VACUUM and autovacuum).

Explain a data change without keeping it

EXPLAIN ANALYZE executes the statement, so an UPDATE or DELETE changes rows. Wrap it in a transaction and roll back:

All 1000 rows are still there after the rollback.

Show I/O with BUFFERS and other options

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;        -- pages read from cache (hit) and disk (read)
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT ...; -- also list changed planner settings
EXPLAIN (FORMAT JSON) SELECT ...;              -- machine-readable, for plan visualizers
EXPLAIN (ANALYZE, TIMING OFF) SELECT ...;      -- row counts without per-node timing overhead

BUFFERS shows I/O: a node with many read pages went to disk. PostgreSQL 17 added SERIALIZE (the cost of converting the output rows to text) and MEMORY (planner memory). The official EXPLAIN guide walks through more plans.

Test a plan with planner settings

To see the plan PostgreSQL would use without a method, turn that method off for your session. Do this in experiments only, never in production code:

With 100 rows the planner prefers a Seq Scan; with enable_seqscan = off the plan shows Index Scan using items_pkey. Compare the two EXPLAIN ANALYZE timings to see whether the index helps. RESET enable_seqscan; restores the default.

Frequently Asked Questions

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN only plans the query and shows estimates. EXPLAIN ANALYZE also executes it and adds the actual time, row count and loop count of each node, so it takes as long as the query itself.

What does cost mean in a PostgreSQL plan?

Cost is the planner's estimate in arbitrary units, scaled so that reading one page sequentially costs 1 (seq_page_cost). It is useful for comparing plans of the same query, not as a time in milliseconds.

Does EXPLAIN ANALYZE change data?

Yes, for INSERT, UPDATE, DELETE and MERGE it performs the change. Run it between BEGIN and ROLLBACK to measure without keeping the result.

Why is a Seq Scan chosen when an index exists?

Because the planner estimates it is cheaper: the table is small, the query returns a large fraction of the rows, or the condition cannot use the index. Stale statistics can also mislead it, which ANALYZE fixes.

Coddy programming languages illustration

Learn to code with Coddy

Get started