skip to content

How does PEV2 flag a row-estimate miss in a PostgreSQL plan, and how do you decide which flagged node actually matters?

level: seniorimportance: should knowfreq 25%

answer

  1. actual over planned, or the reverse
  2. a thumbs-down past ten times
  3. both sides are per loop
  4. lowest wrong node first
  5. Limit and never-executed artefacts

basics

~20 s

PEV2 divides actual by planned rows (or the reverse for overestimates) and badges nodes off by over 10x, 100x and 1000x. The miss that matters is the lowest one that drove a join or aggregation choice above it.

solid answer

~50 s

For each node, PEV2 compares `Actual Rows` with the planner's `Plan Rows`. If actual is larger, the node is **underestimated** by actual divided by planned. If smaller, it is **overestimated** by planned divided by actual. Both values are per loop, so they can be compared directly. Nodes off by more than 10x get a thumbs-down badge, stronger past 100x and 1000x. The Diagram's estimation metric and the Grid's estim column show the same factor. To decide which flag matters, start at the **lowest** flagged node, because estimates are built upward and one bad leaf skews every ancestor. Then check whether the node above chose its method from that wrong number: a Nested Loop, a hash sized too small, a sort expected to fit in memory. Ignore never-executed nodes, scans under a `LIMIT`, and tiny absolute counts. Why the estimate is wrong is a statistics question.

code

json · 10 lines
json
{
  "Node Type": "Seq Scan",
  "Relation Name": "invoices",
  "Alias": "i",
  "Plan Rows": 1200,
  "Actual Rows": 48000.00,
  "Actual Loops": 1,
  "Filter": "(issued_on >= '2026-09-01'::date)",
  "Rows Removed by Filter": 2000
}

go deeper

for a junior

Recall that PEV2 compares planned and actual rows and badges big gaps. A thumbs-down means the planner guessed wrong by more than ten times.

for a middle

Explain the factor's two directions, why per-loop values can be compared directly, and where the estimation metric and the estim column appear.

for a senior

Show triage: lowest flagged node first, link it to the decision above it, dismiss Limit and never-executed artefacts, then hand the cause to statistics work.

for a principal

Use recurring misestimate patterns across plan reviews to justify statistics policy, such as extended statistics on known correlated columns, instead of patching queries one at a time.

## How PEV2 measures a misestimate Every node in a PostgreSQL `EXPLAIN ANALYZE` plan carries two row counts. The planner's estimate is `rows=` in the cost parentheses (`Plan Rows` in JSON). The measured result is `rows=` in the actual parentheses (`Actual Rows`). PEV2 turns the pair into a **factor** and a **direction**: - if actual > planned, the node is **underestimated** and the factor is actual / planned; - if actual < planned, it is **overestimated** and the factor is planned / actual; - if they are equal, it is estimated correctly, with a factor of 1. The factor is never below 1, so under- and overestimates sit on one scale. Both inputs are **per-loop** values: the planner's `rows` is an estimate per execution, and `Actual Rows` is the average per execution. PEV2 can therefore compare them directly without multiplying by `loops`. The node's tooltip then gives the direction and factor, with actual and planned row totals across loops for context. ## Where it shows up | Surface | What you see | |---|---| | node badge | a thumbs-down ("Bad estimation for number of rows") above 10x, stronger above 100x and 1000x | | node tooltip | under or over, the factor, and actual against planned rows | | Diagram pane, *estimation* metric | a bar per node, scaled to the worst finite factor in the plan | | Grid tab | an *estim* column | The estimation metric is the quickest way to scan a large plan. Switch the Diagram to it, and the nodes with the longest bars are the candidates, whatever their depth in the tree. Then switch back to time, and check which of those candidates sits directly under a slow node. ## Deciding which flag matters A large plan can carry a dozen thumbs-down badges. Most are consequences, not causes. A workable order: 1. **Go to the lowest flagged node.** The planner builds estimates from the bottom up: a join's estimate is computed from its inputs' estimates, so one bad leaf makes every ancestor wrong too. Fix the first wrong number, and the ones above it often fix themselves. 2. **Check whether a decision depended on it.** A misestimate matters when the node above chose its method from that number. Examples: a Nested Loop that expected 1,200 outer rows and got 48,000; a hash sized for a few thousand rows that needed several batches; a sort expected to fit in memory that spilled. Combine the thumbs-down with the duration view. A strong estimate badge directly below a strong clock badge is the pattern to chase. 3. **Set aside the arithmetic artefacts** described below. ## False alarms - **Never-executed nodes.** A branch that never ran reports zero actual rows, so any ratio is meaningless, and arithmetically it is infinite. PEV2 greys the node and labels it "Never executed". Do not chase its badge. - **Nodes under a Limit.** The planner estimates the child's full output, but execution stops once the Limit has enough rows. A big *over*estimate on a scan under `LIMIT 20` is expected, not a statistics problem. - **Tiny absolute numbers.** Planned 1 and actual 15 is a 15x factor and earns a badge, but fifteen rows rarely change a plan choice. ## Worked example In the reporting query under review, the Seq Scan on `invoices` was planned at 1,200 rows and returned 48,000. That is underestimated by 40x, the lowest flagged node, with one badge. Above it, the Nested Loop probes `invoice_lines` once per outer row. PostgreSQL chose that join because 1,200 probes looked cheap. The 48,000 probes took about 384 ms and carry the strongest clock badge. The estimate badge on the leaf explains the clock badge one level up, so that is the flag that matters. A badly estimated node near the top with no slow node beneath it can wait. ## After PEV2 has found it PEV2's job ends once it has located the node. Why the estimate is wrong is a statistics question, and so is what to do about it. Typical causes are stale statistics after a bulk load, correlated columns, or a histogram too coarse for the predicate. Typical fixes are `ANALYZE`, a larger statistics target, or `CREATE STATISTICS`. Re-capture the plan after the fix and load it next to the old one. The badge should be gone, and the join choice above it may have changed.

  • A join node in PEV2 is badly estimated, but both of its inputs have a factor near 1. What does that tell you?
    The inputs were estimated well, so the error came from the join itself: the planner's guess at how many input pairs match the join condition. Columns that are correlated with each other, or skewed join keys, are the usual suspects. Diagnosing and fixing that is a statistics question rather than something PEV2 answers.
  • Can PEV2 compute an estimate factor from a plan captured without ANALYZE?
    No. Without `ANALYZE`, PostgreSQL never ran the statement, so there are no actual rows to compare with the planned ones. PEV2 shows the planner's rows and costs, but no thumbs-down badges and no estimation metric.

  • Every thumbs-down badge in PEV2 is a separate problem to fix
  • A big overestimate on a scan under LIMIT proves statistics are stale
  • PEV2 multiplies actual rows by loops before comparing with planned rows
  • The topmost badly estimated node is the place to start
  • A 15x factor on a node planned at 1 row always needs fixing