1---
2name: sql-optimization-patterns
3description: Master SQL query optimization, indexing strategies, and EXPLAIN analysis to dramatically improve database performance and eliminate slow queries. Use when debugging slow queries, designing database schemas, or optimizing application performance.
4---
5 
6# SQL Optimization Patterns
7 
8Transform slow database queries into lightning-fast operations through systematic optimization, proper indexing, and query plan analysis.
9 
10## When to Use This Skill
11 
12- Debugging slow-running queries
13- Designing performant database schemas
14- Optimizing application response times
15- Reducing database load and costs
16- Improving scalability for growing datasets
17- Analyzing EXPLAIN query plans
18- Implementing efficient indexes
19- Resolving N+1 query problems
20 
21## Core Concepts
22 
23### 1. Query Execution Plans (EXPLAIN)
24 
25Understanding EXPLAIN output is fundamental to optimization.
26 
27**PostgreSQL EXPLAIN:**
28 
29```sql
30-- Basic explain
31EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
32 
33-- With actual execution stats
34EXPLAIN ANALYZE
35SELECT * FROM users WHERE email = '[email protected]';
36 
37-- Verbose output with more details
38EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
39SELECT u.*, o.order_total
40FROM users u
41JOIN orders o ON u.id = o.user_id
42WHERE u.created_at > NOW() - INTERVAL '30 days';
43```
44 
45**Key Metrics to Watch:**
46 
47- **Seq Scan**: Full table scan (usually slow for large tables)
48- **Index Scan**: Using index (good)
49- **Index Only Scan**: Using index without touching table (best)
50- **Nested Loop**: Join method (okay for small datasets)
51- **Hash Join**: Join method (good for larger datasets)
52- **Merge Join**: Join method (good for sorted data)
53- **Cost**: Estimated query cost (lower is better)
54- **Rows**: Estimated rows returned
55- **Actual Time**: Real execution time
56 
57### 2. Index Strategies
58 
59Indexes are the most powerful optimization tool.
60 
61**Index Types:**
62 
63- **B-Tree**: Default, good for equality and range queries
64- **Hash**: Only for equality (=) comparisons
65- **GIN**: Full-text search, array queries, JSONB
66- **GiST**: Geometric data, full-text search
67- **BRIN**: Block Range INdex for very large tables with correlation
68 
69```sql
70-- Standard B-Tree index
71CREATE INDEX idx_users_email ON users(email);
72 
73-- Composite index (order matters!)
74CREATE INDEX idx_orders_user_status ON orders(user_id, status);
75 
76-- Partial index (index subset of rows)
77CREATE INDEX idx_active_users ON users(email)
78WHERE status = 'active';
79 
80-- Expression index
81CREATE INDEX idx_users_lower_email ON users(LOWER(email));
82 
83-- Covering index (include additional columns)
84CREATE INDEX idx_users_email_covering ON users(email)
85INCLUDE (name, created_at);
86 
87-- Full-text search index
88CREATE INDEX idx_posts_search ON posts
89USING GIN(to_tsvector('english', title || ' ' || body));
90 
91-- JSONB index
92CREATE INDEX idx_metadata ON events USING GIN(metadata);
93```
94 
95### 3. Query Optimization Patterns
96 
97**Avoid SELECT \*:**
98 
99```sql
100-- Bad: Fetches unnecessary columns
101SELECT * FROM users WHERE id = 123;
102 
103-- Good: Fetch only what you need
104SELECT id, email, name FROM users WHERE id = 123;
105```
106 
107**Use WHERE Clause Efficiently:**
108 
109```sql
110-- Bad: Function prevents index usage
111SELECT * FROM users WHERE LOWER(email) = '[email protected]';
112 
113-- Good: Create functional index or use exact match
114CREATE INDEX idx_users_email_lower ON users(LOWER(email));
115-- Then:
116SELECT * FROM users WHERE LOWER(email) = '[email protected]';
117 
118-- Or store normalized data
119SELECT * FROM users WHERE email = '[email protected]';
120```
121 
122**Optimize JOINs:**
123 
124```sql
125-- Bad: Cartesian product then filter
126SELECT u.name, o.total
127FROM users u, orders o
128WHERE u.id = o.user_id AND u.created_at > '2024-01-01';
129 
130-- Good: Filter before join
131SELECT u.name, o.total
132FROM users u
133JOIN orders o ON u.id = o.user_id
134WHERE u.created_at > '2024-01-01';
135 
136-- Better: Filter both tables
137SELECT u.name, o.total
138FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
139JOIN orders o ON u.id = o.user_id;
140```
141 
142## Detailed patterns and worked examples
143 
144Detailed pattern documentation lives in `references/details.md`. Read that file when the navigation tier above is insufficient.
145 
146## Best Practices
147 
1481. **Index Selectively**: Too many indexes slow down writes
1492. **Monitor Query Performance**: Use slow query logs
1503. **Keep Statistics Updated**: Run ANALYZE regularly
1514. **Use Appropriate Data Types**: Smaller types = better performance
1525. **Normalize Thoughtfully**: Balance normalization vs performance
1536. **Cache Frequently Accessed Data**: Use application-level caching
1547. **Connection Pooling**: Reuse database connections
1558. **Regular Maintenance**: VACUUM, ANALYZE, rebuild indexes
156 
157```sql
158-- Update statistics
159ANALYZE users;
160ANALYZE VERBOSE orders;
161 
162-- Vacuum (PostgreSQL)
163VACUUM ANALYZE users;
164VACUUM FULL users;  -- Reclaim space (locks table)
165 
166-- Reindex
167REINDEX INDEX idx_users_email;
168REINDEX TABLE users;
169```
170 
171## Common Pitfalls
172 
173- **Over-Indexing**: Each index slows down INSERT/UPDATE/DELETE
174- **Unused Indexes**: Waste space and slow writes
175- **Missing Indexes**: Slow queries, full table scans
176- **Implicit Type Conversion**: Prevents index usage
177- **OR Conditions**: Can't use indexes efficiently
178- **LIKE with Leading Wildcard**: `LIKE '%abc'` can't use index
179- **Function in WHERE**: Prevents index usage unless functional index exists
180 
181## Monitoring Queries
182 
183```sql
184-- Find slow queries (PostgreSQL)
185SELECT query, calls, total_time, mean_time
186FROM pg_stat_statements
187ORDER BY mean_time DESC
188LIMIT 10;
189 
190-- Find missing indexes (PostgreSQL)
191SELECT
192    schemaname,
193    tablename,
194    seq_scan,
195    seq_tup_read,
196    idx_scan,
197    seq_tup_read / seq_scan AS avg_seq_tup_read
198FROM pg_stat_user_tables
199WHERE seq_scan > 0
200ORDER BY seq_tup_read DESC
201LIMIT 10;
202 
203-- Find unused indexes (PostgreSQL)
204SELECT
205    schemaname,
206    tablename,
207    indexname,
208    idx_scan,
209    idx_tup_read,
210    idx_tup_fetch
211FROM pg_stat_user_indexes
212WHERE idx_scan = 0
213ORDER BY pg_relation_size(indexrelid) DESC;
214```
215