Lab 13: Find out why the planner is wrong, and correct it

Objective

Lab 12 established that a bad row estimate invalidates every decision made above it. This lab is about where bad estimates come from and what to do about them.

There are two mechanisms and they need different fixes. The first is structural: the planner assumes predicates on different columns are independent, and multiplies their selectivities. When the columns are correlated — a city and its country, a product and its category, a server and its rack — the product is far too small.

The second is temporal: the statistics describe data that no longer exists. You will find that this is less dangerous than it is usually described, because one part of it self-corrects, and much more dangerous in a specific case, where it produces an estimate two million times too small.

Architecture

Three scenarios, each isolating one cause.

flowchart TD
    A["addresses\ncountry and city\nperfectly correlated"] --> E1["estimate 33,627\nactual 100,000\n3x"]
    E1 --> F1["CREATE STATISTICS\n-> 99,320"]
    B["sites\nregion and zone\n100 correlated values each"] --> E2["estimate 29\nactual 3,000\n103x"]
    E2 --> D["join plan built on it:\nNested Loop, 3,000 loops"]
    D --> F2["CREATE STATISTICS\n-> parallel plan, 40% faster"]
    C["events\nbulk-loaded after ANALYZE"] --> E3["row count: self-corrects\ndistribution: does NOT"]
    E3 --> F3["estimate rows=1\nactual 2,000,000"]

Requirements

• A PostgreSQL 18 cluster with superuser access. The lab creates and drops a database called lab13.
• Roughly 500 MB of free disk; the last scenario loads four million rows.

Scenario

A query that has always been fast has become slow, and the plan has changed. Nobody deployed anything. You need to find out what the planner now believes that it did not believe last week, and whether it is right.

Tasks

Task 1 — Build two perfectly correlated columns

LAB="$HOME/rbpg-lab-13"
mkdir -p "$LAB"

docker exec -i -u postgres rbpg-lab01 psql -X -c "CREATE DATABASE lab13;"
docker exec -i -u postgres rbpg-lab01 psql -X -d lab13 <<'SQL'
CREATE TABLE addresses(id int PRIMARY KEY, country text, city text,
                       postcode text, resident text);
INSERT INTO addresses
SELECT g,
       (ARRAY['GB','US','DE'])[1+((g/100000)%3)],
       (ARRAY['London','NewYork','Berlin'])[1+((g/100000)%3)],
       'PC-'||(g%1000), 'resident-'||g
FROM generate_series(1,300000) g;
CREATE INDEX addresses_country_city_idx ON addresses(country, city);
SQL

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "VACUUM ANALYZE addresses;"
docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c \
  "SELECT country, city, count(*) FROM addresses GROUP BY country, city ORDER BY country;"

$ psql -X -d lab13 -c "SELECT country, city, count(*) FROM addresses GROUP BY country, city ORDER BY country;"

 country |  city   | count  
---------+---------+--------
DE      | Berlin  | 100000
GB      | London  | 100000
US      | NewYork | 100000
(3 rows)

Task 2 — Each predicate alone is estimated well

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c \
  "EXPLAIN ANALYZE SELECT * FROM addresses WHERE country = 'GB';" | head -3 \
  | tee "$LAB/independence.txt"
docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c \
  "EXPLAIN ANALYZE SELECT * FROM addresses WHERE city = 'London';" | head -3 \
  | tee -a "$LAB/independence.txt"

$ EXPLAIN ANALYZE on each predicate separately

 Bitmap Heap Scan on addresses  (cost=1130.83..4886.33 rows=100440 width=35) (actual time=1.132..6.187 rows=100000.00 loops=1)
 Recheck Cond: (country = 'GB'::text)

Bitmap Heap Scan on addresses  (cost=1084.10..4839.60 rows=100440 width=35) (actual time=1.151..6.165 rows=100000.00 loops=1)
 Recheck Cond: (city = 'London'::text)

Both estimates are excellent — a ratio of 1.004. The single-column statistics are doing their job perfectly.

Task 3 — Both predicates together, and the arithmetic behind the error

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c \
  "EXPLAIN ANALYZE SELECT * FROM addresses WHERE country = 'GB' AND city = 'London';" \
  | tee -a "$LAB/independence.txt"

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "
  SELECT 100440.0/300000 AS sel_country,
         100440.0/300000 AS sel_city,
         round((100440.0/300000)*(100440.0/300000)*300000) AS product_estimate,
         100000 AS actual;"

$ EXPLAIN ANALYZE with both predicates, then the selectivity arithmetic

   ->  Bitmap Index Scan on addresses_country_city_idx  (cost=0.00..456.69 rows=33627 width=0) (actual time=1.092..1.092 rows=100000.00 loops=1)
       Index Cond: ((country = 'GB'::text) AND (city = 'London'::text))
       Index Searches: 1
Execution Time: 8.613 ms

    sel_country       |        sel_city        | product_estimate | actual 
------------------------+------------------------+------------------+--------
0.33480000000000000000 | 0.33480000000000000000 |            33627 | 100000
(1 row)

The hand calculation produces 33,627 and the plan says 33,627. The planner multiplied 0.3348 by 0.3348 and applied it to 300,000 rows.

That is correct arithmetic on a false premise. Every city = 'London' row is already a country = 'GB' row, so adding the second predicate removes nothing at all, and the true answer is 100,000.

Task 4 — Teach the planner the dependency

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "
  CREATE STATISTICS addresses_country_city (dependencies, ndistinct)
  ON country, city FROM addresses;"
docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "ANALYZE addresses;"

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c \
  "EXPLAIN ANALYZE SELECT * FROM addresses WHERE country = 'GB' AND city = 'London';" \
  | head -3 | tee "$LAB/extended-stats.txt"

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "
  SELECT statistics_name, n_distinct, dependencies FROM pg_stats_ext
  WHERE statistics_name = 'addresses_country_city';" | tee -a "$LAB/extended-stats.txt"

$ CREATE STATISTICS, ANALYZE, re-explain, then read pg_stats_ext

 Bitmap Heap Scan on addresses  (cost=1366.45..5356.25 rows=99320 width=35) (actual time=1.113..6.115 rows=100000.00 loops=1)
 Recheck Cond: ((country = 'GB'::text) AND (city = 'London'::text))

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

The estimate is now 99,320 against 100,000 actual — a ratio of 0.993.

Read what it stored. dependencies says "2 => 3": 1.000000, meaning column 2 (country) determines column 3 (city) with a strength of 1.0 — a perfect functional dependency — and the reverse is also 1.0. n_distinct says "2, 3": 3: the pair takes only three distinct combinations, not the 3 × 3 = 9 that independence would suggest.

Task 5 — But does the error actually matter?

This is the question people skip. Run the join both ways:

docker exec -i -u postgres rbpg-lab01 psql -X -d lab13 <<'SQL'
CREATE TABLE deliveries(id int PRIMARY KEY, address_id int, delivered date);
INSERT INTO deliveries SELECT g, 1+(g%300000), date '2025-01-01'+(g%300)
  FROM generate_series(1,300000) g;
CREATE INDEX deliveries_address_idx ON deliveries(address_id);
SQL
docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "VACUUM ANALYZE deliveries;"

# With the extended statistics in place:
docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "
  EXPLAIN ANALYZE SELECT count(*) FROM addresses a
  JOIN deliveries d ON d.address_id = a.id
  WHERE a.country='GB' AND a.city='London';" | grep -E "Join|Execution Time"

# And without:
docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "DROP STATISTICS addresses_country_city;"
docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "ANALYZE addresses;"
docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "
  EXPLAIN ANALYZE SELECT count(*) FROM addresses a
  JOIN deliveries d ON d.address_id = a.id
  WHERE a.country='GB' AND a.city='London';" | grep -E "Join|Execution Time"

On the lab run of 2026-08-28 both produced a parallel hash join, in 28.357 ms and 28.831 ms respectively. The three-fold underestimate changed nothing.

Task 6 — An error large enough to break the plan

Three values per column produce a nine-fold worst case. A hundred values per column produce a ten-thousand-fold one.

docker exec -i -u postgres rbpg-lab01 psql -X -d lab13 <<'SQL'
CREATE TABLE sites(id int PRIMARY KEY, region text, zone text, payload text);
INSERT INTO sites SELECT g, 'region-'||(g%100), 'zone-'||(g%100), repeat('z',40)
  FROM generate_series(1,300000) g;
CREATE INDEX sites_region_idx ON sites(region);

CREATE TABLE readings(id int PRIMARY KEY, site_id int, val numeric);
INSERT INTO readings SELECT g, 1+(g%300000), (g%97)*1.5 FROM generate_series(1,2000000) g;
CREATE INDEX readings_site_idx ON readings(site_id);
SQL
docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "VACUUM ANALYZE sites, readings;"

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "
  SELECT count(DISTINCT region) AS regions, count(DISTINCT zone) AS zones,
         count(DISTINCT (region,zone)) AS combinations FROM sites;"

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "
  EXPLAIN ANALYZE SELECT count(*) FROM sites s
  JOIN readings r ON r.site_id = s.id
  WHERE s.region='region-7' AND s.zone='zone-7';" | tee "$LAB/plan-damage.txt"

$ EXPLAIN ANALYZE the join with a 103-fold underestimate at its base

 regions | zones | combinations 
---------+-------+--------------
   100 |   100 |          100
(1 row)

Aggregate  (cost=3649.90..3649.91 rows=1 width=8) (actual time=15.707..15.708 rows=1.00 loops=1)
 ->  Nested Loop  (cost=34.98..3649.43 rows=190 width=0) (actual time=0.415..15.055 rows=20000.00 loops=1)
       ->  Bitmap Heap Scan on sites s  (cost=34.55..3515.45 rows=29 width=4) (actual time=0.410..3.953 rows=3000.00 loops=1)
             Recheck Cond: (region = 'region-7'::text)
             Filter: (zone = 'zone-7'::text)
             Heap Blocks: exact=3000
       ->  Index Only Scan using readings_site_idx on readings r  (cost=0.43..4.55 rows=7 width=4) (actual time=0.003..0.003 rows=6.67 loops=3000)
             Index Cond: (site_id = s.id)
             Heap Fetches: 0
             Index Searches: 3000
Planning Time: 0.572 ms
Execution Time: 15.761 ms

The base scan estimated 29 rows and produced 3,000 — a factor of 103.

Look at what the planner did with that. It chose a Nested Loop, because running an index lookup 29 times is cheap. It then ran that index lookup 3,000 times: loops=3000, Index Searches: 3000.

The query still completed in 15.7 ms because this table is small and fully cached. On a table where each of those index lookups costs a disk seek, a 103-fold error in the loop count is the difference between a sub-second query and a several-minute one.

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "
  CREATE STATISTICS sites_region_zone (dependencies, ndistinct)
  ON region, zone FROM sites;"
docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "ANALYZE sites;"

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "
  EXPLAIN ANALYZE SELECT count(*) FROM sites s
  JOIN readings r ON r.site_id = s.id
  WHERE s.region='region-7' AND s.zone='zone-7';" | tee -a "$LAB/plan-damage.txt"

$ CREATE STATISTICS, ANALYZE, and re-run the identical join

         ->  Partial Aggregate  (cost=8933.07..8933.08 rows=1 width=8) (actual time=6.498..6.499 rows=1.00 loops=2)
             ->  Nested Loop  (cost=35.02..8905.50 rows=11029 width=0) (actual time=0.309..6.207 rows=10000.00 loops=2)
                   ->  Parallel Bitmap Heap Scan on sites s  (cost=34.59..3472.99 rows=1682 width=4) (actual time=0.295..2.136 rows=1500.00 loops=2)
                         Recheck Cond: (region = 'region-7'::text)
                         Filter: (zone = 'zone-7'::text)
Planning Time: 0.499 ms
Execution Time: 9.275 ms

statistics_name  |  n_distinct   |               dependencies               
-------------------+---------------+------------------------------------------
sites_region_zone | {"2, 3": 100} | {"2 => 3": 1.000000, "3 => 2": 1.000000}
(1 row)

The join estimate is now 11,029 against 10,000 actual, the base scan 1,682 against 1,500, and the planner chose to parallelise. Execution time fell from 15.761 ms to 9.275 ms on identical data and an identical query.

Task 7 — Stale statistics, and the part that self-corrects

docker exec -i -u postgres rbpg-lab01 psql -X -d lab13 <<'SQL'
CREATE TABLE events(id int PRIMARY KEY, kind text, ts timestamptz);
INSERT INTO events SELECT g, 'kind-'||(g%10), now() FROM generate_series(1,1000) g;
ANALYZE events;
SQL

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c \
  "SELECT reltuples::bigint, relpages FROM pg_class WHERE relname='events';" \
  | tee "$LAB/stale-stats.txt"

# Load two million rows and query immediately, with no ANALYZE.
docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c \
  "INSERT INTO events SELECT g+1000, 'kind-'||(g%10), now() FROM generate_series(1,2000000) g;"

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c \
  "SELECT reltuples::bigint AS reltuples_still_says, relpages FROM pg_class WHERE relname='events';" \
  | tee -a "$LAB/stale-stats.txt"

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c \
  "EXPLAIN ANALYZE SELECT count(*) FROM events WHERE kind='kind-3';" | grep -E "Parallel Seq Scan"

$ ANALYZE at 1,000 rows, bulk-load 2,000,000 more, then explain without re-analyzing

 reltuples | relpages 
-----------+----------
    1000 |        7
(1 row)

reltuples_still_says | relpages 
----------------------+----------
               1000 |        7
(1 row)

->  Parallel Seq Scan on events  (cost=0.00..22229.63 rows=75869 width=0) (actual time=0.103..88.190 rows=66700.00 loops=3)

This is not what the usual story predicts. reltuples says 1,000, and the planner estimated 75,869 per worker — 227,607 in total, against a true 200,100. A ratio of 1.14, on statistics that describe 0.05% of the current table.

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "
  SELECT relpages AS recorded_pages,
         pg_relation_size('events')/8192 AS actual_pages,
         reltuples::bigint AS recorded_tuples,
         (reltuples / relpages * (pg_relation_size('events')/8192))::bigint AS scaled_estimate
  FROM pg_class WHERE relname='events';"
docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "SELECT count(*) AS actual_rows FROM events;"

$ compare recorded relpages against the real file size, and scale reltuples by the ratio

 recorded_pages | actual_pages | recorded_tuples | scaled_estimate 
----------------+--------------+-----------------+-----------------
            7 |        12746 |            1000 |         1820857
(1 row)

actual_rows 
-------------
   2001000
(1 row)

The planner does not trust reltuples literally. It takes the density implied by the stored statistics — 1,000 tuples in 7 pages — and applies it to the table’s current physical size, which it measures rather than reads from the catalog. 1000/7 × 12,746 gives roughly 1.82 million against a true 2.0 million.

Task 8 — A distribution the statistics have never seen

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c \
  "INSERT INTO events SELECT g+3000000, 'kind-new', now() FROM generate_series(1,2000000) g;"

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c \
  "EXPLAIN ANALYZE SELECT count(*) FROM events WHERE kind='kind-new';" \
  | grep -E "Parallel Seq Scan|Rows Removed|Execution Time" | tee -a "$LAB/stale-stats.txt"

$ load 2,000,000 rows of an unseen value, then explain without analyzing

 ->  Parallel Seq Scan on events  (cost=0.00..46323.03 rows=1 width=0) (actual time=18.003..62.250 rows=666666.67 loops=3)
     Filter: (kind = 'kind-new'::text)
     Rows Removed by Filter: 667000
Execution Time: 140.946 ms

rows=1 against two million actual. A two-million-fold underestimate — six orders of magnitude worse than anything the correlated-column cases produced.

The value kind-new appears in no most-common-values list and in no histogram, because neither existed when ANALYZE last ran. The planner concluded the value essentially does not occur and estimated the smallest number it can.

Had this been the inner side of a join, the planner would have chosen a nested loop expecting one iteration and performed two million.

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "ANALYZE events;"
docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c \
  "EXPLAIN ANALYZE SELECT count(*) FROM events WHERE kind='kind-new';" \
  | grep -E "Parallel Seq Scan|Execution Time" | tee -a "$LAB/stale-stats.txt"

$ ANALYZE events, then re-run the identical query

 ->  Parallel Seq Scan on events  (cost=0.00..46323.54 rows=845822 width=0) (actual time=0.010..53.773 rows=666666.67 loops=3)
Execution Time: 76.480 ms

Task 9 — Read the statistics themselves

docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "
  SELECT attname, n_distinct, most_common_vals::text::text[] AS mcv, most_common_freqs
  FROM pg_stats WHERE tablename='events' AND attname='kind';"

$ psql -X -d lab13 -c "SELECT attname, n_distinct, most_common_vals::text::text[] AS mcv, most_common_freqs FROM pg_stats WHERE tablename='events' AND attname='kind';"

 attname | n_distinct |                                       mcv                                        |                                           most_common_freqs                                            
---------+------------+----------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------
kind    |         11 | {kind-new,kind-2,kind-6,kind-0,kind-3,kind-7,kind-4,kind-1,kind-5,kind-8,kind-9} | {0.50736666,0.051766668,0.0515,0.0509,0.050566666,0.049933333,0.0488,0.0478,0.047566667,0.0471,0.0467}
(1 row)

kind-new is now first in the list with a frequency of 0.507 — the sampled proportion of the table. That single entry is what turned a rows=1 estimate into rows=845822.

docker exec -u postgres rbpg-lab01 psql -X -c \
  "SELECT name, setting, boot_val, context FROM pg_settings WHERE name='default_statistics_target';"
docker exec -u postgres rbpg-lab01 psql -X -d lab13 -c "
  SELECT attname,
         array_length(most_common_vals::text::text[],1)  AS mcv_entries,
         array_length(histogram_bounds::text::text[],1)  AS histogram_entries
  FROM pg_stats WHERE tablename='events';"

$ read default_statistics_target, then count MCV and histogram entries per column

           name            | setting | boot_val | context 
---------------------------+---------+----------+---------
default_statistics_target | 100     | 100      | user
(1 row)

attname | mcv_entries | histogram_entries 
---------+-------------+-------------------
id      |             |               101
kind    |          11 |                  
ts      |           3 |                  
(3 rows)

default_statistics_target = 100 is a budget: at most 100 most-common values and around 100 histogram buckets per column, from a sample of roughly 300 times the target in rows.

Note how the budget is spent differently per column. kind has 11 distinct values, so all 11 fit in the MCV list and no histogram is needed. id is unique, so there is no point listing common values and the whole budget goes to a 101-boundary histogram.

Validation

test -s "$LAB/independence.txt"  && echo "OK independence"
test -s "$LAB/extended-stats.txt" && echo "OK extended-stats"
test -s "$LAB/plan-damage.txt"    && echo "OK plan-damage"
test -s "$LAB/stale-stats.txt"    && echo "OK stale-stats"

grep -q "rows=33627" "$LAB/independence.txt"  && echo "OK independence error reproduced"
grep -q "1.000000"   "$LAB/extended-stats.txt" && echo "OK dependency learned"
grep -q "loops=3000" "$LAB/plan-damage.txt"    && echo "OK plan damage captured"
grep -q "rows=1 "    "$LAB/stale-stats.txt"    && echo "OK unseen-value estimate captured"

Questions to answer without looking anything up:

1. Two predicates are each estimated perfectly and their conjunction is estimated at a ninth of the truth. What assumption produced that, and in which direction is the error always?
2. You find a node estimating 29 rows that produces 3,000. What is the next question to ask before creating extended statistics?
3. A table has grown 2,000 times since its last ANALYZE. Why might its cardinality estimates still be reasonable?
4. The same table then receives two million rows of a value never seen before. What estimate does the planner produce, and why?
5. A column’s MCV list has 40 entries and default_statistics_target = 100. Would raising the target help?

Expected Outcome

You have reproduced both mechanisms that make planner estimates wrong, computed one of them by hand and matched the planner exactly, and seen the range of consequences: from a three-fold error that changed nothing to a two-million-fold error that would wreck any join built on it.

The diagnostic order:

1. Read the plan and find the deepest node with a bad estimate ratio.
2. Is the table analyzed? last_analyze and last_autoanalyze in pg_stat_user_tables. If not, that is the answer.
3. Is the predicate on a value the statistics know about? Check most_common_vals and the histogram.
4. Are there multiple predicates on correlated columns? That is CREATE STATISTICS.
5. Would fixing it change the plan? If not, stop.

Troubleshooting

Both single-column estimates are already wrong. The table has not been analyzed. ANALYZE it and start again — Task 2 depends on the single-column estimates being good, so that Task 3 isolates the correlation effect rather than a statistics-staleness effect.

CREATE STATISTICS has no effect. The object exists but has never been populated; extended statistics are gathered by ANALYZE, so run ANALYZE t; after creating it. Confirm with SELECT * FROM pg_stats_ext; that the dependency was actually computed.

CREATE STATISTICS fixed the estimate and the plan did not change. That is Task 5’s result and it is the honest one: measured here, a correlated-column estimate error changed no plan at all (28.357 ms against 28.831 ms). An estimate error only matters if it changes a decision. Fixing estimates that change nothing is effort spent for no return.

most_common_vals is empty for a column you know has skew. The statistics target is too low for the distribution, or the column has more distinct values than the target can hold. Raise it per column with ALTER TABLE t ALTER COLUMN c SET STATISTICS 1000; and re-analyze.

The plan changed back after you did nothing. Autoanalyze ran. That is the self-correcting part in Task 7, and it is why “it was slow this morning and it is fine now” is such a common and unsatisfying report — check last_autoanalyze.

Row estimates are wrong for a value the statistics have never seen. That is Task 8. For a value outside the histogram the planner falls back to a generic estimate, which is why a query that is fine for existing data can be badly planned for a value inserted five minutes ago.

Cleanup

docker exec -u postgres rbpg-lab01 psql -X -c "DROP DATABASE IF EXISTS lab13;"

Production notes

• Check last_analyze and last_autoanalyze before doing anything else with a bad estimate. Stale statistics account for more estimate errors than every other cause combined, and the fix is one command.
• Add extended statistics only where the estimate error changes a plan. They cost ANALYZE time on every run, and this lab measured a case where correcting a three-fold error changed nothing measurable.
• After a bulk load, ANALYZE explicitly rather than waiting for autoanalyze. The window between the load finishing and the statistics catching up is exactly when the newly loaded values are outside the histogram and estimated worst.
• Raise default_statistics_target per column, not globally. A higher target on one skewed column costs a little; on every column in the cluster it costs ANALYZE time on all of them.
• Keep the plan you captured before a deployment. “The plan changed” is only a claim you can make if you have the earlier one.

What You Learned

• The planner multiplies selectivities, assuming independence, which is why two correlated predicates produce an error neither produces alone — and you computed the same number the planner did, by hand.
• CREATE STATISTICS teaches it the dependency, but only after ANALYZE populates the object.
• An estimate error matters only if it changes a plan. A three-fold error changed nothing here; a two-million-fold error would wreck any join built on it.
• Stale statistics self-correct when autoanalyze runs, which is why the incident is often over before anyone looks.
• A value the statistics have never seen gets a generic estimate, so freshly inserted data is the data most likely to be planned badly.
• pg_stats is readable. n_distinct, most_common_vals, null_frac and the histogram are the planner’s whole input, and you can check them directly instead of inferring.