| name | index-optimizer |
| description | Database indexing optimization expert. Identifies missing, redundant, and unused indexes, and provides optimization recommendations. Trigger when user mentions index optimization, query performance, or database tuning. |
| when_to_use | - User asks about index strategy
- User needs to optimize query performance
- User encounters slow queries
- User wants to reduce database costs
- User needs to audit index usage
|
| allowed-tools | Bash(psql *, wrangler *) |
Database Index Optimization Expert
Index Types
| Type | Use Case | Trade-off |
|---|
| Single Column | Simple equality/range | Low overhead |
| Composite | Multi-column queries | Column order matters |
| Partial | Subset of rows | Smaller, faster |
| Covering | Index-only scans | Larger, eliminates lookups |
Common Anti-Patterns
| Anti-Pattern | Problem | Reference |
|---|
| Missing composite index | Multi-column queries slow | references/composite-index.md |
| Missing covering index | Table lookups required | references/covering-index.md |
| Missing partial index | Indexing all rows | references/partial-index.md |
| Unused indexes | Write overhead | references/unused-index.md |
| Redundant indexes | Duplicate coverage | references/redundant-index.md |
| Index skip scan | Wrong column order | references/index-skip-scan.md |
Audit Workflow
Step 1: Identify Missing Indexes
Check sequential scans and slow queries.
See references/composite-index.md for multi-column patterns.
Step 2: Check Redundant Indexes
Find indexes that are subsets of other indexes.
See references/redundant-index.md for detection.
Step 3: Detect Unused Indexes
Monitor index usage statistics.
See references/unused-index.md for detection and removal.
Step 4: Optimize Index Design
Apply appropriate index type based on query patterns:
- Multi-column WHERE →
references/composite-index.md
- Subset queries →
references/partial-index.md
- SELECT-heavy →
references/covering-index.md
Benchmark Reference
| Optimization | Before | After | Improvement |
|---|
| Composite index | 500ms | 5ms | 99% |
| Partial index | 50MB | 5MB | 90% size reduction |
| Covering index | 50ms | 10ms | 80% faster |
| Remove unused | 100ms write | 70ms write | 30% faster |
| Remove redundant | 200MB | 100MB | 50% reduction |
Output Format
## Index Optimization Report
### 1. Current State
- Total indexes: X
- Missing indexes: X
- Redundant indexes: X
- Unused indexes: X
### 2. Anti-Patterns Detected
| Table | Anti-Pattern | Reference | Fix |
|-------|--------------|-----------|-----|
| ... | ... | ... | ... |
### 3. Recommendations (by priority)
1. [P0] Fix: <anti-pattern> → Expected improvement: XX%
Reference: references/<file>.md
### 4. Implementation
Apply fixes from referenced files.
