sql-optimization-patterns

SQL Optimization Patterns

Transform slow database queries into lightning-fast operations through systematic optimization, proper indexing, and query plan analysis.

When to Use

• 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)

Understanding EXPLAIN output is fundamental to optimization.

PostgreSQL EXPLAIN:

-- Basic explain
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- With actual execution stats
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';

-- Verbose output with more details
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 to Watch:

• Seq Scan: Full table scan (usually slow for large tables)
• Index Scan: Using index (good)
• Index Only Scan: Using index without touching table (best)
• Nested Loop: Join method (okay for small datasets)
• Hash Join: Join method (good for larger datasets)
• Merge Join: Join method (good for sorted data)
• Cost: Estimated query cost (lower is better)
• Rows: Estimated rows returned
• Actual Time: Real execution time

2. Index Strategies

Indexes are the most powerful optimization tool.

Index Types:

• B-Tree: Default, good for equality and range queries
• Hash: Only for equality (=) comparisons
• GIN: Full-text search, array queries, JSONB
• GiST: Geometric data, full-text search
• BRIN: Block Range INdex for very large tables with correlation

-- Standard B-Tree index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (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 (include additional columns)
CREATE INDEX idx_users_email_covering ON users(email)
INCLUDE (name, created_at);

-- Full-text search index
CREATE INDEX idx_posts_search ON posts
USING GIN(to_tsvector('english', title || ' ' || body));

-- JSONB index
CREATE INDEX idx_metadata ON events USING GIN(metadata);