ES002: Row misestimate

• Signal: actual and estimated rows per loop differ by 10× or more, and the error starts at this node rather than being passed up from a child. Each count is taken as at least one row.

• Evidence: estimated and actual rows, loops, and the join that consumes the node.

• Action: depends on the cause:

  • stale statistics: ANALYZE the table;
  • correlated columns in the condition: CREATE STATISTICS on them;
  • a single column: a higher statistics target;
  • a cast or a function of a column, which has no statistics: rewrite the condition, or CREATE STATISTICS on the expression (PostgreSQL 14 and later);
  • a comparison with a value known only at run time ($1, an InitPlan’s result): a default guess that ANALYZE cannot change.

• Stays silent when:

  • both counts are small: under 100 rows per loop, and under 10,000 over all loops;
  • the node returned fewer rows than estimated but a node above may have stopped it early;
  • the node passes its input’s rows through (Sort, Hash, Materialize, Memoize, Gather) or reports none (bitmap nodes);
  • the node is a recursive CTE’s Recursive Union, whose depth the planner cannot know.

  A CTE Scan inherits the error of the CTE it reads.

Example

The misestimate_stale_stats scenario of the test corpus, captured on PostgreSQL 16: statistics predate 50,000 inserted ‘active’ rows, so the estimate is off by orders of magnitude.

SELECT count(*) FROM shipments WHERE state = 'active';

Aggregate  (cost=1743.00..1743.01 rows=1 width=8) (actual time=9.397..9.399 rows=1 loops=1)
  Output: count(*)
  Buffers: shared hit=589
  ->  Seq Scan on public.shipments  (cost=0.00..1743.00 rows=1 width=0) (actual time=2.647..7.317 rows=50000 loops=1)
        Output: id, state, weight
        Filter: (shipments.state = 'active'::text)
        Rows Removed by Filter: 50000
        Buffers: shared hit=589
Settings: max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=63
Planning Time: 0.260 ms
Execution Time: 9.452 ms

explainsql reports:

LOW  ES002 Row misestimate
Seq Scan on shipments returned 50,000 rows where the planner expected 1: 50,000× more.
Estimated rows: 1
Actual rows: 50,000
→ Run ANALYZE shipments: its statistics may be out of date. If the estimate stays off, raise the statistics target of state (ALTER TABLE shipments ALTER COLUMN … SET STATISTICS).

All rules