MariaDB ANALYZE FORMAT=JSON: When the Optimizer's Row Estimates Lie to You

The query was a weekly revenue rollup, and its behavior made no sense on paper: 40 milliseconds Tuesday through Sunday, 41 seconds on Monday, same parameters, same tables, and — here is the part that sent me down the wrong path for a day — byte-identical EXPLAIN output on a fast day and a slow day. The plan was not changing. What was changing was the data shape: Monday's run covered the weekend's orders, and one regional warehouse shipped 70% of weekend volume, so a predicate the optimizer assumed would match about 900 rows actually matched 1.8 million. The estimate was wrong by a factor of 2,000, every join order downstream of it was wrong with it, and EXPLAIN never said a word, because EXPLAIN only shows you what the optimizer believes. ANALYZE shows you what actually happened. Running ANALYZE on a Monday copy of the query took ninety seconds and ended the investigation: one line of output, rows 900, r_rows 1847261, and the whole mystery collapsed into a stale-statistics problem.

EXPLAIN FORMAT=JSON, optimizer trace, and the slow log all have their place, but ANALYZE is the tool that closes the gap between belief and reality, and MariaDB has shipped it since the 10.1 era — long enough that there is no excuse for it to be missing from a DBA's muscle memory. This is how I use it: what it does that EXPLAIN cannot, how to read the r_ columns, what FORMAT=JSON adds, and what to do once you have found the lie.

What does ANALYZE do that EXPLAIN cannot?

ANALYZE executes your query for real, measures what happens at every step of the plan, and then shows you the plan annotated with actuals — where EXPLAIN shows you the optimizer's predictions without running anything. That distinction is the entire value. A plan can look perfectly reasonable and be catastrophically wrong because one estimate deep inside it is off by three orders of magnitude; the only way to see that is to compare what the optimizer expected with what the storage engine actually delivered, node by node.

-- run the query under measurement: plan plus ACTUALS
ANALYZE
SELECT o.region, SUM(oi.amount)
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.order_date >= '2026-07-27' AND o.order_date < '2026-08-03'
  AND o.warehouse_id = 7
GROUP BY o.region;

-- tabular output adds two column families next to EXPLAIN's own:
--   rows  / r_rows      -> estimated rows vs ACTUAL rows read
--   filtered / r_filtered -> estimated vs ACTUAL selectivity

The caution that must travel with the power: ANALYZE really runs the statement. A 41-second query costs you 41 seconds of server resources every time you measure it, and a query with side effects is out of scope entirely — point it at SELECTs, and run the heavy ones on a replica or a restored copy. That is not a reason to avoid it; it is a reason to be deliberate about where. Our habit is to capture the exact slow query from the slow log, replay it under ANALYZE on the reporting replica, and only then form opinions. Guessing at plans from EXPLAIN alone is how I lost that first day.

How do you read rows versus r_rows?

rows is the optimizer's estimate of how many rows a plan node will produce; r_rows is how many rows it actually produced, averaged over the times that node executed. The ratio between them is your estimation error, and it is the single most diagnostic number in the output. A node with rows=900 and r_rows=1847261 — my Monday warehouse predicate — is an estimate wrong by 2,000x, and everything the optimizer decided on top of that number (this table is the small side of the join, nested loop is cheap, no need for a better index path) is built on sand.

Reading order matters. Start at the leaves of the plan, not the root: estimation errors compound outward, so the first node where rows and r_rows diverge badly is usually the original sin, and the inflated numbers above it are just consequences. Then check r_filtered against filtered the same way — a node that expected to keep 10% of rows and actually kept 0.01% tells you a predicate is far more selective than the statistics believe, which points at skew or correlation between columns. The usual causes, in the order I find them in production: stale or missing table statistics, skewed value distributions that flat statistics cannot represent, predicates that wrap columns in functions so statistics do not apply at all, and correlated columns the optimizer assumes are independent. My Monday query was the second cause wearing the first one's clothes — the statistics were recent, they just could not express "one warehouse dominates weekends."

What does FORMAT=JSON add to the picture?

ANALYZE FORMAT=JSON gives you the same measured plan as a nested document with per-node timing — including r_total_time_ms and loop counts — so you can see not just where the row estimates lied but where the wall-clock time actually went. The tree structure matters for real queries: tabular output flattens the plan into rows you have to mentally reassemble, while the JSON keeps subqueries, derived tables, and join nesting in their true shape:

ANALYZE FORMAT=JSON
SELECT o.region, SUM(oi.amount)
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.order_date >= '2026-07-27' AND o.order_date < '2026-08-03'
  AND o.warehouse_id = 7
GROUP BY o.region;

-- in the JSON, walk the tree looking for two things:
--   r_total_time_ms  -> which node ate the 41 seconds
--   r_rows vs rows   -> which node's estimate poisoned the plan

In practice I use the two formats as a pair: tabular ANALYZE for the fast scan of rows-versus-r_rows ratios, FORMAT=JSON when the plan is deep enough that I need the timing attribution to find the expensive subtree. The JSON is also the format worth pasting into tickets and postmortems — it is self-describing, it preserves the actuals alongside the estimates, and it survives being read six months later by someone who was not in the incident.

How do you fix the plan once you have found the bad estimate?

Fix the information the optimizer reasons from, in this order: refresh the statistics, add histograms for skewed columns, rewrite predicates that hide columns from statistics, and only then reach for hints or optimizer_switch. ANALYZE TABLE is the first move — it recomputes the index cardinality and table statistics the planner consumes, and on tables with heavy write churn it can be stale within days. For skew like my warehouse column, flat statistics are structurally blind, and that is what engine-independent histogram statistics exist for: ANALYZE TABLE ... PERSISTENT FOR with histograms on the skewed columns gives the optimizer a real distribution to reason from. The mechanics, sizing, and failure modes of that are covered in the histogram statistics field notes, and the underlying machinery in engine-independent statistics; my Monday query was fixed permanently by a histogram on warehouse_id plus a statistics refresh schedule, no query change at all.

The rewrites come next: a predicate like WHERE DATE(created_at) = '2026-08-01' defeats every statistic on created_at, and the honest fix is comparing the bare column against a range. Hints and optimizer_switch tweaks are the last resort — they pin today's data shape into the query and rot silently as the data moves. If you find yourself reaching for them regularly, read the divergences in optimizer differences from MySQL first, because several knobs share names with MySQL's and behave differently enough to matter. And keep the slow log feeding you candidates: the workflow in the slow query log digest workflow is how Monday's query got onto my desk in the first place — ANALYZE is the microscope, the digest is what tells you where to point it.

Where MonPG fits

The signals worth trending here are the ones that find estimation rot before users do: per-query latency distributions that bimodal on schedule (fast weekdays, slow Mondays is a statistics smell), statement digest latency drift after bulk loads, and statistics freshness as an operational metric in its own right. Full disclosure, as in every article of this series: I work on MonPG, which monitors PostgreSQL in production today and does not monitor MariaDB yet. MariaDB support is coming soon and in active development — the /mariadb-monitoring page tracks where it stands — and plan-level observability is on the list of signals it is being built around: estimate-versus-actual drift surfaced per query instead of reconstructed by hand during an incident. Until that ships, ANALYZE on a replica is your microscope. If PostgreSQL is also in your fleet, that monitoring is live today — see the PostgreSQL overview, or browse more field notes on the blog.