Sql Query Optimizer

The Problem

Your production dashboard takes 12 seconds to load. The slow query log shows a SELECT that examines 2.4 million rows to return 200. You added an index on created_at — it didn't help because the query wraps it in EXTRACT(YEAR FROM created_at), which makes the index useless. The query plan shows a Seq Scan, and your DBA is on vacation.

What You Get

• Anti-pattern detection — identifies function-on-indexed-column, SELECT *, accidental cross joins, NOT IN with nullable columns, LIKE '%prefix' searches, correlated subqueries, and OFFSET-based pagination on large result sets
• EXPLAIN plan interpretation — reads PostgreSQL (Seq Scan → Index Scan analysis), MySQL (type: ALL, Using filesort), SQLite (SCAN TABLE), and SQL Server (Key Lookup, Table Scan) execution plans and translates them into actionable recommendations
• Query rewriting engine — produces optimized queries with inline comments explaining each transformation, covering sargable predicates, JOIN restructuring, NOT EXISTS conversion, and keyset pagination
• Index recommendation — generates exact CREATE INDEX statements with column order optimized for the identified access patterns, including composite indexes for multi-predicate queries
• Cross-dialect awareness — handles dialect-specific optimizations (PostgreSQL window functions, MySQL implicit casts, SQLite query planner behavior, SQL Server key lookups)
• Performance verification — generates EXPLAIN plan comparison scripts to validate that the optimized query actually performs better before deploying

Why This Beats Prompting It Yourself

You can paste a slow query and ask "optimize this." The agent will suggest an index or two. But it won't run EXPLAIN to verify the index actually helps, won't check if the query has three other anti-patterns you didn't notice, and won't catch the implicit type cast that makes your VARCHAR index invisible. This skill performs a systematic diagnosis — anti-pattern scan, EXPLAIN analysis, rewrite, verification — the same workflow a DBA follows, not the one-shot suggestion you get from a general-purpose agent.

Example Session

You: "This query takes 12 seconds on our orders table (2.4M rows):

SELECT id, title, status FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2024 AND EXTRACT(MONTH FROM created_at) = 3
ORDER BY created_at DESC LIMIT 20;
```"

Agent: "Diagnosis: Function-wrapped index column prevents index usage. EXTRACT() on created_at forces Seq Scan of 2.4M rows.

Anti-pattern: SARGability violation — `EXTRACT(YEAR FROM created_at) = 2024` makes the index on created_at unusable.

Optimized query:
```sql
SELECT id, title, status FROM orders
WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01'
ORDER BY created_at DESC LIMIT 20;

Index recommendation:

CREATE INDEX idx_orders_created_status ON orders (created_at DESC, status);

Expected improvement: Seq Scan (2.4M rows) → Index Scan (≈12K rows). Verify with:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Estimated time reduction: 12 seconds → <50ms."

Use Cases

• Investigating slow queries flagged by production monitoring or slow query logs
• Code review of SQL in PRs for performance anti-patterns before merge
• Designing new queries that will run against large tables (100K+ rows)
• Planning index strategy after analyzing actual query patterns
• Migrating queries between database engines where dialect-specific behavior differs

Known Limitations

Optimization recommendations depend on accurate statistics — stale table statistics can make EXPLAIN plans misleading (run ANALYZE first). Index recommendations assume sufficient disk space and acceptable write overhead; every index slows INSERT/UPDATE/DELETE. The skill analyzes individual queries, not workload-wide query interaction effects. Always validate with EXPLAIN ANALYZE on production-representative data before deploying changes.