backend · intermediate · 6d
Query plan visualizer
Paste an EXPLAIN (ANALYZE, FORMAT JSON) and render the plan tree with per-node timing and row-estimate error, so a bad join jumps out visually.
Deliverable
A web app that turns EXPLAIN (ANALYZE, FORMAT JSON) into an annotated collapsible tree with per-node timing (self-time), corrected row estimates (Actual Rows × Loops), worst-node and spill badges, and plan-to-plan diffing — with no false alarms on cheap nodes.
Milestones
0/5 · 0%- 01Parse EXPLAIN JSON to a tree
Parse EXPLAIN (ANALYZE, FORMAT JSON) into a faithful node tree — preserving parent/child structure, node types, and the fields the later milestones depend on. The JSON top-level is an array with one plan object; each node has Node Type, Plans (children), Startup Cost, Total Cost, Plan Rows, Actual Startup/Total Time, Actual Rows, and Loops. Loops is the multiplier that makes or breaks correctness: inside a 1000-iteration nested loop, Actual Rows is per-loop, so total actual rows = Actual Rows × Loops — conflating them makes estimate error 1000× wrong. Handle multi-child nodes correctly (e.g. Hash Join build vs probe sides are separate child arrays, not to be merged) and all scan/join node types without collapsing them. Malformed or non-JSON input must fail with a clear message and line hint, not a crash or empty tree.
Definition of done- EXPLAIN (ANALYZE, FORMAT JSON) with nested loops, hash joins (build/probe), and mixed scan types parses into a node tree preserving parent/child and per-node fields — verified with at least 3 fixture plans (simple scan, nested-loop join, hash join with batches).
- Malformed / non-JSON input shows a clear error with position hint, not a crash; Actual Rows × Loops total is computed and exposed per node for later milestones.
Feeds fromSelf-review
Paste the parsed tree for a nested-loop plan and show Actual Rows × Loops total per node. A senior reviewer checks Loops is applied (not raw Actual Rows), build/probe children are separate, and malformed input gives a clear error.
- 02Per-node timing and row estimates
Render the tree with per-node actual vs planned rows and self-time vs cumulative time, collapsible. For each node show: Plan Rows vs total actual rows (Actual Rows × Loops) and the estimate error ratio (actual/planned), and Actual Total Time vs self-time. Self-time is the node's own contribution: `self = Total Time − Σ children's Total Time` — for leaf nodes (seq scan, index scan) self equals Total Time; for the root, Total Time is always the largest but self may be tiny, so displaying only Total Time misleads and makes the root always look like the bottleneck. Loop-correct the rows, compute self-time, and make the tree collapsible so large plans remain readable. Surface latency and row signals side-by-side so the user sees both 'where time went' and 'where the planner was most wrong' without conflating cost (planner prediction) with time (execution reality).
Definition of done- Each node shows Plan Rows vs total actual rows (× Loops) with error ratio, and Total Time vs self-time (self = Total − Σ children); the tree is collapsible and leaf self equals Total.
- A fixture nested-loop plan shows corrected row totals (not per-loop) and a hash-join plan shows self-time correctly smaller than the root's Total Time — both verified numerically.
Feeds fromSelf-review
Show a nested-loop fixture with Actual Rows × Loops total and a node where self-time ≠ Total Time. A senior reviewer checks the formula self = Total − Σ children and that rows are loop-corrected, not raw.
- 03Surface the worst node without false alarms
Highlight the worst node with two independent signals — largest estimate error (actual/planned, loop-corrected) and largest self-time — surfaced separately so the user sees both dimensions. The senior subtlety is false alarms: an estimate error of 100× on a 0.01 ms node is noise, not a bottleneck. Weight the severity by self-time (e.g. `severity = log(error) × self-time` or `error × self-time`) so cheap nodes never dominate. A correct plan with no large error must show no flags — test this explicitly. Explain why Total Cost alone is the wrong signal (it's the planner's prediction, not execution time) and why self-time alone misses planner mistakes (a fast node with a huge estimate gap signals stale statistics or a missing index, even if it's not the slowest).
Definition of done- The nodes with the largest loop-corrected estimate error and largest self-time are flagged independently; severity weights error by self-time so a 100× error on a 0.01 ms node does not flag.
- A correct plan with no large error shows no false alarm — verified with a fixture that has uniform small errors and is asserted to produce zero flags.
Feeds fromSelf-review
Show a plan where the largest error is on a cheap node and prove it is not flagged due to severity weighting, plus a correct plan with no flags. A senior reviewer checks severity = f(error, self-time) and that Total Cost alone is not used.
- 04Spill detection and work_mem reasoning
Detect spilled hash/sort nodes and suggest a work_mem target — the most actionable insight this tool can give. Hash joins and sorts use in-memory hash tables bounded by work_mem (default 4 MB per operation); when data exceeds it, Postgres spills to disk in batches (Batches > 1), commonly causing 10–100× slowdown. Surface: which nodes spilled (Batches > 1), their Peak Memory Usage if present (or an estimate from hash batch count when absent), and a suggested work_mem lower bound. Then reason about the tradeoff: setting work_mem too high globally is dangerous — each concurrent query can use it, so 100 connections × 256 MB = 25 GB just for sorts, risking OOM. The right advice is per-query `SET work_mem` for the spilling query, not a global bump. Prove it: a fixture with a 10-batch hash spill is flagged, a suggested work_mem is shown, and a non-spilling plan shows no spill badge.
Definition of done- Nodes with Batches > 1 are flagged as spills with Peak Memory Usage (or batch-count estimate) and a suggested work_mem lower bound; a non-spilling fixture shows no spill flag.
- The work_mem advice warns about global vs per-query setting with the 100×256 MB = 25 GB math and recommends per-query SET for the spilling query.
Feeds fromSelf-review
Show a 10-batch spill flagged with suggested work_mem and a non-spill plan with no flag. A senior reviewer checks Peak Memory Usage is used (or batch estimate) and that the advice is per-query SET with the 25 GB global math, not 'increase work_mem'.
- 05Diff two plans and observe an incident
Add plan-to-plan diffing and make the tool observable in a tuning workflow. Diff two EXPLAIN JSONs (before/after an index or statistics fix) side-by-side: highlight which nodes appeared/disappeared, where estimate error and self-time improved or regressed, and where a spill vanished. Then work the incident: take a slow query's plan with a seq scan + large estimate gap (stale statistics or missing index), add the index or run ANALYZE, re-EXPLAIN, and show the diff — the seq scan becomes an index scan, the error collapses, and the spill (if any) disappears. Emit lightweight RED-like metrics for the visualizer itself (parse time, render time, flag count) and document the tuning loop: EXPLAIN → visualize → fix (index/ANALYZE/work_mem) → re-EXPLAIN → diff → verify no new spill or regression. The post-mortem is: what the bad plan's signal was, what fix was applied, and what the diff proves.
Definition of done- Two plans (before/after) diff side-by-side highlighting node changes, error/self-time deltas, and spill appearance/disappearance — verified with a before/after fixture pair (seq scan → index scan).
- A worked incident (slow query → index/ANALYZE → re-EXPLAIN → diff) is documented with the signal, fix, and diff proof; no new spill or false flag is introduced in the after plan.
Feeds fromSelf-review
Show the before/after diff (seq scan → index scan, error collapse, spill gone if applicable) and the incident post-mortem. A senior reviewer checks the diff highlights node/error/spill deltas and that the fix is index/ANALYZE/work_mem — not 'add more hardware'.
Starter
fallowlone/skein-projects
projects/query-plan-visualizer
- README.md
- src/plan.ts
- test/plan.test.ts
npx degit fallowlone/skein-projects/projects/query-plan-visualizer query-plan-visualizer Implement the stubs, then run the tests until they pass: bun test
Fork the repo and push your work — the grade workflow runs the suite plus static checks on your own runners.
Rubric
| Junior | Mid | Senior | |
|---|---|---|---|
| Plan parsing correctness | Parses the top-level node and displays node type, estimated cost, and planned rows for simple single-node plans. | Recursively traverses Plans/children, preserves parent-child relationships across all scan and join node types, and handles plans with multiple child arrays (e.g. hash join's build vs probe sides) without conflating them. | Correctly accounts for loop counts when computing per-node actual totals: a child inside a 1000-iteration nested loop reports per-loop rows, not total rows — conflating them produces an estimate-error that is 1000x wrong. Can explain the exact JSON fields used and why Loops must be factored in. |
| Cost and timing attribution | Displays the planner's Total Cost and Actual Total Time as-is from the JSON for each node. | Computes self-time per node as Total Time minus the sum of children's Total Times, surfaces it alongside cumulative time, and shows planned vs actual rows so estimate error is immediately visible. | Flags spilled nodes: hash or sort nodes where Batches > 1 indicate a work_mem spill to disk, commonly causing a 10-100x slowdown. Surfaces a suggested work_mem lower bound from Peak Memory Usage, or estimates it from the hash batch count when that field is absent. |
| Worst-node surfacing | Highlights the node with the highest Total Cost as the bottleneck. | Computes two separate signals — largest estimate error (actual/planned rows, accounting for loops) and largest self-time — and surfaces each independently so the user sees both 'where time went' and 'where the planner was most wrong'. | Avoids false alarms on trivially cheap nodes: an estimate error of 100x on a 0.01 ms node is noise; the severity score weights error magnitude by self-time. Articulates why a correct plan with no large estimate gap should show no flags, and tests this case explicitly. |
Reference walkthrough (spoiler)
EXPLAIN JSON structure: the top-level array contains one plan object; each node has Node Type, a Plans array of children, Startup Cost, Total Cost, Plan Rows, Actual Startup Time, Actual Total Time, Actual Rows, and Loops. Loops is the multiplier: Actual Rows in the JSON is per-loop, so total actual rows = Actual Rows x Loops. Missing this produces dramatically wrong estimate-error calculations for any plan containing a nested loop.
Self-time vs cumulative time: Total Time at a node includes all children's time. The cost the node itself contributes is Total Time minus the sum of its children's Total Times. For leaf nodes (seq scan, index scan) self-time equals Total Time. Displaying only Total Time misleads — the root always appears as the most expensive node.
Seq scan as a diagnostic signal: seq scan on a large table is not always wrong — if the query returns a large fraction of rows, seq scan beats index scan because random heap fetches cost more than a sequential read. The signal worth flagging is a seq scan combined with a large estimate gap, which usually means statistics are stale or an index is missing.
work_mem spills: hash joins and sorts use in-memory hash tables bounded by work_mem (default 4 MB per operation). When data exceeds that limit, Postgres spills to disk in batches (Batches > 1). A 10-batch spill means roughly 10x the I/O. Setting work_mem too high globally is dangerous — each concurrent query can use it, so 100 connections x 256 MB = 25 GB just for sorts.
Make it senior
- Detect stale-statistics signals: a seq scan on a large table with a large estimate gap where an index exists — suggest running ANALYZE and show the before/after error collapse.
- Add a cost-vs-time scatter per node to surface where the planner's cost model diverges most from execution time.
- Export the diff as a shareable URL (compressed plan pair in the fragment) so a teammate can open the before/after without pasting JSON.