skip to content

In PostgreSQL EXPLAIN ANALYZE, what do a node's cost, rows, width, actual time and loops mean, and how do you get its total time?

level: middleimportance: must knowfreq 74%

answer

  1. two parentheses: plan versus run
  2. startup..total in planner units
  3. per-execution averages
  4. multiply by loops, subtract children

basics

~20 s

The first group is the planner's estimate: startup..total cost in arbitrary units, rows per execution and average row width in bytes. The second is measured: per-execution average milliseconds and rows, plus loops; multiply by loops for totals.

solid answer

~50 s

Each node prints `(cost=S..T rows=R width=W)`: estimated startup and total cost in planner units scaled so `seq_page_cost` is `1.0`, not milliseconds, plus estimated rows per execution and average row width in bytes. `EXPLAIN ANALYZE` adds `(actual time=S..T rows=R loops=N)`: real milliseconds to the first and to the last row and the rows returned, both averaged per execution, with `loops` the number of executions. So an inner index scan showing `actual time=0.180..0.214 rows=3.00 loops=1460` ran for about 0.214 × 1460 ≈ 312 ms and returned 4,380 rows. Times include child nodes, so subtract the children to find a node's own time. PostgreSQL 18 prints actual rows with two decimals, so `rows=0.50` means half a row per execution on average, such as one row in every second loop. Under a `Gather`, `loops` counts processes, usually the workers plus the leader.

code

console · 13 lines
console
katajob=> EXPLAIN ANALYZE SELECT w.started_at, l.lap_no, l.avg_hr FROM workouts w JOIN workout_laps l ON l.workout_id = w.id WHERE w.club_id = 77 AND w.started_at >= '2026-09-01';
 Nested Loop  (cost=0.85..2210.40 rows=180 width=16) (actual time=0.061..318.902 rows=4380.00 loops=1)
   Buffers: shared hit=11641 read=1472
   ->  Index Scan using workouts_club_started_idx on workouts w  (cost=0.43..96.10 rows=60 width=16) (actual time=0.030..1.214 rows=1460.00 loops=1)
         Index Cond: ((club_id = 77) AND (started_at >= '2026-09-01 00:00:00+00'::timestamp with time zone))
         Index Searches: 1
         Buffers: shared hit=1402 read=31
   ->  Index Scan using workout_laps_workout_id_idx on workout_laps l  (cost=0.42..35.20 rows=3 width=16) (actual time=0.180..0.214 rows=3.00 loops=1460)
         Index Cond: (workout_id = w.id)
         Index Searches: 1460
         Buffers: shared hit=10239 read=1441
 Planning Time: 0.402 ms
 Execution Time: 319.880 ms

go deeper

for a junior

Recall which parenthesised group is the estimate and which is measured, and that cost is in planner units, not milliseconds.

for a middle

Explain per-loop averaging and do the arithmetic aloud: upper actual time times loops, rows times loops, then subtract the children to get a node's own time.

for a senior

Walk a real plan to the node that owns the time, separate averaged from summed counters, and read Gather lines, including fewer workers launched than planned.

for a principal

Give the team one repeatable plan-reading routine, so performance reviews argue from per-node totals rather than the top-line Execution Time.

## Two groups on every node line Every node in PostgreSQL's text-format `EXPLAIN ANALYZE` output carries two parenthesised groups. The first is the **planner's estimate**, which plain `EXPLAIN` prints too; the second is **measured** while the statement ran: | Field | Group | Meaning | Unit | |---|---|---|---| | `cost=S..T` | estimate | Startup cost before the first row; total cost to return all rows | Arbitrary planner units | | `rows` | estimate | Rows the node is expected to emit per execution | Rows | | `width` | estimate | Average size of one output row | Bytes | | `actual time=S..T` | measured | Time to the first row and to the last row, averaged per execution | Milliseconds | | `rows` | measured | Rows emitted, averaged per execution | Rows | | `loops` | measured | How many times the node was executed | Count | ## Cost is not time Cost units are relative. The cost constants are scaled so that `seq_page_cost` is `1.0`; by default `random_page_cost` is `4.0`, `cpu_tuple_cost` `0.01` and `cpu_operator_cost` `0.0025`. A total cost of 96 does not mean 96 ms or 96 pages, and the manual warns that cost and actual time are unlikely to line up. Cost is for comparing alternative plans of the same query. The **startup** figure matters when only the first rows are needed, as under `LIMIT` or inside `EXISTS`. A parent's cost includes its children's. ## Actual time, rows and loops The measured group is where the interview questions live, because PostgreSQL prints **per-execution averages**: - `actual time=0.180..0.214` means the node produced its first row after 0.180 ms and its last after 0.214 ms, on average per execution. - `rows=3.00` is the average number of rows per execution, so the node's total output is `rows × loops`. - `loops=1460` says the node was started 1,460 times, typically once per outer row of a `Nested Loop`. The averaging exists so the measured numbers line up with the per-execution estimates beside them. From **PostgreSQL 18**, actual rows print with two decimals. Earlier releases rounded to whole rows, so a node that found one row in every third loop showed `rows=0` and hid the work; now it shows `rows=0.33`. ## Finding where the time went Node times are **inclusive**: a node's actual time contains everything its children did. To find the expensive node in a fitness app's slow dashboard query: 1. For each node, compute its total time as the upper `actual time` × `loops`. 2. Subtract the totals of its direct children to get the node's own time. 3. Compare estimated and actual `rows` at each level; a large gap marks where the plan choice went wrong. Why it went wrong is a statistics question, not a reading one. 4. Read the lines under the node, such as `Buffers` and `Rows Removed by Filter`, to see what the time was spent on. In the example plan, the inner `Index Scan` on `workout_laps` accounts for about 0.214 ms × 1460 ≈ 312 ms of the 319 ms total. The outer scan estimated 60 workouts and found 1,460, which is very likely why the planner chose a nested loop at all. ## Counters that are totals, not averages Not every line under a node is averaged, and mixing them up gives wrong arithmetic: - `Rows Removed by Filter` and `Rows Removed by Join Filter` are per-execution averages, like `rows`. - `Buffers` counts are totals over all executions, and include the children's. - `Heap Fetches` on an `Index Only Scan` is a total across all loops. - `Index Searches`, new in 18, is the total number of index descents across all loops. ## Parallel plans Under a `Gather` or `Gather Merge` node, `Workers Planned` is what the planner asked for and `Workers Launched`, shown only with `ANALYZE`, is what it got. The parallel child's `loops` counts the **processes** that ran it, usually the launched workers plus the leader, and its `rows` is the per-process average. `VERBOSE` adds a `Worker N:` line per worker with its own time and rows. If fewer workers launched than were planned, it is usually because no background worker slots were free, and the leader and any launched workers carried the load. ## Readings that look wrong but are not - Under `LIMIT`, a child's estimated cost and rows are shown as if it ran to completion, while its actual rows stop at what the limit needed. That is a display difference, not a misestimate. - A `Merge Join` can stop reading one input early, and rescans of its inner side are counted as extra rows. - `BitmapAnd` and `BitmapOr` nodes always report zero actual rows.

  • Why does an Index Scan under a LIMIT show far fewer actual rows than estimated without any misestimate?
    PostgreSQL prints a child's estimated cost and rows as if it ran to completion, but the `Limit` node stops asking for rows once it has enough. The child's actual rows and time therefore stop early. The manual calls this a display discrepancy, not an estimation error, so it should not send you hunting for bad statistics.
  • A Parallel Seq Scan under a Gather with Workers Launched: 2 shows rows=120000.00 loops=3; how many rows did it return?
    About 360,000. Under a `Gather`, the parallel node's `loops` counts the processes that ran it, here two workers and the participating leader, and `rows` is the average per process. The `Gather` node's own actual rows shows the combined total directly, and `VERBOSE` breaks the figures down per worker.
  • What does Workers Planned: 2 with Workers Launched: 0 tell you?
    The planner chose a parallel plan, but when the `Gather` started no background worker slots were free, usually because `max_worker_processes` or `max_parallel_workers` were already used by other work. The leader ran the whole parallel plan alone, often slower than a serial plan would have been. How to size those limits is a planner-settings question.

A fitness watch shows a run's average lap time and the number of laps; the total time is the average multiplied by the laps, not the average alone. EXPLAIN ANALYZE prints each node's average per execution and its loops in the same way.

  • The cost numbers are the planner's prediction in milliseconds.
  • Actual time on a node with loops=1460 is that node's total time.
  • A parent node's actual time excludes the time spent in its children.
  • Buffers and Heap Fetches on a looped node are per-loop averages, like rows.
  • In PostgreSQL 18, actual rows=0.50 means the row count was estimated, not measured.

Assumes PostgreSQL 18; from 18 actual rows print with two decimals, while 17 and earlier round them to whole numbers.