# Query Optimization

> EXPLAIN PLAN analysis, index selection, join strategies, and slow query debugging.

- Skill: `sethdford/query-optimization` (Agent Skill)
- Install (CLI): `npx skillmds@latest add sethdford/query-optimization`
- Raw SKILL.md: https://api.skillmd.com/api/skills/sethdford/query-optimization/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: sethdford (https://skillmd.com/u/sethdford)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/sethdford/query-optimization

---


# Query Optimization

Making queries fast through understanding execution plans and indexes.

## Context

You are optimizing slow queries. Use EXPLAIN PLAN to understand execution.

## Domain Context

- **Execution Plan**: How database executes query; cost is estimate
- **Sequential Scan**: Reads all rows; slow on large tables
- **Index Scan**: Uses index; much faster if selective
- **Join Strategy**: Nested loop, hash, sort-merge; tradeoffs
- **Cardinality**: Rows filtered at each step

## Instructions

1. **Identify Slow Queries**: Slow logs, application monitoring
2. **Get EXPLAIN PLAN**: Understand how database executes query
3. **Look for Scans**: Sequential scan on large table = problem
4. **Add Indexes**: On columns in WHERE, JOIN, ORDER BY
5. **Verify Join Order**: Database should join most selective first
6. **Rewrite if Needed**: Sometimes different query is faster
7. **Monitor**: Re-check plan after schema changes

## Anti-Patterns

- Optimizing without EXPLAIN PLAN; guessing is unreliable
- Adding indexes for every column; slows writes, wastes storage
- Not updating stats; optimizer makes bad decisions
- Complex queries database can't optimize; rewrite
- Indexes on queries that return too much (unconstrained)

## Further Reading

- PostgreSQL EXPLAIN documentation
- MySQL EXPLAIN documentation
- SQL Antipatterns (Joe Celko), Chapter on queries