Validazione delle stime rispetto alle righe effettive

When you fix bad statistics with CREATE STATISTICS or by raising default_statistics_target, you need proof that the planner now estimates cardinalities correctly.

The single best tool is EXPLAIN ANALYZE. It runs the query and reports, for every plan node, both:

• the planner's estimated row count (rows=)
• the actual row count observed at runtime (actual rows=)

If estimate and actual are close, your statistics fix landed. If they diverge by 10x or 100x, the planner is still flying blind.

Plain EXPLAIN shows only estimates. To get actuals you must execute the query with ANALYZE.

Each node line looks like this:

• rows=120 — the estimate
• actual ... rows=11500 — what really happened

A ~100x gap on the orders scan is exactly the kind of misestimate that leads to a nested loop where a hash join would have been far cheaper.

The metric to watch is the estimation ratio per node:

• ratio = actual_rows / estimated_rows

Interpretation:

• ~1.0 — healthy, the planner sees the data correctly
• > 10 or < 0.1 — a real misestimate worth investigating
• > 100 — almost always the root cause of a bad plan

Always compare at the node where the filter or join actually applies, not just the top-level row count.

The most common reading mistake: actual rows is reported per loop, not as a total.

If a node shows actual ... rows=5 loops=2000, the true number of rows produced is 5 × 2000 = 10000.

Compare the planner's estimate against actual_rows × loops, never against the raw per-loop figure. Forgetting this makes a healthy inner-loop node look like a wild misestimate.

After a good statistics fix, estimate and actual should line up on the driving nodes. Read this fragment carefully:

• Seq Scan estimate rows=11200 vs actual rows=11500 → ratio 1.03, healthy
• The aggregate on top also matches closely

This is the outcome you are validating for: the numbers in parentheses on the left agree with the numbers after actual on the right.

The planner assumes columns are independent and multiplies their selectivities. When columns are correlated, the combined estimate collapses far below reality.

Example: most rows where city = 'Berlin' also have country = 'DE'. The planner multiplies the two fractions and predicts a tiny result, but the actual count is large.

This is precisely where multivariate extended statistics repair the estimate.

To teach the planner about correlation, create an extended statistics object with the dependencies kind, then analyze the table so the new statistics are populated.

Critically: CREATE STATISTICS alone does nothing until ANALYZE runs. Validation is meaningless if you skip the refresh.

Validation is a comparison, so capture two snapshots:

• Before: run EXPLAIN ANALYZE and record the estimate vs actual on the filtered node (e.g. rows=40 vs actual rows=9000)
• After: create + analyze the statistics, then re-run the identical query

The fix landed only if the estimate moved toward the actual (e.g. now rows=8700 vs actual rows=9000). A changed plan shape is a bonus, not the proof — the estimate convergence is the proof.

You can confirm extended statistics were computed without re-running the query, by reading pg_stats_ext.

If the dependency degrees are populated (close to 1.0 for strongly correlated pairs), ANALYZE did its job and the planner has the data it needs.

For serious validation, add options to the command:

• BUFFERS — shows shared block hits/reads, exposing I/O caused by a misestimated scan
• FORMAT JSON — gives machine-readable Plan Rows and Plan Actual Rows fields you can diff programmatically
• SETTINGS — echoes non-default planner settings that may be skewing the test

JSON output is ideal when scripting regression checks across many queries.

When the estimate still looks off, check the Rows Removed by Filter line. A node can scan millions of rows yet return few, and the estimate you validate is about returned rows.

Also confirm you are validating a representative parameter value. A query that is healthy for country = 'DE' may misestimate badly for a rare value — per-value MCV skew is normal and may need a higher statistics target rather than extended statistics.

Test your understanding of validating estimates against actual rows.

To confirm a statistics fix landed, validate estimates against actuals with discipline:

• Run EXPLAIN ANALYZE and read rows= (estimate) against actual ... rows= per plan node.
• Compute the ratio; treat >10x or <0.1x as a real misestimate, >100x as a likely root cause.
• Always multiply actual rows by loops to get the true total.
• For correlated columns, create extended statistics (dependencies/ndistinct/mcv) and then ANALYZE — creation alone does nothing.
• Use a before/after snapshot: the proof is the estimate converging toward the actual, not merely a changed plan.
• Reach for BUFFERS, FORMAT JSON, and pg_stats_ext to make validation rigorous and scriptable.