Statistics, ANALYZE and Why the Planner Guesses Wrong

The planner does not look at your data when it picks a plan. It looks at a summary of the data, called statistics, and works out how many rows each step will return. If the summary is missing, old or too simple, the guess is wrong, and a wrong guess can turn into a plan that is much slower than the right one. This lesson shows the three common causes: statistics that were never gathered, statistics that went stale, and columns that depend on each other. Each one is fixed in a different way.

The table is the same 20,000 invented orders as in the last lesson, built again so this lesson stands alone. One change: autovacuum_enabled = false on this table only. Autovacuum normally gathers statistics in the background, and here it would do it before you could see the problem. Do not copy that setting to a real table.

Before any statistics exist

CREATE TABLE big_orders AS
SELECT g                                AS order_id,
       (g % 8) + 1                      AS customer_id,
       DATE '2026-01-01' + (g % 365)    AS order_date,
       CASE WHEN g % 100 = 0 THEN 'cancelled'
            WHEN g % 100 < 4 THEN 'new'
            WHEN g % 100 < 12 THEN 'paid'
            ELSE 'shipped' END          AS status,
       ((g % 97) + 0.5)::numeric(8,2)   AS total
FROM generate_series(1, 20000) AS g;
ALTER TABLE big_orders SET (autovacuum_enabled = false);
SET max_parallel_workers_per_gather = 0;
EXPLAIN SELECT * FROM customers;
EXPLAIN SELECT * FROM big_orders WHERE status = 'new';

SELECT 20000
ALTER TABLE
SET
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on customers  (cost=0.00..16.10 rows=610 width=104)
(1 row)

                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on big_orders  (cost=0.00..417.60 rows=90 width=58)
   Filter: (status = 'new'::text)
(2 rows)

The shop has 8 customers, and the plan expects 610. The orders table has 600 new orders, and the plan expects 90. Nobody has run ANALYZE, so PostgreSQL has no numbers and uses defaults built from the file size. This happens right after a bulk load, a restore, or a new table. In real work, EXPLAIN without ANALYZE shows only the expected rows, which is also a guess from these statistics.

What ANALYZE gathers

ANALYZE reads a sample of the table (the whole table when it is small) and stores a summary per column. The planner reads it from pg_class and pg_stats.

ANALYZE big_orders;
SELECT reltuples::int AS rows, relpages AS pages FROM pg_class WHERE relname = 'big_orders';
SELECT attname, n_distinct, null_frac
FROM pg_stats WHERE tablename = 'big_orders' AND attname IN ('status', 'customer_id')
ORDER BY attname;
SELECT most_common_vals, most_common_freqs
FROM pg_stats WHERE tablename = 'big_orders' AND attname = 'status';

ANALYZE
 rows  | pages
-------+-------
 20000 |   192
(1 row)

   attname   | n_distinct | null_frac
-------------+------------+-----------
 customer_id |          8 |         0
 status      |          4 |         0
(2 rows)

       most_common_vals       |   most_common_freqs
------------------------------+-----------------------
 {shipped,paid,new,cancelled} | {0.88,0.08,0.03,0.01}
(1 row)

n_distinct is the number of different values: 8 customers, 4 statuses. most_common_vals and most_common_freqs are the common values and their share of the table, so new is 0.03 of 20,000, which is 600. For columns with many values, PostgreSQL also keeps a histogram of the value ranges, histogram_bounds.

EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM big_orders WHERE status = 'new';
EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM big_orders WHERE status = 'refunded';

                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Seq Scan on big_orders  (cost=0.00..442.00 rows=600 width=25) (actual rows=600.00 loops=1)
   Filter: (status = 'new'::text)
   Rows Removed by Filter: 19400
(3 rows)

                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Seq Scan on big_orders  (cost=0.00..442.00 rows=1 width=25) (actual rows=0.00 loops=1)
   Filter: (status = 'refunded'::text)
   Rows Removed by Filter: 20000
(3 rows)

Now new is estimated at 600 and is 600. A value that is not in the list, refunded, is estimated at 1, because the planner never returns an estimate of 0 rows.

Statistics go stale

The numbers describe the table as it was at the last ANALYZE. Add a lot of rows and they are wrong. This adds 8,000 orders that are all new, which is 28,000 rows in total.

INSERT INTO big_orders
SELECT g, (g % 8) + 1, DATE '2026-06-01', 'new', 10
FROM generate_series(20001, 28000) AS g;
EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM big_orders WHERE status = 'new';
ANALYZE big_orders;
EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM big_orders WHERE status = 'new';

INSERT 0 8000
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Seq Scan on big_orders  (cost=0.00..557.10 rows=756 width=25) (actual rows=8600.00 loops=1)
   Filter: (status = 'new'::text)
   Rows Removed by Filter: 19400
(3 rows)

ANALYZE
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Seq Scan on big_orders  (cost=0.00..592.00 rows=8600 width=24) (actual rows=8600.00 loops=1)
   Filter: (status = 'new'::text)
   Rows Removed by Filter: 19400
(3 rows)

Before ANALYZE the planner still believed 3% of the table was new and expected about 750 rows. There were 8,600. After ANALYZE the estimate matches. Autovacuum does this for you when enough rows have changed, which is why a table with the default settings rarely gets this far. It can still happen right after a large load, before the background worker gets to the table. Run ANALYZE yourself after a bulk load or a restore.

Columns that depend on each other

The planner assumes that columns are independent. For two conditions on two columns, it multiplies the two shares. That is wrong when one column decides the other. Each warehouse belongs to exactly one region, and each region has two warehouses.

CREATE TABLE shipments AS
SELECT g AS shipment_id,
       'W' || lpad(((g % 20) + 1)::text, 2, '0')       AS warehouse,
       'R' || lpad((((g % 20) / 2) + 1)::text, 2, '0') AS region
FROM generate_series(1, 20000) AS g;
ANALYZE shipments;
EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM shipments WHERE warehouse = 'W03' AND region = 'R02';

SELECT 20000
ANALYZE
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Seq Scan on shipments  (cost=0.00..428.00 rows=100 width=12) (actual rows=1000.00 loops=1)
   Filter: ((warehouse = 'W03'::text) AND (region = 'R02'::text))
   Rows Removed by Filter: 19000
(3 rows)

Warehouse W03 is 1 in 20 rows and region R02 is 1 in 10. Multiplied, that is 1 in 200, or 100 rows. But W03 is always in R02, so the second condition removes nothing, and the real answer is 1,000. The planner is off by a factor of ten, and a join or sort built on that number will be sized for the wrong amount of data. The fix is to tell PostgreSQL about the dependency.

CREATE STATISTICS shipments_wh_region (dependencies) ON warehouse, region FROM shipments;
ANALYZE shipments;
EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM shipments WHERE warehouse = 'W03' AND region = 'R02';
SELECT statistics_name, dependencies FROM pg_stats_ext WHERE statistics_name = 'shipments_wh_region';

CREATE STATISTICS
ANALYZE
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Seq Scan on shipments  (cost=0.00..428.00 rows=1000 width=12) (actual rows=1000.00 loops=1)
   Filter: ((warehouse = 'W03'::text) AND (region = 'R02'::text))
   Rows Removed by Filter: 19000
(3 rows)

   statistics_name   |     dependencies
---------------------+----------------------
 shipments_wh_region | {"2 => 3": 1.000000}
(1 row)

The estimate is now 1,000. CREATE STATISTICS declares the dependency, and ANALYZE fills it in. The value "2 => 3": 1.000000 in pg_stats_ext reads as: column 2 (warehouse) decides column 3 (region) every time, where the numbers are column positions in the table. Other kinds exist: ndistinct for the number of groups in a GROUP BY over several columns, and mcv for common combinations.

Other ways to improve estimates

• ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500 keeps more values and a finer histogram for one column. The default is 100 (default_statistics_target). A higher number makes ANALYZE slower and the statistics larger.
• Keep autovacuum on. Tune its thresholds for a very large table, because the default of 10% changed rows is a lot of rows.
• Do not fight a plan with settings first. Find the node where the estimate is wrong, and fix the statistics behind it.

Common mistakes

• Judging a plan from EXPLAIN alone and not comparing it with EXPLAIN ANALYZE.
• Forgetting ANALYZE after a bulk load, a restore, or a new table.
• Turning autovacuum off to save work, then living with stale numbers.
• Assuming two columns are independent when one decides the other.

If you skip this, you will rewrite queries that were fine and keep the stale numbers that made them slow. Which table in your database had a big load last week, and when was it last analyzed?