Estimated rows

Every node carries two row counts. rows=612 in the first parenthesis is the planner's estimate, made from pg_stats before the query ran. actual rows=206880 in the second is what happened. Every decision in the plan was based on the first. The sample's Seq Scan on commerce.orders guessed 612 and found 206,880, 338× more, and the Nested Loops above it were chosen for 612.

Why it matters

A wrong estimate is behind most bad plans: a plan costed for 612 rows and run on 206,880 does 338× the lookups it budgeted for. Find where it starts. The join above the sample's scan expects 1,836 and produces 620,640, the same 338×, because it inherited the error. The source is the scan. orders was last analysed 41 days ago, before most of the last 30 days of rows existed.

What to do

1. ANALYZE commerce.orders. The estimate corrects and the planner switches to Hash Joins. Autovacuum analyses a table after 10% of it changes, 1,200,000 rows on orders, so lower autovacuum_analyze_scale_factor on a table that keeps going stale.
2. If the estimate is still off, the statistics are too coarse. default_statistics_target is 100 buckets per column. Raise it for the column that matters:

ALTER TABLE commerce.orders ALTER COLUMN placed_at SET STATISTICS 1000;
ANALYZE commerce.orders;

3. If two filtered columns move together, CREATE STATISTICS on the pair tells the planner. Reading the stats covers pg_stats.

In the Plan tab

An estimate-only plan labels every band ≈N. An analyzed plan draws the actual band and, when the estimate is off by more than 2×, a dashed ghost band beside it at the planner's width. Past 10× the label adds the planner's figure, ┅ 612. Both need the header's Estimates toggle, which is on by default. Marker 2 is the ghost, and marker 4 is the same error one node up.

The row flow of the sample, with the ghost band beside the output of Seq Scan on orders.

The finding reads Estimate off 338×, more rows than planned · 207K vs 612 · analyzed 41 days ago, with Copy ANALYZE orders. It fires where the error starts, not on every node that inherits it.

Caveats

• The finding needs 100 or more actual rows and a factor of 10 or more.