futuregerald SQL Optimization Patterns
Transform slow database queries into lightning-fast operations through systematic optimization, proper indexing, and query plan analysis.
When to Use This Skill
- Debugging slow-running queries
- Designing performant database schemas
- Optimizing application response times
- Reducing database load and costs
- Improving scalability for growing datasets
- Analyzing EXPLAIN query plans
- Implementing efficient indexes
- Resolving N+1 query problems
Core Concepts
1. Query Execution Plans (EXPLAIN)
-- Basic explain
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
-- With actual execution stats
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';
-- Full diagnostics
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.order_total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days';
Key Metrics:
| Metric | Meaning |
|---|---|
| Seq Scan | Full table scan (usually slow for large tables) |
| Index Scan | Using index (good) |
| Index Only Scan | Using covering index, no table access (best) |
| Nested Loop | Join for small datasets |
| Hash Join | Join for larger datasets |
| Merge Join | Join for sorted data |
| Cost | Estimated query cost (lower is better) |
| Actual Time | Real execution time |
2. Index Strategies
| Type | Use Case | Database |
|---|---|---|
| B-Tree | Equality and range queries (default) | All |
| Hash | Equality (=) only | PostgreSQL |
| GIN | Full-text search, arrays, JSONB | PostgreSQL |
| GiST | Geometric data, full-text search | PostgreSQL |
| BRIN | Very large tables with natural ordering | PostgreSQL |
-- Standard B-Tree index
CREATE INDEX idx_users_email ON users(email);
-- Composite index (column order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Partial index (index subset of rows)
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';
-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- Covering index (avoid table lookups) -- PostgreSQL
CREATE INDEX idx_users_email_covering ON users(email)
INCLUDE (name, created_at);
-- JSONB index -- PostgreSQL
CREATE INDEX idx_metadata ON events USING GIN(metadata);
3. Query Optimization Patterns
Avoid SELECT *: Fetch only the columns you need.
-- Bad
SELECT * FROM users WHERE id = 123;
-- Good
SELECT id, email, name FROM users WHERE id = 123;
Avoid functions on indexed columns in WHERE:
-- Bad: prevents index usage
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- Good: create expression index, or store normalized data
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
Filter before JOIN:
-- Good: explicit JOIN with WHERE
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';
-- Better: filter both sides early
SELECT u.name, o.total
FROM (SELECT id, name FROM users WHERE created_at > '2024-01-01') u
JOIN orders o ON u.id = o.user_id;
Optimization Patterns
Pattern 1: Eliminate N+1 Queries
Problem: N+1 queries execute one query per row instead of batching.
# Bad: N+1 queries
users = db.query("SELECT * FROM users LIMIT 10")
for user in users:
orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)
Solutions:
-- Solution 1: JOIN
SELECT u.id, u.name, o.id as order_id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id IN (1, 2, 3, 4, 5);
-- Solution 2: Batch query
SELECT * FROM orders WHERE user_id IN (1, 2, 3, 4, 5);
Pattern 2: Cursor-Based Pagination
Problem: OFFSET-based pagination degrades on large tables.
-- Bad: slow for large offsets
SELECT * FROM users ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;
Solution: Use a cursor (last seen value).
-- Fast: cursor-based
SELECT * FROM users
WHERE (created_at, id) < ('2024-01-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Supporting index
CREATE INDEX idx_users_cursor ON users(created_at DESC, id DESC);
ORM-Specific Anti-Patterns
N+1 in ActiveRecord (Rails)
# Bad: N+1
users = User.all
users.each { |u| u.orders } # N queries!
# Good: Eager loading
users = User.includes(:orders).all
# Good: With constraints
users = User.includes(:orders).where(orders: { status: 'completed' })
Sequential Queries in Loops
# Bad
user_ids.each { |id| User.find(id) } # N queries!
# Good: Batch fetch
users = User.where(id: user_ids)
Missing Eager Loading in Nested Relations
# Bad: loads posts, then N queries for comments
users = User.includes(:posts)
users.each { |u| u.posts.each { |p| p.comments } }
# Good: Nested eager loading
users = User.includes(posts: :comments)
Best Practices
- Index Selectively -- too many indexes slow down writes
- Monitor Query Performance -- use slow query logs
- Keep Statistics Updated -- run ANALYZE regularly
- Use Appropriate Data Types -- smaller types = better performance
- Normalize Thoughtfully -- balance normalization vs performance
- Cache Frequently Accessed Data -- use application-level caching
- Connection Pooling -- reuse database connections
- Regular Maintenance -- VACUUM, ANALYZE, rebuild indexes
Common Pitfalls
- Over-Indexing: Each index slows down INSERT/UPDATE/DELETE
- Unused Indexes: Waste space and slow writes
- Missing Indexes: Slow queries, full table scans
- Implicit Type Conversion: Prevents index usage
- OR Conditions: Can't use indexes efficiently
- LIKE with Leading Wildcard:
LIKE '%abc'can't use index - Function in WHERE: Prevents index usage unless functional index exists
Reference Material
| Topic | File |
|---|---|
| Aggregate optimization, subquery patterns, batch operations | references/advanced-patterns.md |
| Materialized views, partitioning, query hints | references/advanced-patterns.md |
| Monitoring queries, maintenance commands | references/advanced-patterns.md |