Query Plan Reader

---
name: query-plan-reader
description: "MANUAL-ONLY; never auto-invoke. Read ONE query's execution plan and turn it into a ranked tuning verdict — capture the plan with actual runtime statistics where safe (EXPLAIN-style, tool-agnostic), interpret operators (scans vs index access, join strategy, sort/spill, row-estimate vs actual divergence as the diagnostic core), name the dominant cost node, and rank fixes by evidence: statistics refresh, query rewrite, index add/change (write-amplification and storage price stated), or schema adjustment — each with a re-verification method at representative data volume. Multi-tenant note: tenant-scoping predicates and row-security policies appear IN the plan and are part of the cost being read. Use when one query is slow, when asked to interpret EXPLAIN output, or when an index decision needs evidence. Do NOT use for many-small-queries chatter (n-plus-one-detector), unattributed slowness (profiling-methodology-designer), or moving whole workloads (operational-vs-analytical-splitter)."
disable-model-invocation: true
---

# Query Plan Reader

**Reading key:** `EXPLAIN` shows a database's planned query operations;
`EXPLAIN ANALYZE` also executes the query to capture actual runtime data
where supported, so it needs a safe environment and authority. An object
relational mapper (ORM) builds queries from code; online analytical
processing (OLAP) describes aggregate-heavy analysis workloads.

## Purpose

A slow query is interrogated, not guessed at: an estimated execution plan
shows the planner's expected work; a plan captured with actual runtime
statistics helps locate measured time and row-count divergence. It may reveal
the scan that reads ten million rows to
return twelve, the join strategy chosen off a row estimate that is wrong
by four orders of magnitude, the sort spilling to disk — and almost every
"add an index?" debate dissolves once the plan is on the table. This
skill reads ONE query's plan and produces a ranked, evidenced verdict:
what dominates the cost, why the planner chose it, which fix removes it
(index, rewrite, statistics, schema), what each fix costs elsewhere, and
how to verify the fix actually took. It is deliberately the narrowest
tool in the performance pack — one query, its plan, its verdict.

## Use When

- Use when: one identified query is slow — from a slow-query log, an
  attribution verdict (`profiling-methodology-designer` handoff), a
  harness regression pinned to a query, or a user pointing at it.
- Use when: asked to interpret EXPLAIN / EXPLAIN ANALYZE-style output
  someone already captured.
- Use when: an index decision needs evidence — "should we add an index
  on X" is a plan-reading question, answered with the plan before and
  the predicted plan after.
- Use when: a query regressed after a data-volume change, statistics
  drift, or an upgrade, and the old/new plans need diffing.
- Do NOT use when: the problem is MANY small queries per request —
  query-count explosion is `n-plus-one-detector` *(manual-only)*; each individual query
  there is usually fine.
- Do NOT use when: nothing has attributed the slowness to a query yet —
  "the app is slow" goes to `profiling-methodology-designer`; this
  skill starts with a named query.
- Do NOT use when: the query is architecturally misplaced — an OLAP
  aggregation on the operational store may tune 2× when it needs 100×;
  read the plan, then route workload placement to
  `operational-vs-analytical-splitter` when the shape says so.
- Do NOT use when: writing or reviewing the MIGRATION that adds the
  recommended index — authoring follows the repo's change process and
  safety review is `secure-migration-reviewer`.
- Do NOT use when: designing tenant partitioning/indexing strategy for
  a store — that structural design is `multi-tenant-data-architect`;
  this skill reads how one query interacts with what exists.

## Inputs to Inspect

1. The query text, its parameters (the actual values matter — plans are
   parameter-sensitive), and its source (ORM-generated or hand-written;
   ORM queries may need capturing as actually emitted).
2. The plan, ideally with ACTUAL statistics (analyze-style execution,
   with the caveat below for writes): node costs/timings, row estimates
   vs actuals, access methods, join strategies, memory/spill indicators.
3. Schema context for the touched tables: existing indexes and their
   definitions, constraints, approximate row counts and growth, column
   cardinality/skew for the filtered columns.
4. Statistics state: when the planner's statistics were last refreshed
   for these tables (estimate-vs-actual divergence often ends here).
5. The multi-tenant overlay: tenant predicates and row-security
   policies that the store injects into this query's plan — they are
   real filter nodes with real cost, and an index that ignores the
   tenant key serves multi-tenant queries badly.
6. The query's write context: how hot the table is for writes (bounds
   the index appetite) and how often this query runs (a weekly report
   earns less optimization than a per-request path).

## Workflow

When a plan verdict presents a user-facing build choice, explain terms such
as statistics refresh, query rewrite, index and schema adjustment. State why
each viable route would address this plan, its case-specific benefits and
drawbacks, and purchase, implementation and ongoing costs (or unknowns).
Recommend one using measured plan evidence and explain why. Ask one decision
at a time only when a decision is needed; a recommendation does not authorize
the database change.

Explicit human invocation selects this execution-capable skill; it does not
expand the allowed target or activate Task-Authorized Local Implementation
(TALI). TALI is a separate route requiring its own classification and activation
under the [skill-generation standard](../../../docs/skill-generation-standard.md#5-least-privilege--side-effects).
Use applicable existing user grants without repeated consent. Before a
state-changing command, confirm its actual files, environment and effects fit
those grants; if authority is absent, propose the operation and obtain it
before proceeding.

1. **Capture the plan honestly.** Prefer actual-execution statistics
   over estimates for reads; for INSERT/UPDATE/DELETE, analyze-style
   execution EXECUTES the write — use the store's rollback-wrapped
   analyze idiom or read-only equivalents, and never against production
   data paths without the repo's approval conventions. Capture at
   representative data volume — a plan over 100 rows says nothing
   about 100 million (the planner itself chooses differently).
2. **Find the dominant node.** Walk the plan for where cost/time
   concentrates: the deepest expensive scan, the join producing the
   row explosion, the sort/hash spilling to disk. Name ONE dominant
   cost; plans with two independent hotspots get sequenced (fix the
   dominant, re-plan, reassess).
3. **Read estimate-vs-actual divergence as the first diagnostic.**
   A node estimating 40 rows and producing 400,000 explains every bad
   downstream choice (wrong join strategy, wrong access method). Causes
   in order of likelihood: stale statistics, correlated predicates the
   planner multiplies independently, skewed data the histogram misses,
   parameter sensitivity. Statistics refresh is the cheapest fix in the
   entire catalog — check it before designing indexes.
4. **Interpret access methods against selectivity.** A full scan is
   WRONG only when the predicate is selective; a scan serving 80% of
   the table is the planner being right. An index access looping
   millions of times can be slower than one scan — read the loop
   counts, not the operator names.
5. **Rank the fixes with their prices.** In default order of
   cheapness: statistics refresh; query rewrite (predicate made
   sargable — no functions over the filtered column, no implicit type
   casts; unneeded columns dropped from the select; pagination made
   keyset instead of large offsets); index add/change (composite
   ordered by equality→range, covering where the store supports it,
   tenant-key-aware in multi-tenant tables) — priced in write
   amplification per mutation and storage; schema adjustment
   (generated/computed columns, denormalized read fields) — priced in
   consistency maintenance. Every recommendation states its price, not
   just its benefit.
6. **Predict, apply (via the change process), re-verify.** State the
   expected plan change per fix ("scan → index access on X, loops
   1→N"), then verify after application: re-capture the plan under the
   SAME parameters and volume, confirm the shape changed as predicted,
   and confirm wall-clock/percentile improvement at the calling
   surface (`performance-test-harness` gates where they exist). A fix
   that changes the plan but not the latency is a diagnosis error —
   loop back to step 2.
7. **Route what plan-reading cannot fix.** OLAP shape on the
   operational store → `operational-vs-analytical-splitter`; result
   cacheable and staleness-tolerant → `caching-strategy-designer`;
   the index migration itself → the change process +
   `secure-migration-reviewer`.

Operator-reading table, estimate-divergence causes, sargability
catalog, and the composite-index worksheet:
[references/plan-reading-guide.md](references/plan-reading-guide.md).

## Output Format

```
QUERY PLAN VERDICT — <query id/summary>
Captured: <actual|estimate>, params=<values>, volume=<rows in touched tables>
Dominant cost: <node, share of total, why the planner chose it>
Divergence:   <est vs actual at key nodes; statistics state>
Tenant overlay: <tenant/row-security predicates present + their cost note | n/a>
Fixes (ranked):
  1. <fix> — why this plan supports it; benefit and drawback; expected plan
     change: <shape>; price: <money, implementation time, write amp/storage/upkeep>
  2. ...
Recommendation: <preferred fix and why; decision required, if any>
Re-verify:    <re-plan under same params+volume; expected shape; surface-level timing check>
Routed out:   <splitter | caching-strategy-designer | secure-migration-reviewer | none>
Not addressed: <what this verdict deliberately leaves alone>
```

## Validation Checklist

- [ ] The plan was captured with actual statistics (or the write-safe
      idiom) at representative volume — not an estimate over a toy
      table.
- [ ] ONE dominant cost is named with its share; multi-hotspot plans
      are sequenced, not shotgunned.
- [ ] Estimate-vs-actual divergence was read and statistics state
      checked BEFORE any index was designed.
- [ ] Every recommended index states column order rationale and its
      write/storage price; no index-everything lists.
- [ ] User-facing choices explain terms, reasons, costs or unknowns,
      case-specific pros/cons, and a justified recommendation.
- [ ] Rewrites preserve semantics (stated) — a faster query returning
      different rows is a bug, not a fix.
- [ ] Tenant predicates/row-security cost was read, and recommended
      indexes serve the tenant-scoped access pattern.
- [ ] The re-verification method is stated with expected plan shape and
      a surface-level timing check.
- [ ] Fixes plan-reading cannot deliver are routed, not absorbed.

## Gotchas

- Parameter sensitivity: the plan for `status='rare_value'` and
  `status='common_value'` can differ completely; a fix verified on the
  wrong parameter fixes nothing. Capture with the SYMPTOMATIC values,
  and note plan-cache/prepared-statement effects where one cached plan
  serves all values.
- Analyze-style capture of writes executes them — the rollback-wrapped
  idiom is mandatory, and production data paths need approval. The
  plan you capture must never be the incident.
- Toy-volume plans lie twice: the planner picks different strategies
  at different volumes, AND the timings are cache-warm fictions.
  Representative volume is a capture requirement, not a nicety.
- Functions over filtered columns kill index use quietly — including
  implicit ones: type coercion on a mismatched parameter type is a
  function the plan shows as a scan nobody asked for.
- Index loops beat scans until they don't: nested-loop index access
  looping 10M times loses to one scan; the crossover is in the loop
  count, which the actual plan shows and the operator name hides.
- The covering-index tax: covering every query's columns bloats every
  write; composite order (equality first, then range, then includes)
  and the write-amplification price keep the appetite honest.
- Row-security predicates are invisible in the query text: the plan
  shows filters the developer never wrote. In multi-tenant stores,
  read them — an index without the tenant key in front often serves
  the policy-wrapped query badly.
- OFFSET pagination degrades linearly by design — no index fixes page
  4,000 of an offset paginator; that is a keyset-rewrite verdict.

## Stop Conditions

- The skill was selected automatically rather than explicitly invoked by the
  human: do not execute it. An agent may recommend the named manual skill.
- A write, Git/network operation, database command or test side effect exceeds
  the applicable human grant: stop that operation and obtain the missing scope.
  Existing authorized operations do not need the same permission again.

- Asked to capture analyze-style plans for mutating statements against
  production, or the capture itself would add dangerous load → stop
  and require the rollback-wrapped idiom / a replica / explicit human
  approval per the repo's conventions.
- The plan cannot be captured at representative volume anywhere (no
  environment has realistic data) → halt and say the verdict would be
  fiction; route to test-data provisioning before diagnosing.
- The requested outcome is "make it fast without changing the query or
  the schema and without any new index" and the plan shows the
  dominant cost is structural → explain the required structural changes,
  benefits, drawbacks, money/time/upkeep costs, and the evidenced preferred
  route before asking which constraint can change; there is no incantation.
- The query's SHAPE is analytical on an operational store and tuning
  headroom is an order of magnitude short of the need → deliver the
  plan verdict AND route placement to
  `operational-vs-analytical-splitter`; do not promise tuning will
  carry an architectural burden.
- Asked to also apply the index/rewrite directly to a live store →
  decline execution; recommendations flow through the change process
  (`secure-migration-reviewer` for the migration).

## Supporting Files

- [references/plan-reading-guide.md](references/plan-reading-guide.md)
  — operator-reading table (scans, joins, sorts, spills), estimate-
  divergence cause list, sargability catalog, composite-index
  worksheet, keyset-pagination rewrite pattern.
- `evals/evals.json` — behavior cases including the estimate-divergence
  edge and the index-everything refusal.
- `evals/trigger-evals.json` — discrimination against
  `n-plus-one-detector`, `profiling-methodology-designer`,
  `operational-vs-analytical-splitter`, and `multi-tenant-data-architect`.