In PEV2, why can the PostgreSQL plan node with the largest actual time in raw EXPLAIN ANALYZE text differ from the node PEV2 marks as slowest?
answer
- inclusive versus exclusive
- times are per loop
- multiply, divide, subtract
- leader and workers share the clock
- CTE and InitPlan corrections
basics
~10 sEXPLAIN ANALYZE prints each node's time per loop, including its children. PEV2 multiplies by loops, divides by the parallel processes sharing the work, subtracts the children, and ranks nodes by that exclusive time instead.
solid answer
~50 sRaw times mislead in two ways. Each node's `actual time` is **inclusive**: it contains everything below it, so the root always looks slowest. It is also an **average per loop**, so an inner index scan showing `0.008` ms with `loops=48000` really cost about 384 ms. PEV2 computes an **exclusive duration** per node: total time times loops, divided by launched workers plus the leader for nodes under a Gather, minus the adjusted totals of its children, floored at zero. It also corrects CTE scans and nodes that consume an InitPlan, so a subplan's time is not counted twice. That figure, as a share of execution time, drives the clock badge and the duration highlight. The badge appears above roughly 10%, and gets stronger above 40% and 90%. In the example, the Nested Loop at the top shows 412 ms but owns only about 16 ms; the inner Index Scan owns about 93%.
code
console · 8 linesNested Loop (cost=0.43..18250.00 rows=1200 width=40) (actual time=0.050..412.300 rows=48000.00 loops=1)
-> Seq Scan on invoices i (cost=0.00..1250.00 rows=1200 width=16) (actual time=0.010..12.300 rows=48000.00 loops=1)
Filter: (issued_on >= '2026-09-01'::date)
Rows Removed by Filter: 2000
-> Index Scan using invoice_lines_invoice_id_idx on invoice_lines l (cost=0.43..14.16 rows=1 width=24) (actual time=0.007..0.008 rows=1.00 loops=48000)
Index Cond: (invoice_id = i.id)
Planning Time: 0.410 ms
Execution Time: 413.100 msgo deeper
Recall that EXPLAIN's actual time includes the children and is per loop. PEV2's slow badge uses the node's own time instead.
Walk through the formula: times loops, divided by workers plus one under a Gather, minus the children. Explain why a 0.008 ms node can dominate.
Use it to redirect a review: start from the strongest clock badge, then trace upward to the estimate or join method that caused it. Know where parallel figures are only approximate.
Coach reviewers to argue from exclusive time and loops rather than the top node's number, and to check per-worker detail before trusting parallel attributions in a capacity discussion.
## Two reasons raw times mislead In PostgreSQL's `EXPLAIN ANALYZE`, every node prints `actual time=startup..total`, `rows` and `loops`. Two properties of those numbers make the raw text hard to rank by eye: - **Inclusive time.** A node's total includes the time of every node beneath it, because a parent cannot produce a row until its children have produced theirs. The root therefore always carries the largest number, whether or not it does real work itself. - **Per-loop averages.** Some nodes run many times, such as the inner side of a Nested Loop or a correlated SubPlan. For these, PostgreSQL prints the average time and rows per execution, and the number of executions in `loops`. A tiny time with a large `loops` can be the most expensive line in the plan. The theory of reading those figures belongs to plan reading in general. What PEV2 adds is that it does the arithmetic for every node and shows the result. ## How PEV2 computes exclusive time PEV2's plan service works through every node like this: 1. **Revise the total**: multiply the actual total time by `loops`. 2. **Account for parallelism**: for nodes below a Gather or Gather Merge, divide by the number of launched workers plus one. The leader and the workers run the node at the same time, so adding up their loops would overstate elapsed time. 3. **Subtract the children**: take away each child's revised total. InitPlans are not subtracted from an ordinary parent, except when the parent is a Result node. 4. **Floor at zero**: rounding and the parallel approximation can push the result below zero, so PEV2 clamps it. 5. **Correct CTEs and InitPlans**: a CTE's own time is removed proportionally from the CTE Scan nodes that read it. An InitPlan's time is removed from the node whose expressions use its result. Either way, the same work is not counted twice. The result is stored as the node's exclusive duration, and its share of the plan's execution time drives the badges and the highlight. Cost gets the same treatment: a node's total cost minus its children's total costs. ## A worked example Take the fragment in the code example (buffer lines trimmed): | Node | Raw total `actual time` (ms) | loops | PEV2 exclusive | Share of 413.1 ms | |---|---|---|---|---| | Nested Loop | 412.300 | 1 | 412.3 - 12.3 - 384.0 = 16.0 ms | about 4% | | Seq Scan on invoices | 12.300 | 1 | 12.3 ms | about 3% | | Index Scan on invoice_lines | 0.008 | 48000 | 0.008 x 48000 = 384.0 ms | about 93% | Read from the top, the Nested Loop looks like the culprit and the index scan looks free. PEV2 shows the opposite. The index scan carries the strongest clock badge: each probe is cheap, but there were 48,000 of them. The review question becomes "why 48,000 probes?", which leads straight to the row estimate on the outer Seq Scan. ## Severity bands | Exclusive time as % of execution | PEV2 marking | |---|---| | 10% or less | no clock badge | | above 10% | clock badge, mild | | above 40% | clock badge, stronger | | above 90% | clock badge, strongest | The duration **highlight** is separate from the badge. It draws a bar on every node, scaled against the single slowest node and coloured from green to red. ## Where the arithmetic is approximate - **Parallel plans.** Dividing by workers plus one assumes the leader took part and the work was spread evenly. If `parallel_leader_participation` is off, or the workers are unbalanced, the figure is an estimate. `VERBOSE` adds per-worker lines that let you check. - **Rounded per-loop rows.** Before PostgreSQL 18, per-loop rows were printed as whole numbers, so rows times loops on a multi-loop node can be off. PEV2 marks such derived values as possibly inaccurate. - **Timing overhead.** Per-node timing reads the clock again and again, and on some platforms that inflates nodes that run very many times. `pg_test_timing` measures the cost. - **No timing at all.** With `TIMING FALSE` there are no per-node times, so PEV2 has nothing to subtract. ## Using it in a review Open the plan, switch the highlight to duration, and start from the node with the strongest clock badge, not from the top of the tree. Then ask why that node ran as often, or read as much, as it did. That is usually a question about the estimate or the join method above it.
- How does PEV2 avoid counting a CTE's time twice?PEV2 lifts each CTE out as its own subtree and draws it beside the main tree, linked to the CTE Scan nodes that read it. The CTE's own time is then subtracted from those CTE Scan nodes, in proportion to each scan's share. The CTE body keeps the work of producing rows, and each scan keeps only the cost of reading them.
- Why can a parallel node's exclusive time in PEV2 disagree with the per-worker lines?PEV2 divides time times loops by launched workers plus one, which assumes the leader took part and the work was evenly spread. With `VERBOSE`, PostgreSQL prints each worker's own actual time and rows, and those can show skew, such as one worker doing most of the work. The per-worker lines are then the better evidence.
Think of a timesheet where every manager's hours include their whole team's hours. To find who did the work, subtract each manager's direct reports. Then notice that a contractor who logged eight seconds on each of 48,000 tiny jobs outranks everyone.
- The top node of a plan is the slowest because its actual time is largest
- An inner index scan showing 0.008 ms cannot be the bottleneck
- PEV2's exclusive time is a node's total time minus its startup time
- PEV2 adds up parallel workers' times to show total CPU spent
- EXPLAIN ANALYZE's actual time for a node already excludes its children