SQL Optimizer Skill

# SQL Optimizer Skill

---
slug: sql-optimizer-skill
version: 1.0.0
category: data
command: /optimize-sql
---

## What it does
Analyzes a slow or poorly structured SQL query and produces an optimized rewrite.
Provides index recommendations, fixes join order, eliminates N+1 patterns, and
explains the reasoning in terms of the query planner. Works with PostgreSQL by
default; adapts to MySQL, SQLite, or BigQuery when stated.

## Trigger
Use this skill when asked to optimize, speed up, or fix a slow query.
Typical invocations:
- "This query is taking 4 seconds — optimize it"
- "Why is this JOIN so slow?"
- `/optimize-sql` in Claude Code
- `/optimize-sql <context>` where context is the table schema, EXPLAIN output, or row counts

## Input
Provide one or more of:
1. The SQL query to optimize
2. The relevant table schemas (column names, types, existing indexes)
3. EXPLAIN or EXPLAIN ANALYZE output, if available
4. Approximate row counts for the tables involved
5. The database engine (PostgreSQL, MySQL, SQLite, BigQuery — defaults to PostgreSQL)

The more schema and EXPLAIN context you provide, the more precise the recommendations.
If no query is provided, ask for it before proceeding.

## Method

1. **Parse the query** — identify SELECT, JOINs, WHERE predicates, GROUP BY, ORDER BY, LIMIT.
2. **Spot anti-patterns** — sequential scans on large tables, missing index on join keys,
   functions on indexed columns (defeating the index), SELECT *, correlated subqueries.
3. **Check for N+1** — loops that issue one query per row; consolidate into a single JOIN or
   a WHERE ... IN (...) or a lateral join.
4. **Recommend indexes** — name the column(s), the index type (B-tree, GIN, GiST, composite),
   and explain which predicate it serves.
5. **Rewrite** — produce the optimized SQL and annotate each change.

### Rules
- Never suggest an index that duplicates an existing one shown in the schema.
- When rewriting a correlated subquery as a JOIN, verify the cardinality does not change the result (flag if ambiguous).
- Prefer partial indexes when the predicate filters a minority of rows.
- For N+1 in application code, show the collapsed SQL and the application-level change together.

## Output format

Show the original query and the optimized query side by side, then a **Recommendations** section:

```
## Recommendations
1. Add index: <DDL> — serves the WHERE predicate on <column>, estimated seq scan → index scan.
2. Rewrote correlated subquery as LEFT JOIN — eliminates one query per row.
3. Replaced SELECT * with explicit columns — avoids fetching unused JSONB column (avg 8 KB/row).
```

## Example output

**Original**
```sql
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
```
EXPLAIN shows: Seq Scan on orders (cost=0.00..18432.00 rows=1200000)

**Optimized**
```sql
SELECT id, status, total, created_at FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
```

## Recommendations
1. Add index: `CREATE INDEX orders_customer_created ON orders (customer_id, created_at DESC);`
   — serves both the WHERE and ORDER BY in one index scan; the planner can skip the sort entirely.
2. Replace `SELECT *` with explicit columns — the `metadata` JSONB column averages 6 KB per row;
   fetching 1 200 000 rows × 6 KB is the dominant cost today.
3. After adding the index, EXPLAIN ANALYZE to confirm Index Scan replaces Seq Scan.

## Commands (Claude Code)

- `/optimize-sql` — Analyze and rewrite the pasted SQL query with index recommendations.
- `/optimize-sql <context>` — Optimize with additional context: schema DDL, EXPLAIN output, or row counts.