PlanetScale — Optimizing query planning in Vitess, a step-by-step approach¶

Summary¶

Andrés Taylor's 2023-06-01 architecture-retrospective post walks through the new model the Vitess team adopted for the VTGate query planner — a model in which every step in the optimisation pipeline produces a runnable plan, each step is a local tree transformation on an operator tree, and the old monolithic "horizon planning" step is replaced by a fixed-point tree rewriter that iteratively expands and pushes down a new Horizon operator until nothing changes. The post frames this as the architectural inversion that enabled every later Vitess planner improvement (push-aggregation-under-join, later phase-gated rewriter fixes, arbitrary expressions in ORDER BY / GROUP BY / aggregations) and introduces a new pipeline stage — Offset planning — between horizon planning and executable-plan emission.

Key takeaways¶

VTGate is a distributed query planner, not a router. "VTGate is the proxy component of Vitess. It accepts queries from users and plans how to spread the query across multiple shards and/or keyspaces. The leaf level of the VTGate query plans are routes, which are operators that will send a query to one or more shards." The pushdown discipline is explicit: "The aim is always to push as much as possible down to the much faster MySQL process. This approach helps to offload processing to MySQL and keep the VTGate layer efficient. This also reduces the risk of compatibility differences between Vitess and plain MySQL, since MySQL is doing most of the work." (Source: this article.)
The old planner was monolithic; the new planner is incremental. "In the old model, we performed the optimization more in a top-down approach — we planned the full aggregation, and all ordering needed to support it, in one go. We would start with the join order tree, and do a lot of logic, and then output a new tree that performed the correct aggregations. In between the two, most of the current state was kept in arguments, local variables, and in the stack." The post calls out that this made horizon planning opaque to inspection — "blurred" in the article's own diagram — because intermediate state only existed on the Go call stack, not as an addressable plan tree.
The new model's load-bearing invariant: runnable plan at every step. "In this new query planning model, every step in the optimization pipeline results in a runnable plan. This means that developers working on the planner can inspect the plan at any stage, allowing for a better understanding of the optimization process at each step. By having runnable plans at every step, it becomes easier to identify potential issues, inefficiencies, or areas where further optimization is possible." This is the runnable-plan-at-every-step invariant — canonicalised by this post as the core architectural property separating the new Vitess planner from the old one.
Each step is a two-operator tree transformation. "Each step is also simpler — it's a tree transformation taking two operators as input, one being the input of the other, and producing a new subtree that replaces the two inputs." The unit of planner work is a local rewrite on an operator + its child, not a global reshuffle of the plan. This is what lets the planner use a single fixed-point loop over rewriters: each rewriter has a narrow precondition and a narrow effect, so rewriter composition is tractable.
The Horizon operator is the new "deferred horizon planning" primitive. "A 'horizon' operator contains the SELECT expressions, aggregations, ORDER BY, GROUP BY, and LIMIT. If we can push the entire operator to MySQL, we don't need to plan this at all. If we can't delegate it to MySQL in a single piece, we have to plan these components separately." The Horizon is a placeholder in the operator tree — it represents "all the things MySQL would do after the FROM clause" and exists so the planner can try to push it down as a whole before expanding it into its constituent operators. If pushdown fails, the Horizon is expanded in place into Ordering + Projection + Aggregator + Filter + Limit, each of which can then be pushed down independently.
Offset planning is a new pipeline stage. The article's own diagram shows the new pipeline has four stages: Parse → Determining Join Order → Horizon Planning (now recursive, with a self-loop) → Offset Planning → Executable Plan. Offset planning is named explicitly as "one new step, 'Offset Planning', that is between Horizon Planning and Executable Plan" — the post does not deeply unpack what offset planning does, but its placement after horizon planning implies it resolves symbolic column references (e.g. u.baz inside an Ordering expression) to the concrete positional offsets the execution engine needs to index into result rows. This is canonicalised on the wiki as concepts/offset-planning.
Arbitrary expressions in ORDER BY / GROUP BY / aggregations. "The new query planning model allows for arbitrary expressions to be used for ordering, grouping, and aggregations. This provides greater flexibility when crafting complex queries and enables developers to write more efficient and optimized queries. In comparison, the old model had limitations in terms of the expressions that could be used in these operations." The capability unlock — users can now write ORDER BY DATE(created_at) or GROUP BY u.foo + u.bar and the planner will evaluate the expression via evalengine at VTGate if MySQL can't push it down. This is the end-user-visible consequence of the architectural change.
Differential-execution equivalence as a design property. "Another benefit of this model is the possibility of running both the unoptimized plan and the optimized version and comparing their results. It should not matter if we have to evaluate a WHERE predicate on the VTGate side with our excellent evalengine support for most MySQL expressions, or if we can delegate it to the underlying database. The result should be the same." This is the correctness-testability property unlocked by the runnable-plan invariant: because every intermediate plan is executable, the planner's optimisation steps can be tested by running the un-rewritten plan + the rewritten plan + comparing row sets. Pairs with the MySQL-compatible differential fuzzing canonicalised in the sibling Fall-2023 fuzzing post.
Worked example: a cross-shard join + ORDER BY. The post walks through SELECT u.foo, ue.bar FROM user u JOIN user_extra ue ON u.uid = ue.uid ORDER BY u.baz through four tree snapshots — initial tree (Horizon over ApplyJoin); Horizon expanded into Ordering + Projection + ApplyJoin; Projection split + pushed to both join sides, Ordering pushed to the LHS; final tree with Ordering + Projection both pushed into the Route on the LHS. The final plan issues two queries: LHS — SELECT u.foo, u.uid, u.baz, weight_string(u.baz) FROM user AS u ORDER BY u.baz ASC (full scatter to all user shards, ordered at MySQL); RHS — SELECT ue.bar FROM user_extra AS ue WHERE ue.uid = :u_uid (per-row lookup bound to the join key). VTGate then merges the results via a nested-loop join — "the VTGate plan is ultimately just a join. One query will be sent to the left-hand side, and for each row we get from those results, we will issue a query on the right-hand side of the join." Canonical worked example for the VTGate nested-loop join emission.

Systems extracted¶

systems/vitess — the parent distributed database; planner lives in VTGate.
systems/vtgate — the proxy tier where the planner runs.
systems/mysql — pushdown target; planner's goal is to delegate as much work to MySQL as possible.
systems/vitess-evalengine — expression-evaluation engine that executes arithmetic / projection / filter operators at VTGate when they can't be pushed down.

Concepts extracted¶

concepts/vtgate-query-planner — parent concept; this post is a canonical architecture-retrospective disclosure.
concepts/horizon-operator — new — the deferred-horizon-planning placeholder operator.
concepts/runnable-plan-at-every-step — new — the invariant that every pipeline step emits an inspectable runnable plan.
concepts/offset-planning — new — named pipeline stage between horizon planning and executable-plan emission.
concepts/fixed-point-tree-rewriting — the convergence discipline the new planner uses inside horizon planning.
concepts/planner-phase-ordering — the phase abstraction the new planner embodies.
concepts/query-planner — parent concept.
concepts/scatter-gather-query — the shape of the LHS emission in the worked example when the user shard key is not pinned.
concepts/cross-shard-query — shape of the join; resolved at VTGate.
concepts/keyspace — unit of shard-count boundary; planner dispatches Routes per keyspace.
concepts/nested-loop-join — the join algorithm VTGate emits for cross-shard joins.

Patterns extracted¶

patterns/runnable-plan-pipeline — new — the architectural pattern of making every optimisation-pipeline step produce a runnable plan (vs. opaque intermediate state).
patterns/phase-gated-planner-rewriter — the downstream pattern this architecture unlocks (gating rewriters behind phase boundaries to prevent interference).

Operational numbers¶

Pipeline stages (new model): 4 — Parse → Determining Join Order → Horizon Planning → Offset Planning → Executable Plan.
Pipeline stages (old model): 3 — Parse → Determining Join Order → Horizon Planning → Executable Plan. Horizon planning was opaque.
Tree transformation arity: 2 (operator + child) in, 1 subtree out.
Worked-example plan-tree snapshots: 4 (initial, expanded, pushed, fully-routed).
Worked-example final queries issued: 2 (LHS scatter + RHS point lookup per row).

Caveats¶

No phase-list enumeration for the new model. Taylor names four pipeline stages in the new-model diagram (including the new Offset Planning stage) but doesn't enumerate the full rewriter set or the internal phase boundaries inside horizon planning. The sibling 2024-07-22 post ("Optimizing aggregation in the Vitess query planner") is the disclosure that names the initial
split-aggregation phases explicitly (Source: sources/2026-04-21-planetscale-optimizing-aggregation-in-the-vitess-query-planner).
Offset Planning is named but not deeply specified. The post says offset planning is "one new step, between Horizon Planning and Executable Plan" but doesn't describe its operator-tree input/output shape. The wiki page for concepts/offset-planning records the name + placement + plausible interpretation (symbolic-column-ref → positional-offset resolution) and flags the mechanism as not-fully-specified-here.
No performance numbers. This is a design-retrospective post, not a benchmark. No claims about planner-latency reduction, cache hit rates, or plan quality vs. the old model. The improvements are described as testability + inspectability + expressiveness, not runtime speed.
Gen4 is implicit, not named. The post is a 2023-06-01 post; it talks about "old model" vs. "new model" but doesn't tag the new model as "Gen4" (which is the external PR-level name). Readers familiar with Vitess release notes will know "the new model" == Gen4. The sibling wiki pages (concepts/vtgate-query-planner, systems/vitess) already canonicalise Gen4 as the external label.

Seen in¶

This is the canonical article. Cross-references to related wiki pages are maintained in the frontmatter related: arrays of the extracted concepts / patterns / systems.