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## Optimization Patterns
143 
144### Pattern 1: Eliminate N+1 Queries
145 
146**Problem: N+1 Query Anti-Pattern**
147 
148```python
149# Bad: Executes N+1 queries
150users = db.query("SELECT * FROM users LIMIT 10")
151for user in users:
152    orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)
153    # Process orders
154```
155 
156**Solution: Use JOINs or Batch Loading**
157 
158```sql
159-- Solution 1: JOIN
160SELECT
161    u.id, u.name,
162    o.id as order_id, o.total
163FROM users u
164LEFT JOIN orders o ON u.id = o.user_id
165WHERE u.id IN (1, 2, 3, 4, 5);
166 
167-- Solution 2: Batch query
168SELECT * FROM orders
169WHERE user_id IN (1, 2, 3, 4, 5);
170```
171 
172```python
173# Good: Single query with JOIN or batch load
174# Using JOIN
175results = db.query("""
176    SELECT u.id, u.name, o.id as order_id, o.total
177    FROM users u
178    LEFT JOIN orders o ON u.id = o.user_id
179    WHERE u.id IN (1, 2, 3, 4, 5)
180""")
181 
182# Or batch load
183users = db.query("SELECT * FROM users LIMIT 10")
184user_ids = [u.id for u in users]
185orders = db.query(
186    "SELECT * FROM orders WHERE user_id IN (?)",
187    user_ids
188)
189# Group orders by user_id
190orders_by_user = {}
191for order in orders:
192    orders_by_user.setdefault(order.user_id, []).append(order)
193```
194 
195### Pattern 2: Optimize Pagination
196 
197**Bad: OFFSET on Large Tables**
198 
199```sql
200-- Slow for large offsets
201SELECT * FROM users
202ORDER BY created_at DESC
203LIMIT 20 OFFSET 100000;  -- Very slow!
204```
205 
206**Good: Cursor-Based Pagination**
207 
208```sql
209-- Much faster: Use cursor (last seen ID)
210SELECT * FROM users
211WHERE created_at < '2024-01-15 10:30:00'  -- Last cursor
212ORDER BY created_at DESC
213LIMIT 20;
214 
215-- With composite sorting
216SELECT * FROM users
217WHERE (created_at, id) < ('2024-01-15 10:30:00', 12345)
218ORDER BY created_at DESC, id DESC
219LIMIT 20;
220 
221-- Requires index
222CREATE INDEX idx_users_cursor ON users(created_at DESC, id DESC);
223```
224 
225### Pattern 3: Aggregate Efficiently
226 
227**Optimize COUNT Queries:**
228 
229```sql
230-- Bad: Counts all rows
231SELECT COUNT(*) FROM orders;  -- Slow on large tables
232 
233-- Good: Use estimates for approximate counts
234SELECT reltuples::bigint AS estimate
235FROM pg_class
236WHERE relname = 'orders';
237 
238-- Good: Filter before counting
239SELECT COUNT(*) FROM orders
240WHERE created_at > NOW() - INTERVAL '7 days';
241 
242-- Better: Use index-only scan
243CREATE INDEX idx_orders_created ON orders(created_at);
244SELECT COUNT(*) FROM orders
245WHERE created_at > NOW() - INTERVAL '7 days';
246```
247 
248**Optimize GROUP BY:**
249 
250```sql
251-- Bad: Group by then filter
252SELECT user_id, COUNT(*) as order_count
253FROM orders
254GROUP BY user_id
255HAVING COUNT(*) > 10;
256 
257-- Better: Filter first, then group (if possible)
258SELECT user_id, COUNT(*) as order_count
259FROM orders
260WHERE status = 'completed'
261GROUP BY user_id
262HAVING COUNT(*) > 10;
263 
264-- Best: Use covering index
265CREATE INDEX idx_orders_user_status ON orders(user_id, status);
266```
267 
268### Pattern 4: Subquery Optimization
269 
270**Transform Correlated Subqueries:**
271 
272```sql
273-- Bad: Correlated subquery (runs for each row)
274SELECT u.name, u.email,
275    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
276FROM users u;
277 
278-- Good: JOIN with aggregation
279SELECT u.name, u.email, COUNT(o.id) as order_count
280FROM users u
281LEFT JOIN orders o ON o.user_id = u.id
282GROUP BY u.id, u.name, u.email;
283 
284-- Better: Use window functions
285SELECT DISTINCT ON (u.id)
286    u.name, u.email,
287    COUNT(o.id) OVER (PARTITION BY u.id) as order_count
288FROM users u
289LEFT JOIN orders o ON o.user_id = u.id;
290```
291 
292**Use CTEs for Clarity:**
293 
294```sql
295-- Using Common Table Expressions
296WITH recent_users AS (
297    SELECT id, name, email
298    FROM users
299    WHERE created_at > NOW() - INTERVAL '30 days'
300),
301user_order_counts AS (
302    SELECT user_id, COUNT(*) as order_count
303    FROM orders
304    WHERE created_at > NOW() - INTERVAL '30 days'
305    GROUP BY user_id
306)
307SELECT ru.name, ru.email, COALESCE(uoc.order_count, 0) as orders
308FROM recent_users ru
309LEFT JOIN user_order_counts uoc ON ru.id = uoc.user_id;
310```
311 
312### Pattern 5: Batch Operations
313 
314**Batch INSERT:**
315 
316```sql
317-- Bad: Multiple individual inserts
318INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');
319INSERT INTO users (name, email) VALUES ('Bob', '[email protected]');
320INSERT INTO users (name, email) VALUES ('Carol', '[email protected]');
321 
322-- Good: Batch insert
323INSERT INTO users (name, email) VALUES
324    ('Alice', '[email protected]'),
325    ('Bob', '[email protected]'),
326    ('Carol', '[email protected]');
327 
328-- Better: Use COPY for bulk inserts (PostgreSQL)
329COPY users (name, email) FROM '/tmp/users.csv' CSV HEADER;
330```
331 
332**Batch UPDATE:**
333 
334```sql
335-- Bad: Update in loop
336UPDATE users SET status = 'active' WHERE id = 1;
337UPDATE users SET status = 'active' WHERE id = 2;
338-- ... repeat for many IDs
339 
340-- Good: Single UPDATE with IN clause
341UPDATE users
342SET status = 'active'
343WHERE id IN (1, 2, 3, 4, 5, ...);
344 
345-- Better: Use temporary table for large batches
346CREATE TEMP TABLE temp_user_updates (id INT, new_status VARCHAR);
347INSERT INTO temp_user_updates VALUES (1, 'active'), (2, 'active'), ...;
348 
349UPDATE users u
350SET status = t.new_status
351FROM temp_user_updates t
352WHERE u.id = t.id;
353```
354 
355## Advanced Techniques
356 
357### Materialized Views
358 
359Pre-compute expensive queries.
360 
361```sql
362-- Create materialized view
363CREATE MATERIALIZED VIEW user_order_summary AS
364SELECT
365    u.id,
366    u.name,
367    COUNT(o.id) as total_orders,
368    SUM(o.total) as total_spent,
369    MAX(o.created_at) as last_order_date
370FROM users u
371LEFT JOIN orders o ON u.id = o.user_id
372GROUP BY u.id, u.name;
373 
374-- Add index to materialized view
375CREATE INDEX idx_user_summary_spent ON user_order_summary(total_spent DESC);
376 
377-- Refresh materialized view
378REFRESH MATERIALIZED VIEW user_order_summary;
379 
380-- Concurrent refresh (PostgreSQL)
381REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary;
382 
383-- Query materialized view (very fast)
384SELECT * FROM user_order_summary
385WHERE total_spent > 1000
386ORDER BY total_spent DESC;
387```
388 
389### Partitioning
390 
391Split large tables for better performance.
392 
393```sql
394-- Range partitioning by date (PostgreSQL)
395CREATE TABLE orders (
396    id SERIAL,
397    user_id INT,
398    total DECIMAL,
399    created_at TIMESTAMP
400) PARTITION BY RANGE (created_at);
401 
402-- Create partitions
403CREATE TABLE orders_2024_q1 PARTITION OF orders
404    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
405 
406CREATE TABLE orders_2024_q2 PARTITION OF orders
407    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
408 
409-- Queries automatically use appropriate partition
410SELECT * FROM orders
411WHERE created_at BETWEEN '2024-02-01' AND '2024-02-28';
412-- Only scans orders_2024_q1 partition
413```
414 
415### Query Hints and Optimization
416 
417```sql
418-- Force index usage (MySQL)
419SELECT * FROM users
420USE INDEX (idx_users_email)
421WHERE email = '[email protected]';
422 
423-- Parallel query (PostgreSQL)
424SET max_parallel_workers_per_gather = 4;
425SELECT * FROM large_table WHERE condition;
426 
427-- Join hints (PostgreSQL)
428SET enable_nestloop = OFF;  -- Force hash or merge join
429```
430 
431## Best Practices
432 
4331. **Index Selectively**: Too many indexes slow down writes
4342. **Monitor Query Performance**: Use slow query logs
4353. **Keep Statistics Updated**: Run ANALYZE regularly
4364. **Use Appropriate Data Types**: Smaller types = better performance
4375. **Normalize Thoughtfully**: Balance normalization vs performance
4386. **Cache Frequently Accessed Data**: Use application-level caching
4397. **Connection Pooling**: Reuse database connections
4408. **Regular Maintenance**: VACUUM, ANALYZE, rebuild indexes
441 
442```sql
443-- Update statistics
444ANALYZE users;
445ANALYZE VERBOSE orders;
446 
447-- Vacuum (PostgreSQL)
448VACUUM ANALYZE users;
449VACUUM FULL users;  -- Reclaim space (locks table)
450 
451-- Reindex
452REINDEX INDEX idx_users_email;
453REINDEX TABLE users;
454```
455 
456## Common Pitfalls
457 
458- **Over-Indexing**: Each index slows down INSERT/UPDATE/DELETE
459- **Unused Indexes**: Waste space and slow writes
460- **Missing Indexes**: Slow queries, full table scans
461- **Implicit Type Conversion**: Prevents index usage
462- **OR Conditions**: Can't use indexes efficiently
463- **LIKE with Leading Wildcard**: `LIKE '%abc'` can't use index
464- **Function in WHERE**: Prevents index usage unless functional index exists
465 
466## Monitoring Queries
467 
468```sql
469-- Find slow queries (PostgreSQL)
470SELECT query, calls, total_time, mean_time
471FROM pg_stat_statements
472ORDER BY mean_time DESC
473LIMIT 10;
474 
475-- Find missing indexes (PostgreSQL)
476SELECT
477    schemaname,
478    tablename,
479    seq_scan,
480    seq_tup_read,
481    idx_scan,
482    seq_tup_read / seq_scan AS avg_seq_tup_read
483FROM pg_stat_user_tables
484WHERE seq_scan > 0
485ORDER BY seq_tup_read DESC
486LIMIT 10;
487 
488-- Find unused indexes (PostgreSQL)
489SELECT
490    schemaname,
491    tablename,
492    indexname,
493    idx_scan,
494    idx_tup_read,
495    idx_tup_fetch
496FROM pg_stat_user_indexes
497WHERE idx_scan = 0
498ORDER BY pg_relation_size(indexrelid) DESC;
499```
500