sql-optimization

SQL Query Optimization Patterns

Comprehensive guide to optimizing SQL queries for performance, including EXPLAIN plan analysis, index design strategies, query rewriting patterns, and N+1 query detection. Works across PostgreSQL, MySQL, and SQLite.

---

Quick Reference

When to use this skill:

• Slow database queries (>100ms for simple queries, >1s for complex)
• High database CPU usage
• Analyzing EXPLAIN plans
• Designing database indexes
• Debugging N+1 query problems
• Optimizing JOIN operations
• Reducing table scans

Common triggers:

• "This query is too slow"
• "How do I optimize this SQL"
• "What indexes should I add"
• "Explain this EXPLAIN plan"
• "Fix N+1 queries"
• "Database CPU at 100%"

Typical improvements:

• 3 seconds → 50ms (60x faster)
• Full table scan → Index scan
• 1000 queries → 2 queries (N+1 elimination)

---

Part 1: Understanding EXPLAIN Plans

Reading PostgreSQL EXPLAIN

EXPLAIN ANALYZE
SELECT u.name, p.title
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE u.created_at > '2024-01-01'
ORDER BY p.created_at DESC
LIMIT 10;

Key Metrics to Watch:

• Seq Scan (bad): Full table scan, reads every row
• Index Scan (good): Uses index, reads only needed rows
• Cost: Estimated computational cost (lower is better)
• Actual time: Real execution time in milliseconds
• Rows: Number of rows processed at each step

Bad EXPLAIN Example

Seq Scan on users u  (cost=0.00..1234.00 rows=1000 width=50)
                     (actual time=0.123..45.678 rows=950 loops=1)
  Filter: (created_at > '2024-01-01'::date)
  Rows Removed by Filter: 50000
Planning Time: 0.234 ms
Execution Time: 3456.789 ms

Problems:

• Seq Scan: Reading entire table (50,950 rows)
• Rows Removed by Filter: Filtering after reading (wasteful)
• Execution Time: 3.5 seconds (way too slow)

Good EXPLAIN Example (After Index)

Index Scan using users_created_at_idx on users u
  (cost=0.29..123.45 rows=950 width=50)
  (actual time=0.012..3.456 rows=950 loops=1)
  Index Cond: (created_at > '2024-01-01'::date)
Planning Time: 0.123 ms
Execution Time: 4.567 ms