Engine-Level Query Routing: EXPLAIN ROUTE PLAN
Also in Chinese: 中文版
Why the decision has to happen before execution
After the migration consolidated serving and analytics onto one Apache Doris deployment, fewer than 1% of queries were heavy — but a single misplaced heavy query could starve the shared light pool. Reactive elasticity cannot be the safety mechanism here: scaling a compute pool is a minutes-scale operation, while a memory blow-up is a seconds-scale event. The only place the routing decision can safely happen is at plan time, before any backend touches data.
The first question was whether stock Doris already exposed enough signal. I built a closed-loop feasibility pipeline (SQL in, EXPLAIN parsed and classified, real execution as ground truth) over a 52-query suite triangulated three ways: a real ~1,900-column schema, the platform’s actual production SQL templates, and the business query taxonomy. Stock EXPLAIN routed the suite at 45% accuracy with a 27% heavy-miss rate; the worst miss ran 188 s and consumed 800 MB, roughly 10% of one backend’s memory, in a single query.
Two measurements settled the design direction. Identical query templates with different parameters (a time-window sweep from 1 hour to 36 days) produced byte-identical EXPLAIN text while real memory differed 23× and wall time 6×. And a point lookup aimed at the highest-cardinality key ran in 32 ms while EXPLAIN predicted a full table scan. The workload is fixed templates with parameter-driven cost swings of 50×–2,000×, which makes static hints structurally unable to solve the problem: any labeling scheme keyed on query text sees the same bytes for wildly different costs.
How: expose what the optimizer already computes
The gap was a signal problem, not a rule problem. Doris’s cost-based optimizer already computes per-operator cardinality, selectivity, average row width, scan bytes, and sort/aggregate state sizes while building the physical plan; it folds them into one opaque cost number and discards them. The first change, on an Apache Doris fork, was EXPLAIN ESTIMATE PLAN: a read-only visitor (PlanEstimateCollector) that walks the finalized physical plan and emits those per-operator estimates as structured JSON. The diff is purely additive — zero deletions, no changes to the cost model or statistics calculator (15 files, +1,310 lines in PR form). The first unit test returned estimated_filter_selectivity = 0.9867, exactly 4933/5000 — evidence this is exposing existing math, not building new estimation logic.
EXPLAIN ROUTE PLAN is that estimate plus one step: a 15-rule classifier ported from a Python reference into Java, returning a verdict JSON — label, confidence, target compute group, target workload group, transport — in 6–8 ms, independent of data volume (13M and 102M rows both plan in ~8 ms, because the estimate reads cached CBO statistics with zero I/O). The integration followed the seams of the prior feature exactly: a ROUTE token in the ANTLR lexer, a planType alternative in the parser grammar, one new ExplainCommand enum case, one token-to-enum mapping, one new branch in the planner’s explain path, and two new classes. All ~21 routing thresholds are declared @ConfField(mutable=true) and hot-reloadable via ADMIN SET FRONTEND CONFIG, with defaults equal to the Python constants — threshold tuning never requires a rebuild.
On a preprod cluster at production scale, heavy recall on the canonical suite went 7% → 100% (15/15) and light precision reached 86%; six of the heaviest real production queries (up to 76 GiB / ~3 B rows scanned) routed 6/6 with zero heavy misses. The classifier is permitted to be imperfect — a misrouted heavy query is contained by a runtime memory hard limit and escalated, a separate layer covered in the main case study.
Two blind spots in the cost model, found by re-benchmarking
Both misclassifications found in the second benchmark round were the same species of bug: the estimate was not slightly off, it was structurally blind.
A bounded window query that looked like a full-table scan. A point query carrying a window function — WHERE user_id = X ... ROW_NUMBER() OVER (PARTITION BY user_id) LIMIT 1000 — routed heavy. The CPU-blocking rule keyed on the raw largest leaf-scan cardinality, so one user’s ~840K rows read as a full-table window, even though the filter bounded the window’s input and the query actually cost 0.8 s of CPU and 12–28 MB peak memory. The fix emits input_cardinality on WINDOW and PARTITIONTOPN operators and gates the CPU rule on that signal, falling back to the raw cardinality when it is absent. Genuine full-table windows still route heavy.
A wide SELECT * the byte model called cheap. Output volume, modeled as rows × average row bytes, badly under-prices a wide read, because the cost of a wide read is not bytes — it is cold-opening thousands of column files from object storage. Measured at production scale: a 3,727-column SELECT * took 253 s cold. The fix adds output_column_count — taken straight from plan.getOutput().size(), a structural fact rather than an estimate — plus one hot-reloadable bar (default 200 columns) that routes a wide result heavy with an output_materialization reason and switches its transport to Arrow Flight. Both fixes are backward-compatible: when the new estimate field is absent, the rule falls back to prior behavior.
“Why not just fix the SQL?” — the fair objection, and answering it is the thesis of this project. SELECT * on a 3,700-column table is bad practice, and callers are told so. But a platform cannot make its safety contingent on every caller being disciplined, least of all now, when a growing share of queries is generated by product features and AI agents rather than written by hand and reviewed. And independent of anyone’s SQL style, an estimator that labels a 253-second query light is wrong on its own terms: that is a defect in the cost model, and it will mis-price the next unfamiliar shape too. Fixing the SQL removes one bad query. Fixing the classifier removes the failure class where any bad query lands in the pool sized for 20 ms point lookups. One is a remedy, the other is a property.
The same reasoning later drove a v3 simplification: rules resting on CBO byte estimates were dropped in favour of structural signals — operator shape, output column count, bounded input — because a signal you can trust to be a fact beats a better-looking number you cannot.
The hard parts
Cross-language parity. The 15 rules existed as a Python implementation; the port had to run in the FE’s hot path in Java. Two independent implementations of routing policy will silently drift, and one off-by-one threshold sends a heavy query into the light pool. The fix was to treat the Python implementation as an executable specification, not documentation: a 197-case golden corpus of real estimate JSON is fed to both sides, asserting label-for-label agreement — 197/197 passed before any cluster or image existed. The most fragile surface was tolerant-accessor semantics: null, -1, and “unknown” must map to the same tri-state in both languages, and “field absent” must never be conflated with “value is zero” — that distinction is exactly where a cross-language port fails silently.
Build economics forcing verification design. A full FE build under QEMU-emulated amd64 took 43:55 min per successful run. That constraint forced a layered strategy: cheap, high-signal verification first (the parity harness runs with standalone javac against built jars, at zero build cost), expensive, low-signal verification last (full build, image, cluster smoke test). Native arm64 compilation later cut builds to ~3 min, and an overlay Dockerfile cut image pushes from 747 MB to 43 MB / 3.8 s. Combined with hot-reloadable thresholds, “change a tuning value” went from a 44-minute rebuild to one SQL statement.
Bugs the parity net cannot catch. Parity proves Java equals Python; it does not prove the specification itself is correct. After rollout, a LIMIT blindspot surfaced: a LIMIT anywhere in the plan suppressed the “scan too large” heavy signal, but LIMIT only bounds the output of blocking operators such as hash aggregation — it does not bound the underlying scan. The bug was caught by cross-validating classifier verdicts against real production behavior, fixed structurally (blocking operators no longer exempt a query; pure scan-plus-limit still does), and fed back into the golden corpus as a new fixture. Spec-level bugs need production cross-validation; the corpus then keeps them fixed.
Deciding what not to upstream. Leadership initially wanted the classifier contributed upstream along with the estimate mechanism. A line-by-line read of the classifier produced seven concrete business couplings: workload-group-anchored memory thresholds, calibration constants such as a 50M-row heavy cutoff, a recall-first risk posture, company-specific query shapes, and private fork fields. The counter-argument used the first PR’s own principle — the engine should emit data; callers decide policy — so upstreaming policy would contradict the mechanism’s own pitch. The outcome: EXPLAIN ESTIMATE PLAN was prepared as a clean 8-commit PR against a synthetic base branch so the diff is exactly the feature; the classifier was archived internally and never proposed for merge. That argument changed the decision.
Takeaways
- Quantify the gap before touching an engine. The load-bearing number was the 27% heavy-miss rate — a safety problem — not the 45% accuracy headline.
- Prefer exposing existing internal math to building new estimation. A zero-deletion diff is itself the risk argument.
- Judging what not to contribute is part of the contribution. Mechanism belongs upstream; calibrated policy does not.