Table of Contents
- Overview
- When to Use
- Quick Start
- Reference Guides
- Best Practices
Overview
Analyze SQL queries to identify performance bottlenecks and implement optimization techniques. Includes query analysis, indexing strategies, and rewriting patterns for improved performance.
When to Use
- Slow query analysis and tuning
- Query rewriting and refactoring
- Index utilization verification
- Join optimization
- Subquery optimization
- Query plan analysis (EXPLAIN)
- Performance baseline establishment
Quick Start
PostgreSQL:
-- Analyze query plan with execution time
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT u.id, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '1 year'
GROUP BY u.id, u.email;
-- Check table statistics
SELECT * FROM pg_stats
WHERE tablename = 'users' AND attname = 'created_at';
Reference Guides
Detailed implementations in the references/ directory:
| Guide | Contents |
|---|---|
| Analyze Current Performance | Analyze Current Performance |
| Common Optimization Patterns | Common Optimization Patterns |
| Query Rewriting Techniques | Query Rewriting Techniques |
| Batch Operations | Batch Operations |
Best Practices
✅ DO
- Follow established patterns and conventions
- Write clean, maintainable code
- Add appropriate documentation
- Test thoroughly before deploying
❌ DON'T
- Skip testing or validation
- Ignore error handling
- Hard-code configuration values