0Pricing
PostgreSQL Performance & Query Optimization · Lesson

Validating Estimates Against Actual Rows

Compare planned and actual cardinalities in EXPLAIN ANALYZE to confirm statistics fixes landed.

Why Validate Estimates?

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.

Reading the Two Numbers

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.

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status = 'shipped'
  AND ship_country = 'DE';

All lessons in this course

  1. How the Planner Estimates Row Counts
  2. Multivariate Statistics for Correlated Columns
  3. MCV and N-Distinct Corrections
  4. Validating Estimates Against Actual Rows
← Back to PostgreSQL Performance & Query Optimization