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)
| Part | Meaning |
|---|---|
cost=STARTUP..TOTAL | estimated work before the first row, and for all rows, in planner units (not milliseconds) |
rows (first) | estimated number of rows the node returns |
width | estimated average row size in bytes |
actual time | milliseconds to the first row and to the last row, per loop |
rows (actual) | rows returned, per loop |
loops | how 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:
| Node | What it does |
|---|---|
Seq Scan | reads the whole table |
Index Scan | finds rows through an index, then reads each row from the table |
Index Only Scan | answers from the index without visiting the table |
Bitmap Index Scan + Bitmap Heap Scan | collects matching row locations from an index, then reads them in table order |
Nested Loop | for each outer row, looks up matches in the inner input; good when the outer side is small |
Hash Join + Hash | builds a hash table from one input, probes it with the other |
Merge Join | joins two inputs already sorted on the join key |
Sort, Incremental Sort | sorts rows for ORDER BY, DISTINCT or a merge join |
HashAggregate, GroupAggregate | GROUP BY with a hash table, or over sorted input |
Limit | stops after N rows |
Gather | collects 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.