SQL Query Optimization

Framework

IRON LAW: Measure Before Optimizing

NEVER guess which query is slow or why. Use EXPLAIN (EXPLAIN ANALYZE in
PostgreSQL) to see the actual execution plan. The database's plan often
differs from what you expect — a query you think is efficient may do
a full table scan, and a complex-looking query may use an index perfectly.

Measure → identify bottleneck → fix → measure again.

EXPLAIN Output Reading

Key metrics in EXPLAIN ANALYZE (PostgreSQL):

Indexing Strategy

Composite index column order matters: Put the most selective (highest cardinality) column first. INDEX(status, date) is good if you always filter by status. INDEX(date, status) is better if you always filter by date range first.

Common Anti-Patterns

Optimization Workflow

1. Identify slow queries: Database slow query log (pg_stat_statements, MySQL slow log)
2. Run EXPLAIN ANALYZE on the slowest
3. Find the bottleneck: Seq Scan on large table? Missing index? Expensive sort?
4. Apply fix: Add index, rewrite query, or restructure schema
5. Verify: Run EXPLAIN ANALYZE again — confirm improvement
6. Monitor: Check that fix didn't degrade other queries

Partitioning (Large Tables)

When tables exceed millions of rows:

Output Format

# Query Optimization: {Context}

## Slow Query
```sql
{the original slow query}

• Execution time: {current ms}
• Rows scanned: {N}
• Problem: {what EXPLAIN revealed}

Fix Applied

{What was changed — new index, query rewrite, etc.}

Result

• Execution time: {original ms} → {optimized ms} ({X% improvement})
• Rows scanned: {original N} → {optimized N}

## Gotchas

- **Indexes have write cost**: Every INSERT/UPDATE must update all indexes. Over-indexing slows writes. Index what you query, not everything.
- **Statistics can be stale**: If EXPLAIN estimates are way off from actuals, run `ANALYZE` (PostgreSQL) or `ANALYZE TABLE` (MySQL) to update statistics.
- **Query cache hides problems**: A query may appear fast because it's cached. Test with cache cleared or cold start.
- **ORM-generated queries**: ORMs (Django, SQLAlchemy, ActiveRecord) generate SQL that may not be optimal. Always inspect the actual SQL for performance-critical paths.
- **Connection pooling**: Sometimes the bottleneck isn't the query but connection overhead. Use connection pooling (PgBouncer, ProxySQL) for high-concurrency applications.

## References

- For PostgreSQL-specific optimization, see `references/pg-optimization.md`
- For CTE vs temp table performance comparison, see `references/cte-vs-temp.md`