Query Explainer Protocol

This skill bridges the gap between raw database optimizer output and human-readable performance tuning. It reads EXPLAIN (ANALYZE, BUFFERS) and translates the nodes and costs into concrete actions.

Core assumption: A developer should not need to be a DBA to understand why their query takes 3 seconds and what to do about it.

0. Context Intake

Before designing, make sure you have the inputs below. Read the previous workflow step's artifact first if it exists. Ask only for what is missing, in a single message, and state any assumption you make instead of blocking.

The execution plan (EXPLAIN (ANALYZE, BUFFERS) output preferred) and the query text.
Engine and version.
Table sizes when the plan does not show them.

1. Plan Ingestion & Translation (Static vs Dynamic)

Default (Static): Analyze based on user-provided EXPLAIN text/JSON outputs.
Dynamic (On-Demand): Only connect to a live database to execute EXPLAIN (ANALYZE, BUFFERS) directly if the user explicitly authorizes it and provides the target query.
When evaluating the plan structurally:
- Cost vs Actual: Differentiate between planner estimates (cost=0.00..10.00) and reality (actual time=0.015..0.020).
- Data Volume: Note discrepancies between rows=1000000 (estimated) and loops=1 actual rows fetched (this indicates bad statistics).

2. Identify Bottleneck Nodes

Highlight the most expensive parts of the query:

🐌 Sequential Scans (Seq Scan): Acceptable on tiny tables. Disastrous on millions of rows.
🐌 Nested Loops over many rows: Indicates missing indexes on the joined columns.
🐌 Filesort / External Merge Disk: The database ran out of work_mem and had to sort on disk. This is a critical performance killer.
🐌 High Disk I/O: Look for high shared read blocks compared to shared hit (buffers).

3. Human-Readable Output Generation

Provide an intuitive breakdown of the issue.

Outputs. In file mode — the user wants artifacts, or this skill runs as a step of an ecosystem workflow — write both files below to docs/database-report/. In inline mode — a quick question — answer in the chat and end with the JSON below as a Handoff block instead of creating files.

Human-Readable Markdown (docs/database-report/query-explainer-report.md)

### 📊 Plan Summary
- **Total Execution Time:** 2350.45 ms
- **Biggest Bottleneck:** `Seq Scan` on the `orders` table (took 2100 ms, 89% of the time).

### 🔍 What the Database Actually Did
1. It scanned **every single row** (1.5 million rows) in the `orders` table to find records where `status = 'pending'`.
2. It then joined these results with the `users` table. Because there was no index to help sort them, it had to sort them in memory (`Sort Method: external merge disk`).

### 💡 How to Fix It
**Action 1: Add a Missing Index**
The planner is begging for an index on `status`. Wait, run `index-advisor` to be sure, but here is a quick fix:
`CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status) WHERE status = 'pending';`

**Action 2: Increase work_mem (Optional)**
If this is a data-warehouse query, the sort spilled to disk because it needed 45MB of memory but only had 4MB. Temporarily increasing `work_mem` for the session might help.

Machine-Readable JSON (docs/database-report/query-explainer-output.json)

{
  "skill": "query-explainer",
  "execution_time_ms": 2350.45,
  "bottlenecks": [
    {"type": "Seq Scan", "table": "orders", "cost_pct": 89, "reason": "Missing index on status"}
  ],
  "recommendations": [
    {"type": "ADD_INDEX", "sql": "CREATE INDEX ..."}
  ]
}

When to Skip

No plan is available and the user cannot run EXPLAIN; ask for it instead of guessing.

Guardrails

Beware of Stats: Always check if row estimations wildly differ from actual rows. If so, recommend ANALYZE table_name; before blindly creating an index.
Context matters: A Seq Scan is supposed to happen if the query requests 80% of the table. Don't recommend indexes for massive bulk exports.
Integration: Forward complex index configurations to the index-advisor skill.

🔗 Next Steps & Handoffs

Ecosystem: @ecosystem-database — Database Domain.

Workflows:

Performance Optimization Flow (db-performance, step 1 of 3): next → @index-advisor.

Handoff contract: pass results to the next skill through docs/database-report/query-explainer-output.json with the fields skill, workflow, created_at, inputs, summary, and next (the handoff contract of @ecosystem-database). If a next skill is not installed, continue with its step from the ecosystem map, or install it with npx skills add fatih-developer/fth-skills --skill <name> after the user agrees.