PostgreSQL planner misestimates: statistics targets, extended statistics and correlated columns

Most bad PostgreSQL plans start with a bad row estimate. The planner thinks a filter returns 10 rows, chooses a nested loop, and then 2 million rows arrive. Fix the estimate and the plan usually fixes itself.

#Find the misestimate

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE country = 'DE' AND city = 'Berlin';

Index Scan using idx_orders_city on orders  (cost=0.43..120.11 rows=31 width=96)
                                            (actual time=0.05..420.33 rows=184220 loops=1)

Estimated 31, actual 184,220. A factor of 10 is worth a look; factors of 1,000+ usually explain a bad plan. Work from the lowest node with a big error upward: errors multiply through joins.

#Why estimates go wrong

#Step 1: fresh statistics

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

ANALYZE orders;

For tables that change heavily between autovacuum runs, lower the analyze threshold for that table:

ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02, autovacuum_analyze_threshold = 5000);

#Step 2: raise the statistics target for skewed columns

SELECT attname, n_distinct, array_length(most_common_vals::text::text[], 1) AS mcv_count
FROM pg_stats WHERE tablename = 'orders' AND attname IN ('customer_id', 'status');

ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
ANALYZE orders (customer_id);

A higher target stores more most-common values and histogram buckets (max 10,000). It costs ANALYZE time and planning time; raise it only on columns that need it.

#Step 3: extended statistics for correlated columns

CREATE STATISTICS st_orders_country_city (dependencies, ndistinct, mcv)
  ON country, city FROM orders;
ANALYZE orders;

Check they were built:

SELECT statistics_name, kinds, n_distinct, dependencies
FROM pg_stats_ext WHERE tablename = 'orders';

Re-run the EXPLAIN. The estimate should move close to the actual count.

#Expression statistics (PostgreSQL 14+)

CREATE STATISTICS st_orders_day ON (date_trunc('day', created_at)) FROM orders;
ANALYZE orders;

An expression index also collects statistics on its expression, which helps when you need the index anyway.

Tip

Extended statistics apply to columns of one table. They do not fix cross-table join correlation. For those, consider denormalising the filtering column, or rewriting the query so the selective filter applies before the join.

#Step 4: generic vs custom plans

Prepared statements (most drivers, PL/pgSQL) may switch to a generic plan after five executions if it looks no worse on average. For skewed parameters that can be bad:

SET plan_cache_mode = force_custom_plan;          -- per session or role
ALTER ROLE app_shop SET plan_cache_mode = force_custom_plan;

Use it narrowly: custom plans mean planning every execution.

#Step 5: when to use planner settings

Settings like enable_nestloop = off or random_page_cost changes are blunt tools. Valid uses:

• random_page_cost = 1.1 on SSD/NVMe storage (the default 4.0 assumes spinning disks), set server-wide after testing.
• SET LOCAL enable_nestloop = off inside one known problem transaction while you fix statistics.

Do not switch off planner features globally to fix one query.

#Finding misestimates across the workload

auto_explain logs plans of slow queries with actual rows:

shared_preload_libraries = 'auto_explain,pg_stat_statements'
auto_explain.log_min_duration = '2s'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.sample_rate = 0.1

log_analyze adds overhead to every sampled query; keep the sample rate low on busy systems. Then search the logs for nodes where rows= estimate and actual ... rows= differ by large factors.

#Checklist

1. EXPLAIN (ANALYZE, BUFFERS) and find the lowest big misestimate.
2. ANALYZE the tables involved.
3. Skewed column: raise its statistics target.
4. Correlated columns in the same table: extended statistics.
5. Expressions: expression statistics or index.
6. Prepared statements: check generic plan behaviour.
7. Only then consider query rewrites or targeted planner settings.

#Prevent it next time

• Autovacuum/analyze tuned per large table, monitored with n_mod_since_analyze.
• Extended statistics defined in migrations next to the indexes they complement.
• auto_explain sampling on in production, reviewed weekly.