explain-interpreterlisted

# Explain Interpreter ## Purpose Provide a standardized way to read a query plan, Postgres or MySQL, text or JSON, and convert it into a plain-language diagnosis and a concrete fix, statically, from pasted output. A plan is a tree of nodes; the database executes the leaves first and passes rows up. The skill is knowing which node is actually eating the time and what that node's presence implies. ## Reading methodology 1. **Identify the format and whether it was analyzed.** `EXPLAIN` alone gives *estimates*; `EXPLAIN ANALYZE` (Postgres) / `EXPLAIN ANALYZE` (MySQL 8) gives *actual* rows, time, and loops. Only actuals prove a bad estimate. 2. **Find the dominant node.** Read inside-out. A node's cost/time includes its children, so compute **self time** = node time − children's time, and for inner nodes of a loop, **multiply by `loops`**. The node with the largest self time (not the root) is the target. 3. **Read the node's meaning** (see `references/postgres-explain-nodes.md` / `references/mysql-explain.md`). 4. **Scan for red flags** (below). 5. **Prescribe** an index (right columns, right order) or a rewrite. ## The single most important number: estimate vs actual `(cost=... rows=1000)` is the planner's *estimate*; `(actual ... rows=920000 loops=1)` is reality. A large skew (planner thought 1 row, got 900k) means the planner chose the wrong strategy (often a nested loop that would have been a hash join). Causes: stale statistics (`ANALYZE`), correlated predicates the planne