index-optimizer

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.