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.