AgentAlley

Skills · Coding

Sql Optimization Patterns

Unverified●28/40

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.

Originally by wshobson · MIT

Claude Code◐PartialHas SKILL.md but declares no allowed-tools — Claude Code will ask for permission each time
Cursor◐PartialPlain prose you can paste in — but no Cursor rules file
Codex◐PartialPlain prose you can paste in — but no AGENTS.md
Gemini CLI◐PartialPlain prose you can paste in
Copilot◐PartialPlain prose you can paste in — but no Copilot instructions file
npx agentalley add sql-optimization-patterns

This command does not work yet — the CLI is still being built. Until then, use Raw in the reader below to take the file.

Who is stuck, and on what

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.

The whole source

No sign-in, no blur, nothing truncated
sql-optimization-patterns/SKILL.md215 lines5.8 KBRawView on GitHub
Frontmatter — 2 properties
namesql-optimization-patterns
descriptionMaster 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.
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---A5 — No allowed-tools declared — no way to tell what this skill may touch
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 needA4 — This skill pulls in web or user content but never says to treat that content as data. A signal, not proof.
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## Detailed patterns and worked examples
143 
144Detailed pattern documentation lives in `references/details.md`. Read that file when the navigation tier above is insufficient.
145 
146## Best Practices
147 
1481. **Index Selectively**: Too many indexes slow down writes
1492. **Monitor Query Performance**: Use slow query logs
1503. **Keep Statistics Updated**: Run ANALYZE regularly
1514. **Use Appropriate Data Types**: Smaller types = better performance
1525. **Normalize Thoughtfully**: Balance normalization vs performance
1536. **Cache Frequently Accessed Data**: Use application-level caching
1547. **Connection Pooling**: Reuse database connections
1558. **Regular Maintenance**: VACUUM, ANALYZE, rebuild indexes
156 
157```sql
158-- Update statistics
159ANALYZE users;
160ANALYZE VERBOSE orders;
161 
162-- Vacuum (PostgreSQL)
163VACUUM ANALYZE users;
164VACUUM FULL users; -- Reclaim space (locks table)
165 
166-- Reindex
167REINDEX INDEX idx_users_email;
168REINDEX TABLE users;
169```
170 
171## Common Pitfalls
172 
173- **Over-Indexing**: Each index slows down INSERT/UPDATE/DELETE
174- **Unused Indexes**: Waste space and slow writes
175- **Missing Indexes**: Slow queries, full table scans
176- **Implicit Type Conversion**: Prevents index usage
177- **OR Conditions**: Can't use indexes efficiently
178- **LIKE with Leading Wildcard**: `LIKE '%abc'` can't use index
179- **Function in WHERE**: Prevents index usage unless functional index exists
180 
181## Monitoring Queries
182 
183```sql
184-- Find slow queries (PostgreSQL)
185SELECT query, calls, total_time, mean_time
186FROM pg_stat_statements
187ORDER BY mean_time DESC
188LIMIT 10;
189 
190-- Find missing indexes (PostgreSQL)
191SELECT
192 schemaname,
193 tablename,
194 seq_scan,
195 seq_tup_read,
196 idx_scan,
197 seq_tup_read / seq_scan AS avg_seq_tup_read
198FROM pg_stat_user_tables
199WHERE seq_scan > 0
200ORDER BY seq_tup_read DESC
201LIMIT 10;
202 
203-- Find unused indexes (PostgreSQL)
204SELECT
205 schemaname,
206 tablename,
207 indexname,
208 idx_scan,
209 idx_tup_read,
210 idx_tup_fetch
211FROM pg_stat_user_indexes
212WHERE idx_scan = 0
213ORDER BY pg_relation_size(indexrelid) DESC;
214```
215 

Reviews

Installed this one?Write the first review and take the Trailblazer badge.

Reviews only open after a real install, so this is empty — and we leave it empty rather than invent one.

Alternatives

Also in Coding
Subagent Driven DevelopmentUse when executing implementation plans with independent tasks in the current sessionCoding · MIT◐◐◐◐◐●36/40Python Code Style & DocumentationPython code style, linting, formatting, naming conventions, and documentation standards. Use when writing new code, reviewing style, configuring linters, writing docstrings, or establishing project standards.Coding · MIT◐····●35/40Competitor Price Analysis 💲Competitor pricing strategy analysis and market positioning. Price mapping, pricing gaps identification, elasticity signals evaluation, and strategic pricing optimization. Use when the user asks about competitor pricing, price analysis, pricing strategy, or coCoding · MIT◐····●34/40Competitor Price Tracker 📊Set up competitor price tracking and monitoring workflows. Track price changes, detect promotions, analyze pricing patterns, and get alerts for competitive price movements.Coding · MIT◐····●34/40