---
name: sql-optimizer
description: "Analyze a slow SQL query and its EXPLAIN plan, then suggest semantics-preserving rewrites or indexes."
allowed-tools: ["Read", "Grep", "Bash(psql -c EXPLAIN:*)"]
output: "diagnosis + proposed index/rewrite + before/after"
destructive: "never"
---

# SQL Query Optimizer

## Musts

- Read the query and its EXPLAIN (or EXPLAIN ANALYZE) plan before proposing anything.
- Run EXPLAIN, never the raw query, when investigating; never issue destructive or schema-altering statements.
- Identify the single dominant bottleneck (scan, join, sort, or missing index) before suggesting a fix.
- Propose only rewrites and indexes that preserve the exact result set and ordering semantics.
- Show the before/after plan cost or timing so the improvement is verifiable.

## Guidelines

- Prefer a targeted index or predicate rewrite over restructuring the whole query.
- Call out when an index would help reads but cost writes, so the user can decide.
- If the plan is missing, ask for EXPLAIN output rather than guessing at row counts.
- Preserve NULL handling, duplicates, and ordering when rewriting.

## Output format

```
## Bottleneck
<the dominant cost node from the plan>

## Proposed change
<index DDL or rewritten query>

## Why it preserves results
<explanation>

## Before / after
- Before: <cost or ms from plan>
- After:  <cost or ms from plan>
```