1# sql-optimization-patterns — detailed patterns and worked examples
2 
3## Optimization Patterns
4 
5### Pattern 1: Eliminate N+1 Queries
6 
7**Problem: N+1 Query Anti-Pattern**
8 
9```python
10# Bad: Executes N+1 queries
11users = db.query("SELECT * FROM users LIMIT 10")
12for user in users:
13    orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)
14    # Process orders
15```
16 
17**Solution: Use JOINs or Batch Loading**
18 
19```sql
20-- Solution 1: JOIN
21SELECT
22    u.id, u.name,
23    o.id as order_id, o.total
24FROM users u
25LEFT JOIN orders o ON u.id = o.user_id
26WHERE u.id IN (1, 2, 3, 4, 5);
27 
28-- Solution 2: Batch query
29SELECT * FROM orders
30WHERE user_id IN (1, 2, 3, 4, 5);
31```
32 
33```python
34# Good: Single query with JOIN or batch load
35# Using JOIN
36results = db.query("""
37    SELECT u.id, u.name, o.id as order_id, o.total
38    FROM users u
39    LEFT JOIN orders o ON u.id = o.user_id
40    WHERE u.id IN (1, 2, 3, 4, 5)
41""")
42 
43# Or batch load
44users = db.query("SELECT * FROM users LIMIT 10")
45user_ids = [u.id for u in users]
46orders = db.query(
47    "SELECT * FROM orders WHERE user_id IN (?)",
48    user_ids
49)
50# Group orders by user_id
51orders_by_user = {}
52for order in orders:
53    orders_by_user.setdefault(order.user_id, []).append(order)
54```
55 
56### Pattern 2: Optimize Pagination
57 
58**Bad: OFFSET on Large Tables**
59 
60```sql
61-- Slow for large offsets
62SELECT * FROM users
63ORDER BY created_at DESC
64LIMIT 20 OFFSET 100000;  -- Very slow!
65```
66 
67**Good: Cursor-Based Pagination**
68 
69```sql
70-- Much faster: Use cursor (last seen ID)
71SELECT * FROM users
72WHERE created_at < '2024-01-15 10:30:00'  -- Last cursor
73ORDER BY created_at DESC
74LIMIT 20;
75 
76-- With composite sorting
77SELECT * FROM users
78WHERE (created_at, id) < ('2024-01-15 10:30:00', 12345)
79ORDER BY created_at DESC, id DESC
80LIMIT 20;
81 
82-- Requires index
83CREATE INDEX idx_users_cursor ON users(created_at DESC, id DESC);
84```
85 
86### Pattern 3: Aggregate Efficiently
87 
88**Optimize COUNT Queries:**
89 
90```sql
91-- Bad: Counts all rows
92SELECT COUNT(*) FROM orders;  -- Slow on large tables
93 
94-- Good: Use estimates for approximate counts
95SELECT reltuples::bigint AS estimate
96FROM pg_class
97WHERE relname = 'orders';
98 
99-- Good: Filter before counting
100SELECT COUNT(*) FROM orders
101WHERE created_at > NOW() - INTERVAL '7 days';
102 
103-- Better: Use index-only scan
104CREATE INDEX idx_orders_created ON orders(created_at);
105SELECT COUNT(*) FROM orders
106WHERE created_at > NOW() - INTERVAL '7 days';
107```
108 
109**Optimize GROUP BY:**
110 
111```sql
112-- Bad: Group by then filter
113SELECT user_id, COUNT(*) as order_count
114FROM orders
115GROUP BY user_id
116HAVING COUNT(*) > 10;
117 
118-- Better: Filter first, then group (if possible)
119SELECT user_id, COUNT(*) as order_count
120FROM orders
121WHERE status = 'completed'
122GROUP BY user_id
123HAVING COUNT(*) > 10;
124 
125-- Best: Use covering index
126CREATE INDEX idx_orders_user_status ON orders(user_id, status);
127```
128 
129### Pattern 4: Subquery Optimization
130 
131**Transform Correlated Subqueries:**
132 
133```sql
134-- Bad: Correlated subquery (runs for each row)
135SELECT u.name, u.email,
136    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
137FROM users u;
138 
139-- Good: JOIN with aggregation
140SELECT u.name, u.email, COUNT(o.id) as order_count
141FROM users u
142LEFT JOIN orders o ON o.user_id = u.id
143GROUP BY u.id, u.name, u.email;
144 
145-- Better: Use window functions
146SELECT DISTINCT ON (u.id)
147    u.name, u.email,
148    COUNT(o.id) OVER (PARTITION BY u.id) as order_count
149FROM users u
150LEFT JOIN orders o ON o.user_id = u.id;
151```
152 
153**Use CTEs for Clarity:**
154 
155```sql
156-- Using Common Table Expressions
157WITH recent_users AS (
158    SELECT id, name, email
159    FROM users
160    WHERE created_at > NOW() - INTERVAL '30 days'
161),
162user_order_counts AS (
163    SELECT user_id, COUNT(*) as order_count
164    FROM orders
165    WHERE created_at > NOW() - INTERVAL '30 days'
166    GROUP BY user_id
167)
168SELECT ru.name, ru.email, COALESCE(uoc.order_count, 0) as orders
169FROM recent_users ru
170LEFT JOIN user_order_counts uoc ON ru.id = uoc.user_id;
171```
172 
173### Pattern 5: Batch Operations
174 
175**Batch INSERT:**
176 
177```sql
178-- Bad: Multiple individual inserts
179INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');
180INSERT INTO users (name, email) VALUES ('Bob', '[email protected]');
181INSERT INTO users (name, email) VALUES ('Carol', '[email protected]');
182 
183-- Good: Batch insert
184INSERT INTO users (name, email) VALUES
185    ('Alice', '[email protected]'),
186    ('Bob', '[email protected]'),
187    ('Carol', '[email protected]');
188 
189-- Better: Use COPY for bulk inserts (PostgreSQL)
190COPY users (name, email) FROM '/tmp/users.csv' CSV HEADER;
191```
192 
193**Batch UPDATE:**
194 
195```sql
196-- Bad: Update in loop
197UPDATE users SET status = 'active' WHERE id = 1;
198UPDATE users SET status = 'active' WHERE id = 2;
199-- ... repeat for many IDs
200 
201-- Good: Single UPDATE with IN clause
202UPDATE users
203SET status = 'active'
204WHERE id IN (1, 2, 3, 4, 5, ...);
205 
206-- Better: Use temporary table for large batches
207CREATE TEMP TABLE temp_user_updates (id INT, new_status VARCHAR);
208INSERT INTO temp_user_updates VALUES (1, 'active'), (2, 'active'), ...;
209 
210UPDATE users u
211SET status = t.new_status
212FROM temp_user_updates t
213WHERE u.id = t.id;
214```
215 
216## Advanced Techniques
217 
218### Materialized Views
219 
220Pre-compute expensive queries.
221 
222```sql
223-- Create materialized view
224CREATE MATERIALIZED VIEW user_order_summary AS
225SELECT
226    u.id,
227    u.name,
228    COUNT(o.id) as total_orders,
229    SUM(o.total) as total_spent,
230    MAX(o.created_at) as last_order_date
231FROM users u
232LEFT JOIN orders o ON u.id = o.user_id
233GROUP BY u.id, u.name;
234 
235-- Add index to materialized view
236CREATE INDEX idx_user_summary_spent ON user_order_summary(total_spent DESC);
237 
238-- Refresh materialized view
239REFRESH MATERIALIZED VIEW user_order_summary;
240 
241-- Concurrent refresh (PostgreSQL)
242REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary;
243 
244-- Query materialized view (very fast)
245SELECT * FROM user_order_summary
246WHERE total_spent > 1000
247ORDER BY total_spent DESC;
248```
249 
250### Partitioning
251 
252Split large tables for better performance.
253 
254```sql
255-- Range partitioning by date (PostgreSQL)
256CREATE TABLE orders (
257    id SERIAL,
258    user_id INT,
259    total DECIMAL,
260    created_at TIMESTAMP
261) PARTITION BY RANGE (created_at);
262 
263-- Create partitions
264CREATE TABLE orders_2024_q1 PARTITION OF orders
265    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
266 
267CREATE TABLE orders_2024_q2 PARTITION OF orders
268    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
269 
270-- Queries automatically use appropriate partition
271SELECT * FROM orders
272WHERE created_at BETWEEN '2024-02-01' AND '2024-02-28';
273-- Only scans orders_2024_q1 partition
274```
275 
276### Query Hints and Optimization
277 
278```sql
279-- Force index usage (MySQL)
280SELECT * FROM users
281USE INDEX (idx_users_email)
282WHERE email = '[email protected]';
283 
284-- Parallel query (PostgreSQL)
285SET max_parallel_workers_per_gather = 4;
286SELECT * FROM large_table WHERE condition;
287 
288-- Join hints (PostgreSQL)
289SET enable_nestloop = OFF;  -- Force hash or merge join
290```
291