PostgreSQL Statistics and the Query Planner

After working as a DBA for long enough, you discover a counterintuitive fact: many slow queries are caused not by missing indexes or poorly written SQL, but by stale statistics.

You add an index, but the query remains slow. You rewrite the SQL, but the planner still stubbornly chooses a Seq Scan. Then you run ANALYZE, and the plan improves instantly. Almost every PostgreSQL engineer has encountered this kind of "paranormal event."

This article will not walk through query-planner theory from beginning to end. Instead, it answers a more practical and consequential question: what are the planner's decisions based on? When does that foundation collapse, and how can you repair or even proactively tune it?

Understand statistics, and you understand half of PostgreSQL performance tuning.

1. The Query Planner: Choosing the Most Cost-Effective Execution Path for Every SQL Statement

Suppose you write a query such as:

SELECT * FROM orders WHERE customer_id = 12345 AND created_at > '2025-01-01';

PostgreSQL does not execute it immediately. After the SQL reaches the database, it goes through two stages:

1. Parser: checks syntax, resolves table and column names, and produces a parse tree.
2. Planner / Optimizer: uses a cost model (Cost-based Optimizer, CBO) to generate the execution plan it believes has the lowest cost.

What does an execution plan look like? It is the tree returned by EXPLAIN:

Hash Join  (cost=10.50..5200.33 rows=320 width=120)
  Hash Cond: (orders.customer_id = customers.id)
  ->  Seq Scan on orders  (cost=0.00..4800.00 rows=4200 width=80)
        Filter: (created_at > '2025-01-01'::date)
  ->  Hash  (cost=8.00..8.00 rows=200 width=40)
        ->  Seq Scan on customers  (cost=0.00..8.00 rows=200 width=40)

Every node in this tree, including Seq Scan, Index Scan, Hash Join, Nested Loop, Sort, and Aggregate, is selected by the planner from multiple candidates. For example, when scanning a table with 10 million rows, the planner must decide:

• Use a full-table scan (Seq Scan), or an index (Index Scan / Bitmap Index Scan)?
• Join two tables with a Hash Join or a Nested Loop? Which table should be the "outer relation"?
• Should it materialize data, or sort it before performing a Merge Join?

These choices are based on a cost model, whose formula looks roughly like this:

cost = seq_page_cost    * sequential pages read
     + random_page_cost * random pages read
     + cpu_tuple_cost   * rows processed
     + ...

Not one of the page counts, row counts, or selectivity estimates in that formula comes out of thin air. They all come from statistics.

In other words, all of the planner's "intelligence" is ultimately downstream of statistics. Once those statistics become inaccurate, the planner becomes a gifted mathematician who cannot see: it still carefully calculates the "lowest cost," but it is calculating against the wrong world.

That is why statistics form the foundation of the entire performance system.

2. Statistics: The Planner's Basis for Decisions

PostgreSQL does not maintain an exact distribution of every value in every column. It stores estimates in a set of system tables. Understand these tables, and you understand the raw material behind the planner's thinking.

1. pg_class.reltuples: Estimated Row Counts for Tables

SELECT relname, reltuples, relpages
FROM pg_class
WHERE relname IN ('orders', 'customers');

 relname   | reltuples  | relpages
-----------+------------+----------
 orders    | 1.2e+07    | 240000
 customers | 85000      | 1200

reltuples estimates how many rows the table contains, while relpages estimates how many 8 KB disk pages it occupies. ANALYZE / VACUUM update both values.

When deciding whether to use an index, one of the first values the planner examines is reltuples. A simple rule is: if a predicate has high selectivity in the sense that it matches many rows, using the index may be slower because random I/O is expensive, so a Seq Scan is preferable.

2. pg_statistic: The Underlying Statistics Table

pg_class tells the planner only how many rows a table contains. The planner also needs to know what each column looks like. That information lives in pg_statistic, with one record per column containing:

• stanullfrac: the proportion of NULL values.
• stadistinct: the number of distinct values in the column, or a ratio.
• most_common_vals (MCV) + most_common_freqs: the most common values and their frequencies, representing the "spikes" in the histogram.
• histogram_bounds: equal-frequency histogram boundaries used to estimate the selectivity of a value's bucket.
• correlation: the correlation between physical row order and column-value order. This directly affects the estimated cost of an Index Scan because reading an index is cheaper when the column is physically ordered.

3. pg_stats: The Human-Readable View

pg_statistic is an internal table and is difficult for humans to inspect directly. PostgreSQL provides the pg_stats view, which is the first tool to use when diagnosing statistics problems:

SELECT
  attname        AS column,
  n_distinct,
  most_common_vals   AS mcv,
  most_common_freqs  AS freq,
  correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname IN ('customer_id', 'status', 'created_at');

  column    | n_distinct |          mcv          |         freq          | correlation
------------+------------+-----------------------+-----------------------+-------------
 customer_id |      85000 | {1024,88,1025}        | {0.0033,0.0029,0.0028}|     0.02
 status      |          4 | {paid,shipped,pending}| {0.61,0.22,0.13}      |     0.15
 created_at  |         -1 |                       |                       |     0.98

Understand these rows, and you understand how the planner sees this table:

• customer_id has approximately 85,000 distinct values. It is a uniformly distributed foreign key, and correlation=0.02 means its physical order is nearly random. Random index reads will be expensive, so the planner will be cautious.
• status has only four values, with paid accounting for 61%. WHERE status='paid' matches most of the table, so the planner will certainly avoid the index.
• n_distinct=-1 for created_at means that almost every row has a different value, while correlation=0.98 means rows are inserted in chronological order. An index is especially cost-effective for range queries such as WHERE created_at > X.

The planner is not merely "guessing." It reads these numbers and performs arithmetic. Whether the numbers you give it are correct directly determines whether its arithmetic is correct.

3. ANALYZE: How Statistics Are Collected

Statistics do not appear on their own. They are produced by the ANALYZE command.

1. ANALYZE Uses Sampling, Not a Full-Table Scan

A common beginner misconception is: "ANALYZE scans the whole table, so it is slow and should not be run casually."

That is wrong. ANALYZE samples data. By default, it reads a number of randomly selected 8 KB pages from the table, on the order of 300 × default_statistics_target, samples rows from them, and estimates histograms, MCVs, distinct values, and other statistics. On a table with hundreds of millions of rows, ANALYZE may inspect less than 1% of the data and finish in a few hundred milliseconds.

ANALYZE orders;              -- Sample every column in this table
ANALYZE orders(customer_id); -- Sample only one column (faster)

This is completely different from VACUUM. VACUUM reclaims dead tuples, a side effect of MVCC, and performs a real scan. ANALYZE only refreshes statistics. VACUUM ANALYZE combines the two, but they can also be run separately.

2. autovacuum: Running Automatically in the Background

PostgreSQL has an autovacuum background worker that automatically triggers ANALYZE and VACUUM when thresholds are reached. The trigger condition is approximately:

Trigger ANALYZE when: modified rows on the table > INSERT/UPDATE/DELETE threshold
                      (default scale_factor=10% + base_threshold=50)

In other words, after 1 million rows in a 10-million-row table are modified, autovacuum automatically runs ANALYZE.

That is useful, but it has blind spots, and those blind spots cause incidents:

• autovacuum primarily prioritizes cleaning up dead tuples; ANALYZE is secondary. If the system is busy and autovacuum cannot get scheduled, statistics lag behind.
• Statistics may lag badly after a large COPY / INSERT import. autovacuum triggers after the event, not before it. If you query immediately after importing 10 million rows, autovacuum may not have reacted yet.
• Statistics for rarely used or small columns may also be insufficiently fresh.

3. When to Run ANALYZE Manually

These are practical engineering rules worth remembering:

-- Scenario: after a large data import
COPY orders FROM '/data/orders.csv' WITH (FORMAT csv);
INSERT INTO report (...) SELECT ... FROM raw WHERE ...;
-- -> Run ANALYZE immediately; do not wait for autovacuum
ANALYZE orders;
ANALYZE report;

-- Scenario: after creating an index or changing the table structure
CREATE INDEX idx_orders_status ON orders(status);
-- -> The index is registered automatically in the system catalogs, but refreshing related statistics is recommended

-- Scenario: before deploying a new query, confirm that statistics are fresh
VACUUM (ANALYZE, VERBOSE) orders;

My engineering practice is: make ANALYZE on critical tables the mandatory final step of every large ETL job. Do not leave stale statistics as a latent risk for production queries.

4. Stale Statistics: Where Planner Decisions Begin to Break Down

Now for the truly dangerous part. What happens when statistics become stale?

The planner carefully and rigorously chooses the wrong optimal path. This is the most insidious type of failure: it produces no error; it is simply slow.

1. Classic Symptom: Choosing a Seq Scan Despite an Available Index

SELECT * FROM orders WHERE customer_id = 12345;

The orders table contains 20 million rows and has a B-tree index on customer_id. An Index Scan should finish within tens of milliseconds. Instead, the execution plan shows:

Seq Scan on orders  (cost=0.00..380000.00 rows=8000000 width=120)
  Filter: (customer_id = 12345)

Why? The statistics are six months old. At that time, the table contained only 100,000 rows, and the planner "remembers" that customer_id=12345 matched 40,000 rows, or 40%, so it correctly chooses a Seq Scan.

Today, however, the table contains 20 million rows, while those old statistics still say "40%." The planner calculates: "This will scan 8 million rows. An index would require 8 million random reads, so a sequential scan is better." The arithmetic is correct, but the premise is wrong.

Run ANALYZE, and the refreshed statistics say "approximately 200 matching rows." The plan immediately switches to an Index Scan and finishes within milliseconds.

2. Classic Symptom: Choosing the Wrong JOIN Method

SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.region = 'EU';

Suppose c.region='EU' actually matches only 200 rows. The planner should choose a Hash Join: build a hash table from those 200 customer rows, scan orders, and match them quickly.

But if the customers statistics are stale, or the data was just imported, the planner may believe region='EU' matches 50,000 rows. It then chooses a Nested Loop instead: 50,000 outer-relation iterations, each performing an index lookup on orders. The result is a query slow enough to make you want to smash the keyboard.

3. Classic Symptom: Wild Materialization or Sorting Caused by Bad Row Estimates

The rows=... value in EXPLAIN is an estimate, not the actual count. If the planner estimates 10 rows, it may decide, "Let's sort them; the input is small." If the actual result contains 100,000 rows, the sort can cause an OOM error or spill into temporary files.

All three symptoms have the same root cause: the statistics no longer reflect the current data.

That is why the first step in diagnosing any slow query is always: inspect EXPLAIN, refresh the statistics, and inspect it again. Do not begin by adding an index.

5. Statistics Targets (default_statistics_target): Balancing Accuracy and Overhead

At this point, you may be wondering: if ANALYZE samples data, would a larger sample be more accurate?

Yes. default_statistics_target controls the number of sampling buckets and defaults to 100. This means the histogram stores 100 buckets and the MCV list stores approximately the 100 most common values.

• Higher values → more accurate statistics → better planner decisions
• But ANALYZE becomes slower, pg_statistic grows larger, and planning also takes longer

1. Adjusting It Globally

ALTER SYSTEM SET default_statistics_target = 500;  -- 100 → 500
SELECT pg_reload_conf();

A value of 100 is sufficient for most workloads. I typically raise it to 200-500 for complex analytical databases or databases containing many heavily skewed columns. Do not blindly raise it to 1000 or more. This can make ANALYZE noticeably slower on large tables, with diminishing returns.

2. Adjusting It Per Column (Recommended)

A better approach is to increase the target only for columns whose distributions are particularly important or heavily skewed, while leaving other columns at the default:

-- This column is heavily skewed and queried frequently, so increase its statistics accuracy independently
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;

-- Resample the column for the change to take effect
ANALYZE orders(customer_id);

Typical columns that deserve a higher target include:

• High-cardinality columns that frequently appear in query predicates (user_id, order_id)
• Heavily skewed columns (90% are status=paid, while 10% have other values)
• Columns frequently used in JOINs

3. A Practical Rule of Thumb

If EXPLAIN shows that the estimated and actual row counts for a predicate differ dramatically, do not suspect the index first. Try:

ALTER TABLE t ALTER COLUMN col SET STATISTICS 500;
ANALYZE t(col);

Then run the query again. The problem often disappears. This is the most practical use of a statistics target: feeding more accurate data to a small number of critical columns.

6. Extended Statistics (CREATE STATISTICS): Moving Beyond the Single-Column Assumption

This is one of the most frequently overlooked yet powerful parts of the statistics system.

1. Default Statistics Examine Only Individual Columns

pg_statistic stores statistics one column at a time. The planner can estimate WHERE a = 1 and WHERE b = 2, but consider:

SELECT * FROM users WHERE country = 'CN' AND language = 'zh';

The planner assumes that country and language are independent, multiplying their selectivities:

selectivity(country='CN') * selectivity(language='zh')

But if the two columns are highly correlated, with nearly every user in CN using zh, this estimate is far too low. The planner expects only dozens of rows when the query actually matches millions. The Index Scan selectivity is wrong, and the entire plan may collapse.

2. CREATE STATISTICS: Tell the Planner That Columns Are Correlated

PostgreSQL 10+ provides extended statistics, allowing the planner to understand relationships among columns:

-- Functional dependency: a determines b (country → language)
CREATE STATISTICS st_country_language (dependencies)
  ON country, language FROM users;

-- Multicolumn MCV: joint distribution histogram
CREATE STATISTICS st_country_language_mcv (mcv)
  ON country, language FROM users;

-- NDV: number of distinct combinations across several columns
CREATE STATISTICS st_user_ndistinct (ndistinct)
  ON user_id, org_id, team_id FROM events;

-- ANALYZE must run after creating the statistics for them to take effect
ANALYZE users;
ANALYZE events;

Afterward, the planner's row estimate for country='CN' AND language='zh' changes from an "independence assumption" to an estimate based on the real joint distribution, immediately making it more accurate.

3. Real-World Use Cases

When should you create extended statistics? Examine your slow-query logs for:

• Multicolumn AND predicates whose columns are correlated (country + language, gender + occupation, product category + brand)
• Combined selectivity of JOIN keys that differs greatly from their individual selectivities
• Columns with obvious functional dependencies (zip_code → city, org_id → department)

Keep one limitation in mind: extended statistics work for AND and equality/range conditions, but not for OR; they also increase ANALYZE overhead. More is not always better. Create them only when the distributions are genuinely correlated.

My recommendation is to treat CREATE STATISTICS as a precision tool for cases where default statistics clearly miscalculate selectivity, not as a default configuration to deploy everywhere.

7. Practical Diagnosis: Use EXPLAIN ANALYZE to Expose the "Statistics Illusion"

After all that theory, here is a diagnostic process you can use immediately. It is an SOP I repeatedly promote within teams.

1. Always Use EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN provides only estimates. EXPLAIN ANALYZE actually runs the query and reports real row counts and timings. That is the hard evidence needed to diagnose statistics problems:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 12345 AND status = 'paid';

Index Scan using idx_orders_customer on orders  (cost=0.43..123.50 rows=1 width=120) (actual time=0.028..0.028 rows=0 loops=1)
  Index Cond: (customer_id = 12345)
  Filter: (status = 'paid')
  Buffers: shared hit=3
Planning Time: 0.18 ms
Execution Time: 0.04 ms

Focus on two things:

• rows=1 (estimated) vs. actual rows=0 (actual): the estimate is close, so the statistics are fine.
• Planning Time and Execution Time: they immediately reveal which phase is slow.

2. Estimated vs. Actual Rows: The Gold Standard for Identifying Stale Statistics

The key rule: when estimated and actual row counts differ by more than an order of magnitude, the statistics are almost certainly problematic.

Consider this counterexample:

Seq Scan on orders  (cost=0.00..420000.00 rows=500 width=120) (actual time=12.0..9800.0 rows=850000 loops=1)
  Filter: (status = 'pending')
Planning Time: 0.5 ms
Execution Time: 9820 ms

• The planner expects status='pending' to match 500 rows
• It actually matches 850,000 rows, a difference of 1,700 times

This is a textbook case of stale statistics. Either the statistics were not refreshed, or this low-cardinality status column was not represented in the MCV list and its distribution has changed. The repair path is:

-- 1. Refresh statistics
ANALYZE orders;

-- 2. If the column is critical and estimates remain inaccurate, increase its target
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders(status);

-- 3. Inspect the plan again
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;

3. A Complete Diagnostic SOP

Here is the process condensed into five steps for a team Wiki:

-- Step 1: Run the plan and compare estimated vs. actual rows
EXPLAIN (ANALYZE, BUFFERS) <your slow query>;

-- Step 2: If the difference is large, check whether statistics are fresh
SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- A large n_mod_since_analyze means ANALYZE is due

-- Step 3: Refresh statistics and increase the statistics target if necessary
ANALYZE orders;
-- Or target a critical column separately:
-- ALTER TABLE orders ALTER COLUMN col SET STATISTICS 500;
-- ANALYZE orders(col);

-- Step 4: If a multicolumn AND estimate is wildly inaccurate, consider extended statistics
-- CREATE STATISTICS ... (dependencies|mcv|ndistinct) ON col1, col2 FROM orders;
-- ANALYZE orders;

-- Step 5: Run the plan again and compare the improvement
EXPLAIN (ANALYZE, BUFFERS) <your slow query>;

Two practical details are worth mentioning:

• pg_stat_user_tables.n_mod_since_analyze is a concrete indicator of whether ANALYZE is needed. If more than 50% of a table's rows have changed since the last ANALYZE, its statistics almost certainly need to be refreshed.

• EXPLAIN ANALYZE actually executes the query, including UPDATE/DELETE. To inspect the execution plan safely, wrap EXPLAIN ANALYZE in a transaction and then run ROLLBACK:

  BEGIN;
  EXPLAIN ANALYZE UPDATE orders SET status='paid' WHERE id=1;
  ROLLBACK;
  

Summary

• The planner is cost-based, and every input to its cost formula comes from statistics. Statistics are the foundation; when that foundation fails, even the planner's most careful calculations are wrong.
• Statistics are sampled estimates, not exact values. pg_class.reltuples supplies row counts, while pg_statistic, exposed through the readable pg_stats view, supplies column distributions, MCVs, histograms, and correlations.
• ANALYZE refreshes statistics through sampling rather than a full-table scan, so it is fast. autovacuum runs it automatically, but run ANALYZE manually after bulk imports instead of relying on a trigger that reacts afterward.
• The clearest symptom of stale statistics is an orders-of-magnitude gap between estimated and actual rows: an index exists but the planner chooses a Seq Scan, it selects the wrong JOIN method, or it sorts aggressively when it should not. Run EXPLAIN ANALYZE and refresh statistics before considering other changes.
• Statistics targets and extended statistics are precision tools: increase STATISTICS separately for important skewed columns, and use CREATE STATISTICS for highly correlated column groups to overcome the single-column independence assumption. Apply both where appropriate, not indiscriminately.

In one sentence: PostgreSQL's query planner is a brilliant abacus master, but every bead on its abacus is a statistic you feed it. Feed it the wrong beads, and no matter how quickly it calculates, the answer will still be wrong.