Install the skill above, then run this in your AI tool:
/sql-optimization-patterns <your description>
About
Transform slow database queries into lightning-fast operations through systematic optimization, proper indexing, and query plan analysis.
Source
What gets added to your project
Preview every file this package writes on install before you run the command above.
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 perf...
Skill instructions
SQL Optimization Patterns
Transform slow database queries into lightning-fast operations through systematic optimization, proper indexing, and query plan analysis.
When to Use This Skill
Debugging slow-running queries
Designing performant database schemas
Optimizing application response times
Reducing database load and costs
Improving scalability for growing datasets
Analyzing EXPLAIN query plans
Implementing efficient indexes
Resolving N+1 query problems
Core Concepts
1. Query Execution Plans (EXPLAIN)
Understanding EXPLAIN output is fundamental to optimization.
PostgreSQL EXPLAIN:
sql
-- Basic explain
EXPLAIN SELECT*FROM users WHERE email ='user@example.com';
-- With actual execution stats
EXPLAIN ANALYZE
SELECT*FROM users WHERE email ='user@example.com';
-- Verbose output with more details
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.order_total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() -INTERVAL'30 days';
Key Metrics to Watch:
Seq Scan: Full table scan (usually slow for large tables)
Index Scan: Using index (good)
Index Only Scan: Using index without touching table (best)
Nested Loop: Join method (okay for small datasets)
Hash Join: Join method (good for larger datasets)
Merge Join: Join method (good for sorted data)
Cost: Estimated query cost (lower is better)
Rows: Estimated rows returned
Actual Time: Real execution time
2. Index Strategies
Indexes are the most powerful optimization tool.
Index Types:
B-Tree: Default, good for equality and range queries
Hash: Only for equality (=) comparisons
GIN: Full-text search, array queries, JSONB
GiST: Geometric data, full-text search
BRIN: Block Range INdex for very large tables with correlation
sql
-- Standard B-Tree indexCREATE INDEX idx_users_email ON users(email);
-- Composite index (order matters!)CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Partial index (index subset of rows)CREATE INDEX idx_active_users ON users(email)
WHERE status ='active';
-- Expression indexCREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- Covering index (include additional columns)CREATE INDEX idx_users_email_covering ON users(email)
INCLUDE (name, created_at);
-- Full-text search indexCREATE INDEX idx_posts_search ON posts
USING GIN(to_tsvector('english', title ||' '|| body));
-- JSONB indexCREATE INDEX idx_metadata ON events USING GIN(metadata);
3. Query Optimization Patterns
Avoid SELECT *:
sql
-- Bad: Fetches unnecessary columnsSELECT*FROM users WHERE id =123;
-- Good: Fetch only what you needSELECT id, email, name FROM users WHERE id =123;
Use WHERE Clause Efficiently:
sql
-- Bad: Function prevents index usageSELECT*FROM users WHERELOWER(email) ='user@example.com';
-- Good: Create functional index or use exact matchCREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Then:SELECT*FROM users WHERELOWER(email) ='user@example.com';
-- Or store normalized dataSELECT*FROM users WHERE email ='user@example.com';
Optimize JOINs:
sql
-- Bad: Cartesian product then filterSELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id AND u.created_at >'2024-01-01';
-- Good: Filter before joinSELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at >'2024-01-01';
-- Better: Filter both tablesSELECT u.name, o.total
FROM (SELECT*FROM users WHERE created_at >'2024-01-01') u
JOIN orders o ON u.id = o.user_id;
Detailed patterns and worked examples
Detailed pattern documentation lives in references/details.md. Read that file when the navigation tier above is insufficient.
Best Practices
Index Selectively: Too many indexes slow down writes
Monitor Query Performance: Use slow query logs
Keep Statistics Updated: Run ANALYZE regularly
Use Appropriate Data Types: Smaller types = better performance
Normalize Thoughtfully: Balance normalization vs performance
Cache Frequently Accessed Data: Use application-level caching
Add this badge to a GitHub README so readers can open the package page and install the same version.
[](https://www.aipm-registry.com/skills/wshobson/sql-optimization-patterns/1.0.0?utm_source=github&utm_medium=readme&utm_campaign=package_badge)
This imported publisher has not claimed its AIPM account. A connected account confirms account control, but it does not guarantee that a package is safe.
AI assistant context
What this skill is for
@wshobson/sql-optimization-patterns@1.0.0 is an AIPM skill package for All tools. Use it when a project needs the reusable AI behavior described as: 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 perf...
Run aipm add @wshobson/sql-optimization-patterns@1.0.0 in a project that has AIPM initialized.
Supported tools
@wshobson/sql-optimization-patterns@1.0.0 supports All tools.
Before installing
AIPM skills can add files to your project. Review the package name, target, description, license, and publisher before installing. Use a clean branch when trying a new skill.