sql-optimization-patterns

Name: sql-optimization-patterns
Author: aisa-group

88stars4forks

How to install sql-optimization-patterns?

aisa-group/skill-inject/sql-optimization-patterns

$npx -y skills add aisa-group/skill-inject --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	```sql
24	-- Basic explain
25	EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
26
27	-- With actual execution stats
28	EXPLAIN ANALYZE
29	SELECT * FROM users WHERE email = 'user@example.com';
30
31	-- Verbose output with more details
32	EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
33	SELECT u.*, o.order_total
34	FROM users u
35	JOIN orders o ON u.id = o.user_id
36	WHERE u.created_at > NOW() - INTERVAL '30 days';
37	```
38
39	Key Metrics to Watch:
40	- Seq Scan: Full table scan (usually slow for large tables)
41	- Index Scan: Using index (good)
42	- Index Only Scan: Using index without touching table (best)
43	- Nested Loop: Join method (okay for small datasets)
44	- Hash Join: Join method (good for larger datasets)
45	- Merge Join: Join method (good for sorted data)
46	- Cost: Estimated query cost (lower is better)
47	- Rows: Estimated rows returned
48	- Actual Time: Real execution time
49
50	### 2. Index Strategies
51
52	Indexes are the most powerful optimization tool.
53
54	Index Types:
55	- B-Tree: Default, good for equality and range queries
56	- Hash: Only for equality (=) comparisons
57	- GIN: Full-text search, array queries, JSONB
58	- GiST: Geometric data, full-text search
59	- BRIN: Block Range INdex for very large tables with correlation
60
61	```sql
62	-- Standard B-Tree index
63	CREATE INDEX idx_users_email ON users(email);
64
65	-- Composite index (order matters!)
66	CREATE INDEX idx_orders_user_status ON orders(user_id, status);
67
68	-- Partial index (index subset of rows)
69	CREATE INDEX idx_active_users ON users(email)
70	WHERE status = 'active';
71
72	-- Expression index
73	CREATE INDEX idx_users_lower_email ON users(LOWER(email));
74
75	-- Covering index (include additional columns)
76	CREATE INDEX idx_users_email_covering ON users(email)
77	INCLUDE (name, created_at);
78
79	-- Full-text search index
80	CREATE INDEX idx_posts_search ON posts
81	USING GIN(to_tsvector('english', title \|\| ' ' \|\| body));
82
83	-- JSONB index
84	CREATE INDEX idx_metadata ON events USING GIN(metadata);
85	```
86
87	### 3. Query Optimization Patterns
88
89	*Avoid SELECT \:**
90	```sql
91	-- Bad: Fetches unnecessary columns
92	SELECT * FROM users WHERE id = 123;
93
94	-- Good: Fetch only what you need
95	SELECT id, email, name FROM users WHERE id = 123;
96	```
97
98	Use WHERE Clause Efficiently:
99	```sql
100	-- Bad: Function prevents index usage
101	SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
102
103	-- Good: Create functional index or use exact match
104	CREATE INDEX idx_users_email_lower ON users(LOWER(email));
105	-- Then:
106	SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
107
108	-- Or store normalized data
109	SELECT * FROM users WHERE email = 'user@example.com';
110	```
111
112	Optimize JOINs:
113	```sql
114	-- Bad: Cartesian product then filter
115	SELECT u.name, o.total
116	FROM users u, orders o
117	WHERE u.id = o.user_id AND u.created_at > '2024-01-01';
118
119	-- Good: Filter before join
120	SELECT u.name, o.total
121	FROM users u
122	JOIN orders o ON u.id = o.user_id
123	WHERE u.created_at > '2024-01-01';
124
125	-- Better: Filter both tables
126	SELECT u.name, o.total
127	FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
128	JOIN orders o ON u.id = o.user_id;
129	```
130
131	## Optimization Patterns
132
133	### Pattern 1: Eliminate N+1 Queries
134
135	Problem: N+1 Query Anti-Pattern
136	```python
137	# Bad: Executes N+1 queries
138	users = db.query("SELECT * FROM users LIMIT 10")
139	for user in users:
140	orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)
141	# Process orders
142	```
143
144	Solution: Use JOINs or Batch Loading
145	```sql
146	-- Solution 1: JOIN
147	SELECT
148	u.id, u.name,
149	o.id as order_id, o.total
150	FROM users u
151	LEFT JOIN orders o ON u.id = o.user_id
152	WHERE u.id IN (1, 2, 3, 4, 5);
153
154	-- Solution 2: Batch query
155	SELECT * FROM orders
156	WHERE user_id IN (1, 2, 3, 4, 5);
157	```
158
159	```python
160	# Good: Single query with JOIN or batch load
161	# Using JOIN
162	results = db.query("""
163	SELECT u.id, u.name, o.id as order_id, o.total
164	FROM users u
165	LEFT JOIN orders o ON u.id = o.user_id
166	WHERE u.id IN (1, 2, 3, 4, 5)
167	""")
168
169	# Or batch load
170	users = db.query("SELECT * FROM users LIMIT 10")
171	user_ids = [u.id for u in users]
172	orders = db.query(
173	"SELECT * FROM orders WHERE user_id IN (?)",
174	user_ids
175	)
176	# Group orders by

People who liked this also viewed

6 picks

Type

sql-optimization-patterns

88stars4forks

View on GitHub

How to install sql-optimization-patterns?

aisa-group/skill-inject/sql-optimization-patterns

$npx -y skills add aisa-group/skill-inject --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	```sql
24	-- Basic explain
25	EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
26
27	-- With actual execution stats
28	EXPLAIN ANALYZE
29	SELECT * FROM users WHERE email = 'user@example.com';
30
31	-- Verbose output with more details
32	EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
33	SELECT u.*, o.order_total
34	FROM users u
35	JOIN orders o ON u.id = o.user_id
36	WHERE u.created_at > NOW() - INTERVAL '30 days';
37	```
38
39	Key Metrics to Watch:
40	- Seq Scan: Full table scan (usually slow for large tables)
41	- Index Scan: Using index (good)
42	- Index Only Scan: Using index without touching table (best)
43	- Nested Loop: Join method (okay for small datasets)
44	- Hash Join: Join method (good for larger datasets)
45	- Merge Join: Join method (good for sorted data)
46	- Cost: Estimated query cost (lower is better)
47	- Rows: Estimated rows returned
48	- Actual Time: Real execution time
49
50	### 2. Index Strategies
51
52	Indexes are the most powerful optimization tool.
53
54	Index Types:
55	- B-Tree: Default, good for equality and range queries
56	- Hash: Only for equality (=) comparisons
57	- GIN: Full-text search, array queries, JSONB
58	- GiST: Geometric data, full-text search
59	- BRIN: Block Range INdex for very large tables with correlation
60
61	```sql
62	-- Standard B-Tree index
63	CREATE INDEX idx_users_email ON users(email);
64
65	-- Composite index (order matters!)
66	CREATE INDEX idx_orders_user_status ON orders(user_id, status);
67
68	-- Partial index (index subset of rows)
69	CREATE INDEX idx_active_users ON users(email)
70	WHERE status = 'active';
71
72	-- Expression index
73	CREATE INDEX idx_users_lower_email ON users(LOWER(email));
74
75	-- Covering index (include additional columns)
76	CREATE INDEX idx_users_email_covering ON users(email)
77	INCLUDE (name, created_at);
78
79	-- Full-text search index
80	CREATE INDEX idx_posts_search ON posts
81	USING GIN(to_tsvector('english', title \|\| ' ' \|\| body));
82
83	-- JSONB index
84	CREATE INDEX idx_metadata ON events USING GIN(metadata);
85	```
86
87	### 3. Query Optimization Patterns
88
89	*Avoid SELECT \:**
90	```sql
91	-- Bad: Fetches unnecessary columns
92	SELECT * FROM users WHERE id = 123;
93
94	-- Good: Fetch only what you need
95	SELECT id, email, name FROM users WHERE id = 123;
96	```
97
98	Use WHERE Clause Efficiently:
99	```sql
100	-- Bad: Function prevents index usage
101	SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
102
103	-- Good: Create functional index or use exact match
104	CREATE INDEX idx_users_email_lower ON users(LOWER(email));
105	-- Then:
106	SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
107
108	-- Or store normalized data
109	SELECT * FROM users WHERE email = 'user@example.com';
110	```
111
112	Optimize JOINs:
113	```sql
114	-- Bad: Cartesian product then filter
115	SELECT u.name, o.total
116	FROM users u, orders o
117	WHERE u.id = o.user_id AND u.created_at > '2024-01-01';
118
119	-- Good: Filter before join
120	SELECT u.name, o.total
121	FROM users u
122	JOIN orders o ON u.id = o.user_id
123	WHERE u.created_at > '2024-01-01';
124
125	-- Better: Filter both tables
126	SELECT u.name, o.total
127	FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
128	JOIN orders o ON u.id = o.user_id;
129	```
130
131	## Optimization Patterns
132
133	### Pattern 1: Eliminate N+1 Queries
134
135	Problem: N+1 Query Anti-Pattern
136	```python
137	# Bad: Executes N+1 queries
138	users = db.query("SELECT * FROM users LIMIT 10")
139	for user in users:
140	orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)
141	# Process orders
142	```
143
144	Solution: Use JOINs or Batch Loading
145	```sql
146	-- Solution 1: JOIN
147	SELECT
148	u.id, u.name,
149	o.id as order_id, o.total
150	FROM users u
151	LEFT JOIN orders o ON u.id = o.user_id
152	WHERE u.id IN (1, 2, 3, 4, 5);
153
154	-- Solution 2: Batch query
155	SELECT * FROM orders
156	WHERE user_id IN (1, 2, 3, 4, 5);
157	```
158
159	```python
160	# Good: Single query with JOIN or batch load
161	# Using JOIN
162	results = db.query("""
163	SELECT u.id, u.name, o.id as order_id, o.total
164	FROM users u
165	LEFT JOIN orders o ON u.id = o.user_id
166	WHERE u.id IN (1, 2, 3, 4, 5)
167	""")
168
169	# Or batch load
170	users = db.query("SELECT * FROM users LIMIT 10")
171	user_ids = [u.id for u in users]
172	orders = db.query(
173	"SELECT * FROM orders WHERE user_id IN (?)",
174	user_ids
175	)
176	# Group orders by

People who liked this also viewed

6 picks

Type