Why the Planner Estimates the Wrong Number of Rows

The most common reason for a slow query that was fast yesterday is not a missing index. It is that the planner expected 10 rows and found 100,000, chose a plan that is brilliant for 10 and terrible for 100,000, and nobody noticed because the SQL did not change. Row estimates drive every planner decision, so when they are wrong, everything built on them is suspect. This article lists the causes in the order to check them, with the symptom and the fix for each. It belongs to the series that reading EXPLAIN ANALYZE output in PostgreSQL step by step opens.

Spot the error

Run EXPLAIN (ANALYZE) and compare, node by node, the estimated rows= with the actual rows= times loops. Look for ratios of ten or more.

Index Scan using customer_country_idx on customer c
    (cost=0.42..8.44 rows=1 width=36) (actual time=0.02..12.5 rows=4000 loops=1)
  Index Cond: (country = 'FR')

An estimate of 1 against an actual 4,000 is a 4,000-fold error. Start with the lowest (deepest) node showing a big error, because its parent's estimate is built on it. Fixing the bottom node often fixes the rest.

How the planner estimates

When you run ANALYZE (or autovacuum does), PostgreSQL samples rows (300 times the statistics target, so 30,000 rows by default) and stores per-column statistics in pg_statistic, viewable through pg_stats: the fraction of nulls, the number of distinct values, the most common values with their frequencies, a histogram of the remaining values and the physical correlation. For a condition like country = 'FR', the planner looks up 'FR' in the most common values; if found, it uses that frequency; if not, it spreads the rest of the probability over the remaining distinct values. For ranges it uses the histogram.

SELECT attname, null_frac, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats WHERE tablename = 'customer' AND attname = 'country';

Reading this view for the column behind a wrong estimate tells you what the planner believes.

Cause 1: stale statistics

After a bulk load, a large delete or a heavy update, the statistics describe the table as it was. Autovacuum re-analyzes when changes exceed a threshold (50 rows plus 10 percent of the table by default), so a large table can be stale for a long time, and a newly loaded table may have no statistics at all.

SELECT relname, n_live_tup, n_mod_since_analyze, last_analyze, last_autoanalyze
FROM pg_stat_user_tables WHERE relname = 'customer';

A high n_mod_since_analyze and an old last_autoanalyze confirm it. The fix is ANALYZE customer;, which is quick, and should follow every bulk load in a script. For tables that change fast, lower the autovacuum analyze threshold for that table: ALTER TABLE customer SET (autovacuum_analyze_scale_factor = 0.02).

Cause 2: a statistics target too low for skewed data

The default target of 100 keeps up to 100 most common values and 100 histogram buckets. A column with a long tail of moderately common values, or very skewed data, is described poorly. Raise the target for that column and re-analyze:

ALTER TABLE customer ALTER COLUMN country SET STATISTICS 1000;
ANALYZE customer;

A higher target takes a larger sample and stores more detail, at the cost of longer analyze times and a little planning time. Use it where it helps, not globally; values of 500 to 1,000 are common for troublesome columns, with 10,000 as the maximum. Check n_distinct too: for a large table the sample may estimate it badly. You can override with ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = 5000) when you know the truth.

Cause 3: correlated columns

The planner multiplies the selectivities of conditions on different columns as if they were independent. For WHERE city = 'Paris' AND country = 'FR', if 1 percent of rows are Paris and 5 percent are France, it predicts 0.05 percent, while in reality every Paris row is in France and the answer is 1 percent: a twenty-fold underestimate. Extended statistics teach the planner about the relationship:

CREATE STATISTICS customer_city_country (dependencies, ndistinct, mcv)
    ON city, country FROM customer;
ANALYZE customer;

dependencies captures functional dependencies (city determines country), ndistinct improves estimates of the number of groups for GROUP BY, and mcv stores the most common combinations (PostgreSQL 12 and later). Create them for column groups that appear together in filters and groupings. They are cheap to store and must be re-analyzed with the table.

Cause 4: conditions on expressions

A condition like WHERE lower(email) = '[email protected]' or WHERE date_trunc('day', placed_at) = '2026-03-01' has no column statistics, because the expression is not a column. The planner falls back on a fixed default guess (often 0.5 percent for equality, 33 percent for inequality ranges). Two fixes: create an expression index (the index's statistics are gathered by ANALYZE and used for estimates) or expression statistics:

CREATE INDEX contact_lower_email ON contact (lower(email));
-- or without an index (PostgreSQL 14 and later)
CREATE STATISTICS contact_lower_email_stats ON lower(email) FROM contact;
ANALYZE contact;

Rewriting the condition to compare the bare column to a range (placed_at >= '2026-03-01' AND placed_at < '2026-03-02') avoids the problem and enables ordinary index use.

Cause 5: parameter values the planner cannot see

A prepared statement with parameters can use a generic plan, planned without knowing the values. After five executions with custom plans, PostgreSQL may switch to a generic plan if its cost is not worse than the average custom cost. For a skewed column, a generic plan fits the typical value and fails for the rare huge one (or the reverse). Symptoms: the query is fast in the console with literals and slow from the application. Tests: EXPLAIN (GENERIC_PLAN) (version 16 and later) shows the generic plan, and SET plan_cache_mode = force_custom_plan for the session or role makes the planner plan with the values each time. Applications through a transaction-mode pooler complicate this, as in prepared statements through a pooler.

Cause 6: join estimates

Join size estimates combine the sizes of the inputs with the number of distinct values of the join keys. A join between two skewed columns, where a few values match many rows on both sides, is estimated from averages and underestimates badly. Extended statistics do not cross tables. Options: rewrite to filter earlier so the join sees smaller inputs; materialize an intermediate result in a temporary table and ANALYZE it; use a CTE with MATERIALIZED to separate the estimates; or restructure to avoid joining on the skewed key.

Cause 7: partitions, foreign tables and functions

Partitioned tables keep statistics per partition, and the parent has none of its own for some purposes; analyze the partitions (autovacuum handles leaf partitions but not the parent, so run ANALYZE parent after loads). Foreign tables are estimated from remote statistics only if use_remote_estimate is on. Set-returning functions return a default of 1,000 rows unless the function declares ROWS n in its definition. A temporary table has no statistics until you analyze it, and autovacuum never touches temporary tables, so run ANALYZE explicitly after filling one.

Cause 8: the data changed shape

Statistics are a snapshot of a distribution. A new customer that brings ten million rows into a multi-tenant table changes selectivity for that tenant overnight, and the histogram does not know. For skewed tenants, per-tenant partitions or partial indexes and a re-analyze after onboarding keep the estimates meaningful.

A routine for fixing an estimate

1. Find the lowest node with a large error.

2. Identify the condition behind it.

3. Look at pg_stats for the column or expression.

4. Check last_analyze and run ANALYZE; rerun the plan.

5. If the error remains, raise the statistics target for that column.

6. If two columns are involved, create extended statistics.

7. If an expression is involved, create expression statistics or an expression index.

8. If parameters are involved, test with GENERIC_PLAN and custom plans.

9. Rerun EXPLAIN (ANALYZE) and confirm the estimate is within a factor of three or so. Estimates need not be exact, only close enough that the plan choice is stable.

Keep the before and after plans, as comparing plans before and after a change describes.

When the estimate is wrong and nothing can be done cheaply

Some estimates cannot be fixed with statistics: a condition built from arbitrary function calls on several columns, a join on a computed key, a query over a table whose distribution shifts every hour. Practical options then include rewriting the query to give the planner simpler conditions, splitting the query into steps with materialized intermediate results that get their own statistics, using partial indexes that make the right plan the obvious one, or, as a last resort, a hint-like override through enable_ settings or the pg_hint_plan extension where your provider allows it. Accept that some plans will be unstable, and monitor those queries with auto_explain so the next regression is caught fast.

What to do next

List your five slowest queries and compare estimates with actuals in each. For any with a ten-fold error, apply the routine above. The manual documents the statistics in planner statistics and extended statistics in CREATE STATISTICS, with the background in how the planner uses statistics.