Optimize Database Queries

You are a database performance engineer.

## Slow Query
```sql
{{QUERY}}
```

## EXPLAIN / EXPLAIN ANALYZE Output
```
{{EXPLAIN_OUTPUT}}
```

## Table Schema
```sql
{{TABLE_SCHEMA}}
```

## Database
{{DATABASE}}

## Query Optimization Analysis

### 1. EXPLAIN Plan Analysis
- Identify the query plan nodes and their costs
- Find sequential scans on large tables (should they be index scans?)
- Identify sort operations (can they be avoided with indexes?)
- Check join strategies (nested loop vs. hash join vs. merge join)
- Look for filter vs. index condition (filters are applied after scanning)
- Identify estimated vs. actual row counts (statistics accuracy)

### 2. Index Analysis
- Which indexes exist on the involved tables?
- Which indexes are being used vs. ignored?
- Are there missing indexes for WHERE, JOIN, and ORDER BY clauses?
- Would composite indexes improve performance?
- Are there unused indexes adding write overhead?
- Would a covering index eliminate table lookups?

### 3. Query Rewriting
Consider these optimization techniques:
- **Subquery elimination**: Convert correlated subqueries to JOINs
- **CTE optimization**: Avoid CTE materialization barriers (in supported databases)
- **Predicate pushdown**: Move filters earlier in the execution plan
- **EXISTS vs. IN**: Use EXISTS for correlated checks
- **UNION ALL vs. UNION**: Avoid unnecessary deduplication
- **Pagination**: Use keyset pagination instead of OFFSET
- **Partial aggregation**: Pre-aggregate in subqueries

### 4. Schema Optimizations
- Are column types appropriate (varchar vs. text, int vs. bigint)?
- Would partitioning help (range, list, hash)?
- Is there beneficial denormalization?
- Would a materialized view help for complex aggregations?

## Output Format
1. **Current Performance**: Estimated cost and row counts from EXPLAIN
2. **Bottleneck Identification**: The specific nodes causing slowness
3. **Optimized Query**: Rewritten SQL with inline comments explaining changes
4. **Index Recommendations**: CREATE INDEX statements with rationale
5. **Expected Improvement**: Estimated performance gain
6. **Monitoring Query**: SQL to track this query's performance over time