Optimizer Statistics: Why Your Plan Flipped Overnight on MySQL and PostgreSQL

Monday, 09:15, and the weekly revenue report that runs in four seconds was timing out at ninety. No deploys over the weekend. No data imports. Same query, same parameters, and on the PostgreSQL side of our dual-run the plan had changed from an index range scan to a sequential scan over a 90-million-row events table. Two months earlier, the mirror image had happened on the MySQL original of the same report: an ANALYZE TABLE run by a well-meaning scheduled job flipped a hot lookup from an index dive to a full index scan, and we spent a morning blaming the application release before anyone looked at the plan.

Both incidents were statistics, not plans. The planner is a function of its inputs, and when the inputs change, the output changes with no code motion at all. MySQL and PostgreSQL both maintain optimizer statistics, but they sample different things, store different shapes of evidence, refresh on different triggers, and fail in different ways. If you operate both, or you are mid-migration, you need both mental models, because "run ANALYZE" is not one procedure; it is two.

This article covers how each engine gathers statistics, what the numbers actually mean, the failure modes I have met in production, and the fixes that address the estimate instead of forcing the plan.

What does each engine actually sample?

PostgreSQL samples rows. ANALYZE reads a random sample of each table, default_statistics_target times 300 rows per column (30,000 rows at the default target of 100), and builds per-column evidence: most common values with their frequencies, a histogram of the rest, a null fraction, and a correlation figure between physical and logical order. You can inspect all of it:

SELECT attname,
       null_frac,
       n_distinct,
       array_length(most_common_vals::text[], 1) AS mcv_count,
       correlation
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename  = 'events'
ORDER BY attname;

MySQL samples index pages. ANALYZE TABLE does not read rows; it dives into each index at random pages and extrapolates key cardinality from what it finds there, innodb_stats_persistent_sample_pages pages per index (20 by default) when persistent statistics are enabled, which they have been by default since 5.6. The results land in mysql.innodb_table_stats and mysql.innodb_index_stats, visible through information_schema:

SELECT t.table_name,
       s.index_name,
       s.stat_name,
       s.stat_value
FROM information_schema.innodb_table_stats t
JOIN information_schema.innodb_index_stats s
  ON s.database_name = t.database_name
 AND s.table_name    = t.table_name
WHERE t.table_name = 'events'
  AND s.stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'size');

The difference in what gets sampled explains the difference in what goes wrong. PostgreSQL's row sample tells it about value distribution inside a single column: skew, common values, histogram shape. MySQL's index dive tells it about key prefix cardinality: how many distinct values the index's first column has, roughly. MySQL historically knew nothing about non-indexed column distribution, and it still knows nothing about correlations between columns. PostgreSQL knows single-column distribution well and, by default, also nothing about correlations. Keep those two sentences; the rest of this article is their consequences.

Why did the PostgreSQL report flip to a sequential scan?

Because autoanalyze ran over the weekend on a freshly grown table, and the new sample produced a selectivity estimate that crossed a costing threshold. The report predicates were on event_type and a created_at range. Over the weekend a partner backfilled several million rows of one event type, autoanalyze kicked in Sunday night, and Monday's estimate for event_type = 'partner_sync' said "about a third of the table," at which point the planner quite rationally chose a sequential scan. The estimate was wrong because the histogram bounds and the range predicate interacted badly on a column whose recent values clustered at the top, but the planner had no way to know that.

The diagnostic habit that resolves these in minutes instead of mornings is comparing estimated rows to actual rows at the node where they diverge:

EXPLAIN (ANALYZE, BUFFERS)
SELECT date_trunc('day', created_at) AS day, COUNT(*)
FROM events
WHERE event_type = 'partner_sync'
  AND created_at >= now() - interval '28 days'
GROUP BY 1
ORDER BY 1;
-- look for: rows=38000000 estimated vs actual rows=412000

An estimate off by two orders of magnitude at a scan node is a statistics problem. The fixes, in order of preference: raise the statistics target on the skewed column with ALTER TABLE events ALTER COLUMN event_type SET STATISTICS 500 and ANALYZE the table; make sure autoanalyze is not starved on fast-growing tables; and only then consider restructuring the query. The deeper version of this workflow lives in the planner statistics guide. What you should not do is reach for a plan-forcing extension on day one, because the next data shift will invalidate the frozen plan just as thoroughly as it invalidated the stale estimate.

Why did the MySQL lookup flip after ANALYZE TABLE?

Because 20 random index pages is a tiny sample, and on a table with lumpy key distribution the cardinality estimate can swing wildly between runs of ANALYZE TABLE. Our lookup filtered on a (tenant_id, status) composite where one tenant owned forty percent of the rows. The optimizer was choosing between the composite index and the primary key range it would have scanned otherwise, based on a cardinality estimate that moved by a factor of five depending on which pages the sample happened to hit. The scheduled ANALYZE TABLE job was re-rolling the dice every Sunday.

Three knobs stabilize this. First, raise the sample size for the tables where it matters: innodb_stats_persistent_sample_pages can be set per table, and 200 pages is a cheap insurance premium on a big, skewed index. Second, stop re-sampling healthy tables on a schedule; InnoDB re-estimates automatically when about a tenth of the table changes, and for most tables that is enough. Third, and most importantly for skew: MySQL 8.0 added real column histograms, which give the optimizer distribution evidence it never had before:

-- MySQL 8.0: distribution evidence for a skewed column
ANALYZE TABLE orders
  UPDATE HISTOGRAM ON status WITH 32 BUCKETS;

-- check what was stored
SELECT histogram
FROM information_schema.column_statistics
WHERE schema_name = 'shop' AND table_name = 'orders';

Histograms in MySQL are per-column, stored in the data dictionary, and used for filtering selectivity on non-indexed or non-prefix columns, which is exactly the gap index dives could never cover. They are also the fix I see MySQL teams reach for last, usually after months of fighting plan instability with index hints. If a range predicate on a skewed column is choosing badly on MySQL 8.0, a histogram is the first thing to try, and the index statistics and index dives article covers the mechanics underneath it.

How do you fix correlated-column estimates on each engine?

This is where PostgreSQL pulls decisively ahead, because MySQL has no answer. The classic failure is two correlated predicates, city and country, where the planner multiplies individual selectivities and underestimates by orders of magnitude because it assumes independence. PostgreSQL's answer is extended statistics, which gather cross-column evidence explicitly:

CREATE STATISTICS addresses_geo (dependencies, ndistinct, mcv)
  ON country, city
FROM addresses;

ANALYZE addresses;

Dependencies fix the functional case (city implies country), ndistinct fixes join cardinality estimates on correlated keys, and the multi-column MCV list, available from PostgreSQL 12, fixes correlated filter selectivity. The production patterns are covered in the extended statistics piece; the short version is that one CREATE STATISTICS turned a 600x misestimate on our geo report into a plan that survived the next year of data growth.

On MySQL, there is no cross-column statistics object, full stop. The compensations are structural: a composite index on the correlated pair, so the index cardinality itself encodes the correlation; a histogram on the leading column; or accepting the misestimate and testing whether it actually hurts. This asymmetry matters in migration planning: any MySQL query that leans on a composite index to paper over correlated predicates is a query to re-test on PostgreSQL, because the equivalent fix there is a statistics object, not necessarily an index, and the planner will not invent it for you.

What about statistics freshness and migration?

Both engines refresh statistics automatically, and both automatic mechanisms have gaps that show up exactly when data changes shape. PostgreSQL's autoanalyze fires on a threshold of changed rows, autovacuum_analyze_threshold plus a fraction of the table, which means a huge table can go a long time between analyses even as its composition shifts; per-table scale factors are the tuning lever. MySQL re-estimates on roughly ten-percent change with persistent stats, and on top of that, certain operations such as a table rebuild silently reset or invalidate what was there. During a migration, treat statistics as part of the data: ANALYZE every large table on the target after the load, on both engines, before you let anyone benchmark anything. A fresh load on PostgreSQL without ANALYZE leaves the planner with no row sample at all and default guesses; a fresh load on MySQL without ANALYZE TABLE leaves it with whatever the bulk insert path recorded, which on some load methods is stale from the first second.

One more migration trap: statistics targets and sample sizes are tuning state, and they do not travel. If your MySQL shop raised innodb_stats_persistent_sample_pages on three skewed tables and nobody wrote it down, the PostgreSQL side will not inherit the intuition; you have to rediscover which columns need SET STATISTICS 500 from the EXPLAIN evidence. Keep a list. Ours lives next to the schema, and every entry has the incident number that justified it.

How MonPG catches plan flips before your users do

Statistics problems announce themselves as plan changes, and plan changes announce themselves as latency changes on a statement that did not change. That is a monitoring question: does the normalized query's performance history show a step change with no deploy to explain it? MonPG's PostgreSQL monitoring keeps pg_stat_statements history per normalized query, so the Monday-morning flip shows up as a line that jumped over the weekend, next to the autoanalyze activity that explains it. Diagnosis in minutes, not mornings.

MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the counters this article leaned on are the ones it will surface: per-statement digest timing from performance_schema, so a plan flip on MySQL is visible as a timing step change on an unchanged digest, alongside the index statistics state that explains why. Until then, the MySQL monitoring page tracks that work, and the discipline transfers: when a plan changes with no deploy, look at what the estimator was told before you touch what the estimator decides.