Menu

Individual skill

explain-plan-reader

0upvotes

Read a captured EXPLAIN ANALYZE plan, name the single bottleneck node, and prescribe a targeted fix for it. Use when a specific query is slow and you have (or can capture) its execution plan from Postgres or MySQL and need to know WHICH node is burning the time and why.

skills/explain-plan-reader/SKILL.md

Read pinned source

Source commit 97a13607d669157a4a16d5ba79b77f208319f7b9

What was checked

Matches the observed repository HEAD
Yes
Scanner rules are current
Yes
Repository scan complete
No
Skill scan complete
No
Observed behavior
Instructions only
Last checked
Nov 11, 2026

These are observations at the recorded commit, not a guarantee of safety. Review the source before running it.

Install this skill

Choose an agent in your Stack or copy a command below. Commands fetch the pinned commit and copy only this skill directory. Existing destinations are refused. Copying a command does not install anything.

An install command is unavailable: current rules, complete repository and skill scans, an eligible behavior level and a permitted license are required.

Repository context

Repository stars (not skill ratings)
24
Repository last updated
Oct 13, 2026
License
MIT

Observed agent integration: No specific integration evidence. Standard SKILL.md installation does not imply verified compatibility.

Files and excerpt

File metadata and permitted, redacted excerpts come from the recorded scan. Source links are pinned to that commit.

Browse files
Read excerpt
# EXPLAIN Plan Reader

Turn one captured execution plan into a named bottleneck node and a specific fix. A plan is a tree executed bottom-up and inside-out; your job is to find the one node where time or rows explode, not to audit every line.

## Workflow

1. **Demand a real plan first.** Do not diagnose from query text alone. In Postgres, require `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)` - ANALYZE runs the query for real timings, BUFFERS shows cache-vs-disk reads. In MySQL, require `EXPLAIN ANALYZE`. Plain `EXPLAIN` shows estimates only and lies under data skew; if that is all you have, say so and ask for ANALYZE output before prescribing.
2. **Run it twice.** The first run can be cold cache. Trust the warm run's timings unless cold-cache I/O is itself the problem (confirm via BUFFERS: high `read=` vs `hit=`).
3. **Find the bottleneck node.** Postgres reports cumulative `actual time`; a node's own cost is its time minus its children's. Locate the node with the largest delta or the largest `loops` - a real bottleneck typically owns 50% or more of total runtime; if no single node does, the time is spread and the query shape (not one node) is the problem. `(actual rows=R loops=N)` means the node ran N times - multiply R×N for true rows. A Nested Loop with high `loops` driving a Seq Scan is the classic disaster. In MySQL, scan for `type=ALL` (full scan), large `rows` examined, and `Extra: Using filesort` / `Using temporary`.
4. **Confirm with the estimate gap.** Compare estimated vs actual rows on the hot node. A gap of 10x or more (e.g. estimated 1, actual 500000) means the planner is flying blind - that misestimate usually causes the bad node choice upstream and is the root cause, not the node itself. A gap under 2-3x is normal noise; do not chase it.
5. **Diagnose by node type and prescribe one fix:**
   - Seq Scan on a large table filtered to few rows → the predicate is not served by an index. Name the column(s) and predicate shape; check whether the WHERE wraps the column in a function (defeats a plain index). Hand the access pattern to index-advisor for the actual index choice.
   - Bad row estimate → stale or insufficient statistics. Run `ANALYZE the_table`; for skewed columns raise `default_statistics_target` (default 100) to 500-1000 for the affected column and re-ANALYZE.
   - External merge / disk-spilling Sort → raise `work_mem` for the session (the Postgres default is a conservative 4MB; sorts spill the moment they exceed it), or provide pre-sorted input via an index ordering.
   - Hash Join with `Batches > 1` (spilling to disk) → raise `work_mem`.
Back to top