/sql-optimizer

name: sql-optimizer
description: 'Analyzes SQL queries for missing indexes, N+1 patterns, suboptimal joins, and full table scans. Interprets EXPLAIN, detects anti-patterns, rewrites queries. Triggers on: "optimize this query", "slow query", "add indexes", "explain plan", "N+1 query", "why is this query slow".'
metadata:
  version: 1.1.1
  category: data
  tags: [sql, performance, database, optimization]
  difficulty: intermediate
  phase: build

SQL Optimizer

Systematic SQL performance analysis: parse query structure, interpret EXPLAIN plans, detect anti-patterns (N+1, full scans, cartesian joins), recommend indexes, and rewrite queries — with explanations of WHY each change improves performance, not just WHAT changed.

Reference Files

| File | Contents | Load When | | --------------------------------- | ------------------------------------------------------------------------- | ---------------------------------- | | `references/anti-patterns.md` | Common SQL anti-patterns with detection rules and fixes | Always | | `references/index-strategies.md` | Index type selection, composite index ordering, covering indexes | Index recommendations needed | | `references/explain-guide.md` | Reading EXPLAIN output for PostgreSQL, MySQL, SQLite | EXPLAIN plan provided | | `references/join-optimization.md` | Join type selection, join order optimization, subquery-to-join conversion | Query contains joins or subqueries |

Prerequisites

• The SQL query to optimize
• Database engine (PostgreSQL, MySQL, SQLite) — optimization differs by engine
• Table schemas and approximate row counts (helpful but not required)
• EXPLAIN output (highly valuable when available)

Workflow

Phase 1: Query Analysis

Parse the SQL to understand its structure:

1. **Identify operations** — SELECT columns, FROM tables, JOIN conditions, WHERE filters, GROUP BY, ORDER BY, HAVING, subqueries. 2. **Map table relationships** — Which tables are joined? On what keys? Are there implicit cartesian products? 3. **Detect immediate red flags**:

• `SELECT *` — fetching unnecessary columns
• Functions on indexed columns in WHERE — prevents index use
• `OR` in WHERE — often prevents index use
• Correlated subqueries — potential N+1
• Missing WHERE on DELETE/UPDATE — dangerous

Phase 2: EXPLAIN Interpretation

If an EXPLAIN plan is provided:

1. **Scan types** — Sequential Scan (bad for large tables), Index Scan (good), Index Only Scan (best), Bitmap Index Scan (acceptable). 2. **Join methods** — Nested Loop (good for small tables), Hash Join (good for equi-joins), Merge Join (good for sorted data). 3. **Row estimates** — Compare estimated rows with actual rows. Large discrepancies indicate stale statistics (`ANALYZE`). 4. **Cost hotspots** — Highest-cost node is the bottleneck. Optimize there first. 5. **Sort operations** — External sorts (disk) are expensive. Consider indexes that match ORDER BY.

Phase 3: Anti-Pattern Detection

Check for known performance anti-patterns (see `references/anti-patterns.md`):

| Pattern | Detection | Impact | | --------------------------- | ------------------------------ | -------------------------- | | SELECT \* | Star in select list | Transfers unnecessary data | | N+1 queries | Loop with query inside | N additional roundtrips | | Function on indexed column | `WHERE UPPER(name) = 'X'` | Index bypass | | Implicit type cast | String compared to integer | Index bypass | | Missing join condition | Cartesian product | Exponential rows | | LIKE '%prefix' | Leading wildcard | Full scan | | OR with different columns | `WHERE a=1 OR b=2` | Index bypass | | SELECT DISTINCT as band-aid | Hides duplicate-producing join | Fix the join instead |

Phase 4: Optimization

1. **Index recommendations** — Based on WHERE, JOIN, ORDER BY, GROUP BY columns. Consider composite indexes for multi-column conditions. 2. **Query rewrite** — Convert correlated subqueries to JOINs, replace `IN (SELECT...)` with EXISTS, use CTEs for readability without performance cost (PostgreSQL 12+ may inline CTEs). 3. **Schema suggestions** — Denormalization, materialized views, partitioning (mention only when query-level optimization is insufficient).

Phase 5: Output

Present the original query, detected issues, recommended indexes, rewritten query, and explanation of each change.

Output Format

## SQL Optimization Analysis

### Original Query
```sql
{original SQL}

Issues Detected

| # | Issue | Severity | Location | Impact | | --- | ------- | ----------------- | ------------------- | ---------------- | | 1 | {issue} | {High/Medium/Low} | {WHERE/JOIN/SELECT} | {what it causes} |

EXPLAIN Interpretation

{If EXPLAIN provided}

• **Bottleneck:** {node type} on `{table}` (cost: {N})
• **Rows scanned:** {N} (estimated {M})
• **Index used:** {name or "None"}
• **Key insight:** {what this reveals}

Recommended Indexes

-- {Reason for this index}
CREATE INDEX {name} ON {table}({columns});

Optimized Query

{rewritten query}

Change Explanation

1. **{Change}** — {Why this improves performance. Include estimated impact.}

Expected Improvement

• Scan type: {before} → {after}
• Estimated rows scanned: {before} → {after}
• Index usage: {before} → {after}

## Configuring Scope

| Mode       | Input                                 | Depth                                    | When to Use