How to diagnose PostgreSQL row estimate errors

Scope and versions

This guide covers PostgreSQL 16, 17, and 18. SQL and plan excerpts are illustrative. Output and estimates depend on table contents, schema, statistics settings, configuration, query parameters, and release.

Compare estimated and actual rows

Consider a query filtering a frequently queried status:

EXPLAIN
SELECT id, created_at
FROM orders
WHERE status = 'pending';

An illustrative plain plan might estimate only a few matching rows:

Seq Scan on orders  (cost=0.00..2200.00 rows=12 width=16)
  Filter: (status = 'pending'::text)

On a safe test system, EXPLAIN (ANALYZE, BUFFERS) might instead report:

Seq Scan on orders  (cost=0.00..2200.00 rows=12 width=16)
                    (actual time=0.020..15.800 rows=18500 loops=1)
  Filter: (status = 'pending'::text)
  Rows Removed by Filter: 81500

These fabricated excerpts demonstrate a mismatch, not a benchmark. Here, the estimate is far below observed output. A sequential scan may still be reasonable because many rows are returned. Understand the estimate before deciding whether an index is appropriate. See how to read a PostgreSQL EXPLAIN plan.

Safety: EXPLAIN ANALYZE executes the statement. This SELECT reads data and consumes database resources; an expensive query runs as usual. For data-modifying statements it performs the modification. Use a safe environment and review version-specific EXPLAIN documentation.

Find where the estimate first goes wrong

1. Compare estimated rows and actual rows at each node, starting with scans near the bottom.
2. For nodes executed multiple times, account for loops: actual rows and time are reported as per-loop averages. Consider total work across loops.
3. Check rows removed by filters and join output. A scan mismatch can lead to poor downstream join choices.
4. Repeat with representative parameter values; a skewed distribution can make one value unlike another.

Estimate errors describe planner cardinality assumptions; they are distinct from execution time. See official Using EXPLAIN.

Common causes and checks

• Statistics have not caught up: Check when the table was last analyzed and whether substantial data changes occurred since. Autovacuum normally analyzes tables as needed, but workload and settings affect timing.
• Highly skewed values: Inspect column statistics with appropriate privileges. A higher per-column statistics target can collect more detail, with analysis and catalog-space costs.
• Columns are related: Independent single-column statistics can misestimate combined conditions when columns are correlated. Extended statistics can capture selected dependencies, distinct counts, or most-common-value combinations for columns in one table.
• Different data or expression: Confirm the tested parameters resemble production values. Base-column statistics may not represent a transformed expression accurately.

Use ANALYZE orders; to collect fresh statistics under your operational process. For correlated filters in one table, a candidate such as CREATE STATISTICS orders_status_region_stats (dependencies, mcv) ON status, region FROM orders; can be evaluated, followed by ANALYZE orders;. Extended statistics do not fix every estimate problem. See PostgreSQL's planner statistics, CREATE STATISTICS, and row estimation examples.

Verify a statistics change

1. Save the original plan, estimates and actual rows, query, parameters, data volume, and server version.
2. Change one cause at a time, such as refreshing statistics or raising a targeted column statistics setting. Follow operational procedures.
3. Re-run with representative parameters and data. Compare estimates, actual rows, plan shape, buffers, and repeated latency measurements.
4. Confirm result correctness and check other important queries and write workloads.

Better estimates can help the planner compare alternatives, but do not guarantee lower latency or a particular index choice. If this mismatch led you to suspect index selection, read why PostgreSQL may not use an index.

Primary sources

• PostgreSQL 18: Planner Statistics
• PostgreSQL 18: ANALYZE
• PostgreSQL 18: CREATE STATISTICS
• PostgreSQL 18: How the Planner Uses Statistics
• PostgreSQL 18: EXPLAIN

These chapters are also available in PostgreSQL 16 and 17 manuals; confirm feature details against the deployed major version.

Editorial review

TuningLens editorial team · Published 29 September 2026

TuningLens beta currently supports MySQL and MariaDB SELECT statements only. PostgreSQL analysis is not available.

TuningLens does not connect to your database or execute submitted SQL. Recommendations require review and testing in your environment.