SQL Optimization

1---
2name: sql-optimization
3description: SQL query optimization patterns including EXPLAIN plan analysis, index strategies, query rewriting, and N+1 query prevention. Use when optimizing slow database queries, analyzing query performance, designing indexes, or debugging database bottlenecks. Works with PostgreSQL, MySQL, SQLite, and other SQL databases. Typical improvements: 10x-1000x query speedup.
4---
5 
6# SQL Query Optimization Patterns
7 
8Comprehensive 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.
9 
10---
11 
12## Quick Reference
13 
14**When to use this skill:**
15- Slow database queries (>100ms for simple queries, >1s for complex)
16- High database CPU usage
17- Analyzing EXPLAIN plans
18- Designing database indexes
19- Debugging N+1 query problems
20- Optimizing JOIN operations
21- Reducing table scans
22 
23**Common triggers:**
24- "This query is too slow"
25- "How do I optimize this SQL"
26- "What indexes should I add"
27- "Explain this EXPLAIN plan"
28- "Fix N+1 queries"
29- "Database CPU at 100%"
30 
31**Typical improvements:**
32- 3 seconds → 50ms (60x faster)
33- Full table scan → Index scan
34- 1000 queries → 2 queries (N+1 elimination)
35 
36---
37 
38## Part 1: Understanding EXPLAIN Plans
39 
40### Reading PostgreSQL EXPLAIN
41 
42```sql
43EXPLAIN ANALYZE
44SELECT u.name, p.title
45FROM users u
46JOIN posts p ON u.id = p.user_id
47WHERE u.created_at > '2024-01-01'
48ORDER BY p.created_at DESC
49LIMIT 10;
50```
51 
52**Key Metrics to Watch**:
53- **Seq Scan** (bad): Full table scan, reads every row
54- **Index Scan** (good): Uses index, reads only needed rows
55- **Cost**: Estimated computational cost (lower is better)
56- **Actual time**: Real execution time in milliseconds
57- **Rows**: Number of rows processed at each step
58 
59### Bad EXPLAIN Example
60 
61```
62Seq Scan on users u  (cost=0.00..1234.00 rows=1000 width=50)
63                     (actual time=0.123..45.678 rows=950 loops=1)
64  Filter: (created_at > '2024-01-01'::date)
65  Rows Removed by Filter: 50000
66Planning Time: 0.234 ms
67Execution Time: 3456.789 ms
68```
69 
70**Problems**:
71- **Seq Scan**: Reading entire table (50,950 rows)
72- **Rows Removed by Filter**: Filtering after reading (wasteful)
73- **Execution Time**: 3.5 seconds (way too slow)
74 
75### Good EXPLAIN Example (After Index)
76 
77```
78Index Scan using users_created_at_idx on users u
79  (cost=0.29..123.45 rows=950 width=50)
80  (actual time=0.012..3.456 rows=950 loops=1)
81  Index Cond: (created_at > '2024-01-01'::date)
82Planning Time: 0.123 ms
83Execution Time: 4.567 ms
84```
85 
86**Improvements**:
87- **Index Scan**: Using index (only reads needed rows)
88- **Index Cond**: Filtering during index scan (efficient)
89- **Execution Time**: 4.5ms (750x faster!)
90 
91---
92 
93## Part 2: Index Design Strategies
94 
95### When to Add an Index
96 
97**✅ Add index when:**
98- Column frequently in WHERE clauses
99- Column frequently in JOIN conditions
100- Column frequently in ORDER BY
101- Query does Seq Scan on large table (>10K rows)
102- Query execution time >100ms
103 
104**❌ Don't add index when:**
105- Table has <1000 rows (Seq Scan is fast enough)
106- Column rarely queried
107- Column has very low cardinality (e.g., boolean with 50/50 distribution)
108- Table has heavy writes (indexes slow down INSERTs/UPDATEs)
109 
110### Single-Column Indexes
111 
112```sql
113-- Create index on frequently queried column
114CREATE INDEX idx_users_email ON users(email);
115CREATE INDEX idx_posts_user_id ON posts(user_id);
116CREATE INDEX idx_orders_created_at ON orders(created_at);
117```
118 
119**Query that benefits**:
120```sql
121-- Before index: Seq Scan (3000ms)
122-- After index: Index Scan (5ms)
123SELECT * FROM users WHERE email = 'user@example.com';
124```
125 
126### Composite Indexes (Multi-Column)
127 
128**Order matters!** Put most selective column first.
129 
130```sql
131-- Good: Specific to general
132CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at);
133 
134-- Query that uses all columns
135SELECT * FROM orders
136WHERE user_id = 123
137  AND status = 'pending'
138ORDER BY created_at DESC;
139```
140 
141**Index usage rules**:
142```sql
143-- ✅ Uses index (matches from left)
144WHERE user_id = 123
145WHERE user_id = 123 AND status = 'pending'
146WHERE user_id = 123 AND status = 'pending' AND created_at > '2024-01-01'
147 
148-- ❌ Doesn't use index (skips first column)
149WHERE status = 'pending'
150WHERE created_at > '2024-01-01'
151```
152 
153### Partial Indexes (Filtered)
154 
155**Index only subset of rows** (smaller, faster):
156 
157```sql
158-- Only index active users
159CREATE INDEX idx_users_active_email
160ON users(email)
161WHERE status = 'active';
162 
163-- Query that uses partial index
164SELECT * FROM users
165WHERE email = 'user@example.com'
166  AND status = 'active';  -- Must include filter condition!
167```
168 
169**Benefits**:
170- Smaller index (faster scans, less storage)
171- Only indexes rows you actually query
172- Great for status fields with skewed distribution
173 
174### Covering Indexes (INCLUDE)
175 
176**Include columns in index** to avoid table access:
177 
178```sql
179-- Postgres: INCLUDE clause
180CREATE INDEX idx_users_email_covering
181ON users(email)
182INCLUDE (name, created_at);
183 
184-- Query doesn't need to access table (all data in index)
185SELECT name, created_at FROM users WHERE email = 'user@example.com';
186```
187 
188**Benefit**: Index-Only Scan (even faster than Index Scan)
189 
190---
191 
192## Part 3: Query Rewriting Patterns
193 
194### Problem 1: SELECT * (Fetching Unnecessary Data)
195 
196**❌ Bad** (fetches all columns):
197```sql
198SELECT * FROM users WHERE id = 123;
199```
200 
201**✅ Good** (fetches only needed columns):
202```sql
203SELECT id, name, email FROM users WHERE id = 123;
204```
205 
206**Impact**:
207- Smaller result set
208- Less memory usage
209- Faster network transfer
210- Can use covering indexes
211 
212### Problem 2: N+1 Queries
213 
214**❌ Bad** (1 query for posts + N queries for users):
215```python
216# 1 query
217posts = db.execute("SELECT * FROM posts LIMIT 10")
218 
219# N queries (10 separate queries!)
220for post in posts:
221    user = db.execute("SELECT * FROM users WHERE id = ?", post.user_id)
222    print(f"{user.name}: {post.title}")
223```
224 
225**✅ Good** (2 queries total):
226```python
227# 1 query for posts
228posts = db.execute("SELECT * FROM posts LIMIT 10")
229 
230# 1 query for all users
231user_ids = [p.user_id for p in posts]
232users = db.execute("SELECT * FROM users WHERE id IN (?)", user_ids)
233users_by_id = {u.id: u for u in users}
234 
235# No additional queries
236for post in posts:
237    user = users_by_id[post.user_id]
238    print(f"{user.name}: {post.title}")
239```
240 
241**Even Better** (1 query with JOIN):
242```sql
243SELECT u.name, p.title
244FROM posts p
245JOIN users u ON p.user_id = u.id
246LIMIT 10;
247```
248 
249### Problem 3: Implicit Type Conversion
250 
251**❌ Bad** (index not used due to implicit conversion):
252```sql
253-- user_id is INTEGER, but '123' is string
254SELECT * FROM posts WHERE user_id = '123';  -- Seq Scan!
255```
256 
257**✅ Good** (explicit type, index used):
258```sql
259SELECT * FROM posts WHERE user_id = 123;  -- Index Scan
260```
261 
262### Problem 4: Function Calls in WHERE Clause
263 
264**❌ Bad** (can't use index):
265```sql
266-- Function prevents index usage
267SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
268```
269 
270**✅ Good** (use functional index):
271```sql
272-- Create index on function result
273CREATE INDEX idx_users_lower_email ON users(LOWER(email));
274 
275-- Now query can use index
276SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
277```
278 
279**✅ Even Better** (store lowercase, regular index):
280```sql
281-- Store email in lowercase
282CREATE INDEX idx_users_email ON users(email);
283 
284-- Query with lowercase value
285SELECT * FROM users WHERE email = LOWER('user@example.com');
286```
287 
288### Problem 5: OR Conditions
289 
290**❌ Bad** (often does Seq Scan):
291```sql
292SELECT * FROM users
293WHERE email = 'user@example.com'
294   OR username = 'johndoe';
295```
296 
297**✅ Good** (use UNION, allows index usage):
298```sql
299SELECT * FROM users WHERE email = 'user@example.com'
300UNION
301SELECT * FROM users WHERE username = 'johndoe';
302```
303 
304### Problem 6: NOT IN with Subquery
305 
306**❌ Bad** (very slow, especially with large subquery):
307```sql
308SELECT * FROM users
309WHERE id NOT IN (SELECT user_id FROM blocked_users);
310```
311 
312**✅ Good** (use LEFT JOIN with NULL check):
313```sql
314SELECT u.*
315FROM users u
316LEFT JOIN blocked_users b ON u.id = b.user_id
317WHERE b.user_id IS NULL;
318```
319 
320---
321 
322## Part 4: JOIN Optimization
323 
324### JOIN Order Matters
325 
326Database optimizes join order, but you can help:
327 
328**Small table first, large table second**:
329```sql
330-- Good: Join small table (categories: 10 rows) to large table (products: 10M rows)
331SELECT p.*
332FROM categories c
333JOIN products p ON c.id = p.category_id
334WHERE c.name = 'Electronics';
335```
336 
337### Avoid Cartesian Products
338 
339**❌ Bad** (produces rows1 × rows2 × rows3):
340```sql
341SELECT *
342FROM users, posts, comments;  -- 100 × 10000 × 50000 = 50 billion rows!
343```
344 
345**✅ Good** (proper JOIN conditions):
346```sql
347SELECT *
348FROM users u
349JOIN posts p ON u.id = p.user_id
350JOIN comments c ON p.id = c.post_id;
351```
352 
353### EXISTS vs IN for Subqueries
354 
355**Scenario**: Find users who have at least one post
356 
357**❌ Slower** (IN loads all post IDs):
358```sql
359SELECT * FROM users
360WHERE id IN (SELECT user_id FROM posts);
361```
362 
363**✅ Faster** (EXISTS stops at first match):
364```sql
365SELECT * FROM users u
366WHERE EXISTS (SELECT 1 FROM posts p WHERE p.user_id = u.id);
367```
368 
369### JOIN vs Subquery
370 
371**Use JOIN when** you need data from both tables:
372```sql
373SELECT u.name, p.title
374FROM users u
375JOIN posts p ON u.id = p.user_id;
376```
377 
378**Use Subquery when** you only need filtering:
379```sql
380SELECT * FROM users
381WHERE id IN (SELECT DISTINCT user_id FROM posts);
382```
383 
384---
385 
386## Part 5: Aggregation Optimization
387 
388### COUNT(*) Optimization
389 
390**Problem**: COUNT(*) on large tables is slow
391 
392**❌ Slow** (counts all rows):
393```sql
394SELECT COUNT(*) FROM orders;  -- Full table scan on 10M rows
395```
396 
397**✅ Faster** (approximate count from statistics):
398```sql
399-- PostgreSQL: Use statistics (fast but approximate)
400SELECT reltuples::bigint AS estimate
401FROM pg_class
402WHERE relname = 'orders';
403```
404 
405**✅ Also Good** (exact count with index):
406```sql
407-- If you have an index, COUNT can use index-only scan
408CREATE INDEX idx_orders_id ON orders(id);
409SELECT COUNT(*) FROM orders;  -- Index-only scan
410```
411 
412### GROUP BY Optimization
413 
414**❌ Bad** (groups after fetching all data):
415```sql
416SELECT user_id, COUNT(*)
417FROM posts
418GROUP BY user_id;
419```
420 
421**✅ Good** (index helps grouping):
422```sql
423-- Create index on grouped column
424CREATE INDEX idx_posts_user_id ON posts(user_id);
425 
426SELECT user_id, COUNT(*)
427FROM posts
428GROUP BY user_id;  -- Can use index for grouping
429```
430 
431### HAVING vs WHERE
432 
433**❌ Bad** (filters after grouping):
434```sql
435SELECT user_id, COUNT(*) as post_count
436FROM posts
437GROUP BY user_id
438HAVING user_id > 1000;  -- Filters after grouping all users
439```
440 
441**✅ Good** (filters before grouping):
442```sql
443SELECT user_id, COUNT(*) as post_count
444FROM posts
445WHERE user_id > 1000  -- Filters before grouping (can use index)
446GROUP BY user_id;
447```
448 
449---
450 
451## Part 6: Pagination Optimization
452 
453### OFFSET is Slow for Large Offsets
454 
455**❌ Bad** (OFFSET scans all skipped rows):
456```sql
457-- Page 1000 (skips 50,000 rows, then returns 50)
458SELECT * FROM posts
459ORDER BY created_at DESC
460LIMIT 50 OFFSET 50000;  -- Scans 50,050 rows!
461```
462 
463**✅ Good** (keyset pagination with WHERE):
464```sql
465-- Save last seen ID from previous page
466SELECT * FROM posts
467WHERE created_at < '2024-01-15 10:30:00'  -- Last seen timestamp
468ORDER BY created_at DESC
469LIMIT 50;  -- Only fetches 50 rows
470```
471 
472**Implementation**:
473```python
474# Page 1
475results = db.execute("""
476    SELECT id, created_at, title
477    FROM posts
478    ORDER BY created_at DESC, id DESC
479    LIMIT 50
480""")
481last_created_at = results[-1].created_at
482last_id = results[-1].id
483 
484# Page 2 (using last seen values)
485results = db.execute("""
486    SELECT id, created_at, title
487    FROM posts
488    WHERE (created_at, id) < (%s, %s)
489    ORDER BY created_at DESC, id DESC
490    LIMIT 50
491""", last_created_at, last_id)
492```
493 
494**Benefits**:
495- Consistent performance for any page number
496- No OFFSET overhead
497- Works with millions of rows
498 
499---
500 
501## Part 7: Database-Specific Patterns
502 
503### PostgreSQL-Specific
504 
505**1. EXPLAIN ANALYZE** (shows actual execution):
506```sql
507EXPLAIN (ANALYZE, BUFFERS)
508SELECT * FROM users WHERE email = 'user@example.com';
509```
510 
511**2. pg_stat_statements** (find slow queries):
512```sql
513-- Enable extension
514CREATE EXTENSION pg_stat_statements;
515 
516-- Find slowest queries
517SELECT
518    calls,
519    mean_exec_time,
520    total_exec_time,
521    query
522FROM pg_stat_statements
523ORDER BY mean_exec_time DESC
524LIMIT 10;
525```
526 
527**3. VACUUM and ANALYZE**:
528```sql
529-- Update statistics after bulk changes
530VACUUM ANALYZE table_name;
531```
532 
533### MySQL-Specific
534 
535**1. EXPLAIN FORMAT=JSON** (detailed output):
536```sql
537EXPLAIN FORMAT=JSON
538SELECT * FROM users WHERE email = 'user@example.com';
539```
540 
541**2. SHOW PROFILE** (detailed timing):
542```sql
543SET profiling = 1;
544SELECT * FROM users WHERE email = 'user@example.com';
545SHOW PROFILE FOR QUERY 1;
546```
547 
548**3. Query Cache** (deprecated in MySQL 8.0):
549```sql
550-- Old MySQL versions only
551SHOW VARIABLES LIKE 'query_cache%';
552```
553 
554### SQLite-Specific
555 
556**1. EXPLAIN QUERY PLAN**:
557```sql
558EXPLAIN QUERY PLAN
559SELECT * FROM users WHERE email = 'user@example.com';
560```
561 
562**2. ANALYZE** (update statistics):
563```sql
564ANALYZE table_name;
565```
566 
567**3. Indexes are critical** (SQLite has no query optimizer):
568```sql
569-- SQLite relies heavily on indexes
570CREATE INDEX idx_users_email ON users(email);
571```
572 
573---
574 
575## Part 8: Monitoring and Diagnosis
576 
577### Find Missing Indexes (PostgreSQL)
578 
579```sql
580-- Queries doing sequential scans on large tables
581SELECT
582    schemaname,
583    tablename,
584    seq_scan,
585    seq_tup_read,
586    idx_scan,
587    seq_tup_read / seq_scan AS avg_seq_read
588FROM pg_stat_user_tables
589WHERE seq_scan > 0
590ORDER BY seq_tup_read DESC
591LIMIT 25;
592```
593 
594### Find Unused Indexes (PostgreSQL)
595 
596```sql
597-- Indexes that are never used
598SELECT
599    schemaname,
600    tablename,
601    indexname,
602    idx_scan,
603    pg_size_pretty(pg_relation_size(indexrelid)) AS size
604FROM pg_stat_user_indexes
605WHERE idx_scan = 0
606  AND indexrelname NOT LIKE '%_pkey'  -- Exclude primary keys
607ORDER BY pg_relation_size(indexrelid) DESC;
608```
609 
610### Check Index Bloat
611 
612```sql
613-- Postgres: Check for bloated indexes
614SELECT
615    schemaname,
616    tablename,
617    indexname,
618    pg_size_pretty(pg_relation_size(indexrelid)) AS size
619FROM pg_stat_user_indexes
620ORDER BY pg_relation_size(indexrelid) DESC;
621 
622-- Rebuild bloated indexes
623REINDEX INDEX index_name;
624```
625 
626---
627 
628## Part 9: Common Patterns by Use Case
629 
630### Pattern 1: Recent Records with Pagination
631 
632```sql
633-- Create composite index
634CREATE INDEX idx_posts_created_user ON posts(created_at DESC, user_id);
635 
636-- Query uses index efficiently
637SELECT * FROM posts
638WHERE user_id = 123
639ORDER BY created_at DESC
640LIMIT 20;
641```
642 
643### Pattern 2: Search Across Multiple Columns
644 
645```sql
646-- Create GIN index for full-text search (Postgres)
647CREATE INDEX idx_posts_search ON posts
648USING gin(to_tsvector('english', title || ' ' || content));
649 
650-- Fast full-text search
651SELECT * FROM posts
652WHERE to_tsvector('english', title || ' ' || content) @@
653      to_tsquery('english', 'database & optimization');
654```
655 
656### Pattern 3: Range Queries
657 
658```sql
659-- Create index on range column
660CREATE INDEX idx_orders_created_at ON orders(created_at);
661 
662-- Query uses index
663SELECT * FROM orders
664WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'
665ORDER BY created_at;
666```
667 
668### Pattern 4: Distinct Values with Filter
669 
670```sql
671-- Create composite index
672CREATE INDEX idx_posts_category_user ON posts(category_id, user_id);
673 
674-- Query uses index
675SELECT DISTINCT category_id
676FROM posts
677WHERE user_id = 123;
678```
679 
680---
681 
682## Part 10: Quick Optimization Checklist
683 
684**Diagnosis**:
685- [ ] Run EXPLAIN ANALYZE on slow queries
686- [ ] Check for Seq Scan on tables >10K rows
687- [ ] Look for missing indexes
688- [ ] Identify N+1 query patterns
689- [ ] Check query execution time (aim for <100ms)
690 
691**Index Strategy**:
692- [ ] Index columns in WHERE clauses
693- [ ] Index columns in JOIN conditions
694- [ ] Index columns in ORDER BY
695- [ ] Use composite indexes for multi-column queries
696- [ ] Consider partial indexes for filtered queries
697- [ ] Remove unused indexes
698 
699**Query Rewriting**:
700- [ ] Select only needed columns (not SELECT *)
701- [ ] Use JOIN instead of N+1 queries
702- [ ] Use proper types (avoid implicit conversion)
703- [ ] Avoid functions in WHERE (use functional indexes)
704- [ ] Use UNION instead of OR for multiple conditions
705- [ ] Use EXISTS instead of IN for large subqueries
706 
707**Pagination**:
708- [ ] Use keyset pagination (not OFFSET) for large datasets
709- [ ] Include stable sort column (id) in pagination
710- [ ] Index pagination columns
711 
712**Monitoring**:
713- [ ] Enable query logging for slow queries
714- [ ] Monitor index usage
715- [ ] Check for index bloat
716- [ ] Regularly VACUUM ANALYZE (Postgres)
717 
718---
719 
720## Resources
721 
722**PostgreSQL**:
723- EXPLAIN docs: https://www.postgresql.org/docs/current/sql-explain.html
724- Index types: https://www.postgresql.org/docs/current/indexes-types.html
725- Performance tips: https://wiki.postgresql.org/wiki/Performance_Optimization
726 
727**MySQL**:
728- EXPLAIN output: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
729- Optimization: https://dev.mysql.com/doc/refman/8.0/en/optimization.html
730 
731**Tools**:
732- pgAdmin (Postgres GUI)
733- MySQL Workbench
734- DBeaver (multi-database)
735- explain.depesz.com (Postgres EXPLAIN visualizer)
736