Query execution plan

The tree of operations a SQL engine chose to answer a query — which index it used, in what order it joined, whether it sorted in memory or spilled to disk — together with what that plan actually cost when it ran. The plan is the only place a declarative query becomes an observable procedure, which is why every engine in this spoke ships a way to print one and why the tooling that reads them keeps recurring.

Paged 2026-08-13, when pev2 arrived and gave the spoke a second plan-reading instrument to set beside the one already recorded for vitess.

Estimate versus actual is the whole diagnostic

A cost-based planner picks a plan from estimates: how many rows it expects each node to emit. It then executes, and the number of rows that actually came out is usually different. The size and direction of that gap is what explains most bad plans — the planner chooses a nested loop because it expects a handful of rows, meets hundreds of thousands, and the choice that was correct for the estimate is catastrophic for the reality.

Postgres's EXPLAIN ANALYZE prints both numbers on every node and leaves you to do the arithmetic across a plan that may be a hundred lines deep. pev2 makes the ratio a first-class metric (estimate_factor, with an over/under direction), ranks nodes by it and colours the worst. The information was always in the output; what the tool supplies is triage.

The shape recurs beyond this one tool: the engines emit complete, unranked diagnostics, and the tooling layer's job is deciding what to look at first.

The instruments this corpus holds

• Single node. EXPLAIN / EXPLAIN ANALYZE in Postgres, read through pev2: per-node timing, row counts, cost, buffer hits and reads by scope, sort space in memory versus on disk, parallel workers, JIT.
• Sharded fleet. vitess's VEXPLAIN TRACE, which reports ShardsQueried per operator, and VEXPLAIN KEYS, which names the columns that would have to become a vindex for a query to stop scattering (cross-shard-queries).

The two ask different questions of the same artifact. On one machine the question is which node misjudged its cardinality; across a fleet it is how many machines had to be asked at all. A query can be perfectly planned on every shard and still be a scatter-gather, and nothing in a single-node plan would tell you.

What the corpus cannot say

Nobody here measures whether plan visualization changes outcomes — whether an engineer with a coloured diagram fixes a slow query faster, or fixes a different set of queries, than one reading raw EXPLAIN text. The tools are documented by their authors. This is the same evidence gap the hub keeps finding wherever tooling claims to help, and it is worth naming here rather than assuming the diagrams work because they are pleasant.

Also unaddressed: nothing in this corpus explains how the planner arrives at its estimates in the first place — statistics collection, histograms, correlation, and how they go stale. The spoke holds the symptom (estimate error) and the instruments, and not the mechanism underneath.

Related

pev2 · postgresql · cross-shard-queries · vitess · online-transaction-processing · awesome-db-tools