PostgreSQL EXPLAIN guide

PostgreSQL Row Estimate Mismatch

PostgreSQL chooses a plan before executing the query, using estimated row counts. When those estimates differ sharply from reality, the optimizer may choose algorithms that fit the estimate but not the actual workload.

Example EXPLAIN output

Nested Loop
  Plan Rows: 14
  Actual Rows: 28,492
  Actual Loops: 1

What PostgreSQL is telling you

Plan Rows is the optimizer's expected cardinality.

Actual Rows is what execution produced.

Large ratios reveal where the planner's mental model diverged from reality.

What it can mean

Underestimation can make nested loops look cheaper than they become.

Overestimation can discourage otherwise efficient access paths.

The cause can involve stale statistics, skew, correlated columns, or expressions.

What to check next

Run ANALYZE if statistics may be stale.

Inspect data skew.

Consider extended statistics for correlated columns.

Check expressions and casts in predicates.

Inspect the first node where estimates diverge materially.

When this is not automatically a problem

  • • Small absolute row counts can have huge ratios but little cost.
  • • Parameter-sensitive queries may naturally vary.
  • • A mismatch is a clue, not proof of the root cause.

Check your actual plan

PlanCheck analyzes TEXT or JSON locally in your browser. Your SQL and plans are not uploaded.

Analyze EXPLAIN output