Raw view: SQL query explainer and optimizer

No invisible, zero-width, or bidi-control characters detected.

---
name: sql-query-explainer
description: Explains what a SQL query does in plain language, reads its execution plan, and proposes concrete optimizations (indexes, rewrites, pagination) with engine-specific EXPLAIN guidance for PostgreSQL, MySQL/MariaDB, SQLite, SQL Server, and BigQuery in references/. Use when the user pastes a SQL query, asks why a query is slow, asks what a query does, wants an index recommendation, or shares EXPLAIN output.
license: CC0-1.0
metadata:
  author: Waxmark
  version: "1.0.0"
---

# SQL Query Explainer

Two deliverables: an explanation a teammate without SQL fluency can follow, and an optimization plan grounded in the actual plan output rather than folklore.

## Procedure

1. **Identify the engine.** From the user, the dialect (`LIMIT` vs `TOP`, `::` casts, backticks, `ILIKE`, `STRUCT`), or the connection string. If unknown, ask; the EXPLAIN syntax and the optimizer behavior differ enough to matter. Then open the matching section in `references/engines.md`.
2. **Explain the query** top-down in the order the engine evaluates it, not the order it is written: FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT. One sentence per clause. Name the grain of the result ("one row per customer per month").
3. **Get the plan.** Ask the user to run the engine's EXPLAIN variant from the reference (prefer the one that executes and reports actual rows/time). If they cannot run it, reason from the schema and say the recommendations are unverified.
4. **Read the plan** for the five usual suspects:
   - Full scans on large tables where a predicate exists (missing or unusable index).
   - Estimated vs actual row counts off by 10x or more (stale statistics or correlated predicates).
   - Nested loops over large outer sets (join order or missing index on the inner side).
   - Sorts or hashes spilling to disk (work memory, or sort that an index could provide).
   - Functions or casts on indexed columns in predicates (`WHERE lower(email) = ...`, `WHERE date(created_at) = ...`) that defeat the index.
5. **Propose fixes** in order of expected payoff and lowest risk: predicate rewrite, index (composite, covering, partial, expression), query restructure (EXISTS instead of IN with subquery, pre-aggregate, keyset pagination), then schema or config. For each, state the mechanism and how to confirm it worked (re-run EXPLAIN, compare rows and time).
6. **Check correctness** of any rewrite: same grain, same NULL handling, same duplicates. Say explicitly when a rewrite changes semantics.

## Output format

```markdown
## What it does
<Plain-language walkthrough, one bullet per clause, ending with the result grain.>

## Plan reading (<engine>)
- <Node>: <what it is doing> - <rows est/actual> - <concern or ok>

## Recommendations
1. **<Fix>** - <mechanism> - expected effect: <e.g. seq scan -> index scan on 2M rows>
   ```sql
   CREATE INDEX CONCURRENTLY idx_orders_customer_created
     ON orders (customer_id, created_at DESC);
   ```
   Verify: re-run `EXPLAIN (ANALYZE, BUFFERS)`; expect Index Scan and shared hits under N.
2. ...

## Rewritten query (if any)
<SQL, with a note on any semantic difference. "Equivalent" or "Differs: ...">

## Not verified
<Anything assumed about table sizes, indexes, or data distribution.>
```

## Rules

- Never recommend an index without naming the predicate or sort it serves and the write cost it adds.
- Prefer keyset pagination (`WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT n`) over `OFFSET` for deep pages.
- `SELECT *` is a finding only when it blocks a covering index or moves large columns; say which.
- Quote the plan node you are talking about; do not describe the plan from memory.