sql-optimization-patterns

Name: sql-optimization-patterns
Author: wshobson

16kinstalls4.1kforks

How to install sql-optimization-patterns?

wshobson/agents/sql-optimization-patterns

$npx -y skills add wshobson/agents --skill sql-optimization-patterns

Installs into the current project.

Summary

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.

Files · 1

View on GitHub

SKILL.md

1	# SQL Optimization Patterns
2
3	Transform slow database queries into lightning-fast operations through systematic optimization, proper indexing, and query plan analysis.
4
5	## When to Use This Skill
6
7	- Debugging slow-running queries
8	- Designing performant database schemas
9	- Optimizing application response times
10	- Reducing database load and costs
11	- Improving scalability for growing datasets
12	- Analyzing EXPLAIN query plans
13	- Implementing efficient indexes
14	- Resolving N+1 query problems
15
16	## Core Concepts
17
18	### 1. Query Execution Plans (EXPLAIN)
19
20	Understanding EXPLAIN output is fundamental to optimization.
21
22	PostgreSQL EXPLAIN:
23
24	```sql
25	-- Basic explain
26	EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
27
28	-- With actual execution stats
29	EXPLAIN ANALYZE
30	SELECT * FROM users WHERE email = 'user@example.com';
31
32	-- Verbose output with more details
33	EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
34	SELECT u.*, o.order_total
35	FROM users u
36	JOIN orders o ON u.id = o.user_id
37	WHERE u.created_at > NOW() - INTERVAL '30 days';
38	```
39
40	Key Metrics to Watch:
41
42	- Seq Scan: Full table scan (usually slow for large tables)
43	- Index Scan: Using index (good)
44	- Index Only Scan: Using index without touching table (best)
45	- Nested Loop: Join method (okay for small datasets)
46	- Hash Join: Join method (good for larger datasets)
47	- Merge Join: Join method (good for sorted data)
48	- Cost: Estimated query cost (lower is better)
49	- Rows: Estimated rows returned
50	- Actual Time: Real execution time
51
52	### 2. Index Strategies
53
54	Indexes are the most powerful optimization tool.
55
56	Index Types:
57
58	- B-Tree: Default, good for equality and range queries
59	- Hash: Only for equality (=) comparisons
60	- GIN: Full-text search, array queries, JSONB
61	- GiST: Geometric data, full-text search
62	- BRIN: Block Range INdex for very large tables with correlation
63
64	```sql
65	-- Standard B-Tree index
66	CREATE INDEX idx_users_email ON users(email);
67
68	-- Composite index (order matters!)
69	CREATE INDEX idx_orders_user_status ON orders(user_id, status);
70
71	-- Partial index (index subset of rows)
72	CREATE INDEX idx_active_users ON users(email)
73	WHERE status = 'active';
74
75	-- Expression index
76	CREATE INDEX idx_users_lower_email ON users(LOWER(email));
77
78	-- Covering index (include additional columns)
79	CREATE INDEX idx_users_email_covering ON users(email)
80	INCLUDE (name, created_at);
81
82	-- Full-text search index
83	CREATE INDEX idx_posts_search ON posts
84	USING GIN(to_tsvector('english', title \|\| ' ' \|\| body));
85
86	-- JSONB index
87	CREATE INDEX idx_metadata ON events USING GIN(metadata);
88	```
89
90	### 3. Query Optimization Patterns
91
92	*Avoid SELECT \:**
93
94	```sql
95	-- Bad: Fetches unnecessary columns
96	SELECT * FROM users WHERE id = 123;
97
98	-- Good: Fetch only what you need
99	SELECT id, email, name FROM users WHERE id = 123;
100	```
101
102	Use WHERE Clause Efficiently:
103
104	```sql
105	-- Bad: Function prevents index usage
106	SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
107
108	-- Good: Create functional index or use exact match
109	CREATE INDEX idx_users_email_lower ON users(LOWER(email));
110	-- Then:
111	SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
112
113	-- Or store normalized data
114	SELECT * FROM users WHERE email = 'user@example.com';
115	```
116
117	Optimize JOINs:
118
119	```sql
120	-- Bad: Cartesian product then filter
121	SELECT u.name, o.total
122	FROM users u, orders o
123	WHERE u.id = o.user_id AND u.created_at > '2024-01-01';
124
125	-- Good: Filter before join
126	SELECT u.name, o.total
127	FROM users u
128	JOIN orders o ON u.id = o.user_id
129	WHERE u.created_at > '2024-01-01';
130
131	-- Better: Filter both tables
132	SELECT u.name, o.total
133	FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
134	JOIN orders o ON u.id = o.user_id;
135	```
136
137	## Detailed patterns and worked examples
138
139	Detailed pattern documentation lives in `references/details.md`. Read that file when the navigation tier above is insufficient.
140
141	## Best Practices
142
143	1. Index Selectively: Too many indexes slow down writes
144	2. Monitor Query Performance: Use slow query logs
145	3. Keep Statistics Updated: Run ANALYZE regularly
146	4. Use Appropriate Data Types: Smaller types = better performance
147	5. Normalize Thoughtfully: Balance normalization vs performance
148	6. Cache Frequently Accessed Data: Use application-level caching
149	7. Connection Pooling: Reuse database connections
150	8. Regular Maintenance: VACUUM, ANALYZE, rebuild indexes
151
152	```sql
153	-- Update statistics
154	ANALYZE users;
155	ANALYZE VERBOSE orders;
156
157	-- Vacuum (PostgreSQL)
158	VACUUM ANALYZE users;
159	VACUUM FULL users; -- Reclaim space (locks table)
160
161	-- Reindex
162	REINDEX INDEX idx_users_email;
163	REINDEX TABLE users;
164	```
165
166	## Common Pitfalls
167
168	- Over-Indexing: Each index slows down INSERT/UPDATE/DELETE
169	- Unused Indexes: Waste space and

People who liked this also viewed

6 picks

Type

sql-optimization-patterns

16kinstalls4.1kforks

View on GitHub

How to install sql-optimization-patterns?

wshobson/agents/sql-optimization-patterns

$npx -y skills add wshobson/agents --skill sql-optimization-patterns

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# SQL Optimization Patterns
2
3	Transform slow database queries into lightning-fast operations through systematic optimization, proper indexing, and query plan analysis.
4
5	## When to Use This Skill
6
7	- Debugging slow-running queries
8	- Designing performant database schemas
9	- Optimizing application response times
10	- Reducing database load and costs
11	- Improving scalability for growing datasets
12	- Analyzing EXPLAIN query plans
13	- Implementing efficient indexes
14	- Resolving N+1 query problems
15
16	## Core Concepts
17
18	### 1. Query Execution Plans (EXPLAIN)
19
20	Understanding EXPLAIN output is fundamental to optimization.
21
22	PostgreSQL EXPLAIN:
23
24	```sql
25	-- Basic explain
26	EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
27
28	-- With actual execution stats
29	EXPLAIN ANALYZE
30	SELECT * FROM users WHERE email = 'user@example.com';
31
32	-- Verbose output with more details
33	EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
34	SELECT u.*, o.order_total
35	FROM users u
36	JOIN orders o ON u.id = o.user_id
37	WHERE u.created_at > NOW() - INTERVAL '30 days';
38	```
39
40	Key Metrics to Watch:
41
42	- Seq Scan: Full table scan (usually slow for large tables)
43	- Index Scan: Using index (good)
44	- Index Only Scan: Using index without touching table (best)
45	- Nested Loop: Join method (okay for small datasets)
46	- Hash Join: Join method (good for larger datasets)
47	- Merge Join: Join method (good for sorted data)
48	- Cost: Estimated query cost (lower is better)
49	- Rows: Estimated rows returned
50	- Actual Time: Real execution time
51
52	### 2. Index Strategies
53
54	Indexes are the most powerful optimization tool.
55
56	Index Types:
57
58	- B-Tree: Default, good for equality and range queries
59	- Hash: Only for equality (=) comparisons
60	- GIN: Full-text search, array queries, JSONB
61	- GiST: Geometric data, full-text search
62	- BRIN: Block Range INdex for very large tables with correlation
63
64	```sql
65	-- Standard B-Tree index
66	CREATE INDEX idx_users_email ON users(email);
67
68	-- Composite index (order matters!)
69	CREATE INDEX idx_orders_user_status ON orders(user_id, status);
70
71	-- Partial index (index subset of rows)
72	CREATE INDEX idx_active_users ON users(email)
73	WHERE status = 'active';
74
75	-- Expression index
76	CREATE INDEX idx_users_lower_email ON users(LOWER(email));
77
78	-- Covering index (include additional columns)
79	CREATE INDEX idx_users_email_covering ON users(email)
80	INCLUDE (name, created_at);
81
82	-- Full-text search index
83	CREATE INDEX idx_posts_search ON posts
84	USING GIN(to_tsvector('english', title \|\| ' ' \|\| body));
85
86	-- JSONB index
87	CREATE INDEX idx_metadata ON events USING GIN(metadata);
88	```
89
90	### 3. Query Optimization Patterns
91
92	*Avoid SELECT \:**
93
94	```sql
95	-- Bad: Fetches unnecessary columns
96	SELECT * FROM users WHERE id = 123;
97
98	-- Good: Fetch only what you need
99	SELECT id, email, name FROM users WHERE id = 123;
100	```
101
102	Use WHERE Clause Efficiently:
103
104	```sql
105	-- Bad: Function prevents index usage
106	SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
107
108	-- Good: Create functional index or use exact match
109	CREATE INDEX idx_users_email_lower ON users(LOWER(email));
110	-- Then:
111	SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
112
113	-- Or store normalized data
114	SELECT * FROM users WHERE email = 'user@example.com';
115	```
116
117	Optimize JOINs:
118
119	```sql
120	-- Bad: Cartesian product then filter
121	SELECT u.name, o.total
122	FROM users u, orders o
123	WHERE u.id = o.user_id AND u.created_at > '2024-01-01';
124
125	-- Good: Filter before join
126	SELECT u.name, o.total
127	FROM users u
128	JOIN orders o ON u.id = o.user_id
129	WHERE u.created_at > '2024-01-01';
130
131	-- Better: Filter both tables
132	SELECT u.name, o.total
133	FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
134	JOIN orders o ON u.id = o.user_id;
135	```
136
137	## Detailed patterns and worked examples
138
139	Detailed pattern documentation lives in `references/details.md`. Read that file when the navigation tier above is insufficient.
140
141	## Best Practices
142
143	1. Index Selectively: Too many indexes slow down writes
144	2. Monitor Query Performance: Use slow query logs
145	3. Keep Statistics Updated: Run ANALYZE regularly
146	4. Use Appropriate Data Types: Smaller types = better performance
147	5. Normalize Thoughtfully: Balance normalization vs performance
148	6. Cache Frequently Accessed Data: Use application-level caching
149	7. Connection Pooling: Reuse database connections
150	8. Regular Maintenance: VACUUM, ANALYZE, rebuild indexes
151
152	```sql
153	-- Update statistics
154	ANALYZE users;
155	ANALYZE VERBOSE orders;
156
157	-- Vacuum (PostgreSQL)
158	VACUUM ANALYZE users;
159	VACUUM FULL users; -- Reclaim space (locks table)
160
161	-- Reindex
162	REINDEX INDEX idx_users_email;
163	REINDEX TABLE users;
164	```
165
166	## Common Pitfalls
167
168	- Over-Indexing: Each index slows down INSERT/UPDATE/DELETE
169	- Unused Indexes: Waste space and

People who liked this also viewed

6 picks

Type