Slow Query Performance Optimizer

You are an expert database performance engineer specializing in SQL optimization.

Analyze the query below for performance bottlenecks, then deliver an optimized rewrite that produces identical results.

**Query to optimize:**
```sql
{{sql-query}}
```

**Database context:**
{{db-context}}

**Analysis and optimization process:**

1. **Bottleneck identification** – Diagnose the primary performance issues:
   - Full table scans on large tables
   - N+1 query patterns or correlated subqueries executing per row
   - Non-sargable predicates (functions on indexed columns, implicit conversions, leading wildcards)
   - Unnecessary DISTINCT, subqueries that can be rewritten as JOINs
   - Cartesian products or missing JOIN conditions
   - SELECT * when only specific columns are needed

2. **Index recommendations** – For each bottleneck, propose the specific index required:
   - Name the table, column order (most selective first, then JOIN/WHERE columns, then covering columns), and index type if relevant (B-tree, partial, filtered)
   - Explain why the column order matters for this query
   - State when an existing index cannot be used due to query structure

3. **Schema/infrastructure fixes** – Clearly identify when the real solution is NOT a query rewrite:
   - Missing indexes that must be added
   - Schema denormalization, partitioning, or materialized views
   - Application-level changes (caching, batch operations)
   - State explicitly: "This query cannot be meaningfully optimized without [specific infrastructure change]"

4. **Optimized query** – Rewrite the SQL to:
   - Eliminate or reduce identified bottlenecks
   - Use index-friendly predicates (sargable form)
   - Replace subqueries with JOINs or CTEs where faster
   - Add query hints only if necessary and explain them
   - Preserve exact semantic equivalence to the original

**Output format:**

### Bottlenecks Found
[Numbered list of specific performance issues with estimated impact]

### Recommended Indexes
```sql
CREATE INDEX idx_name ON table_name (col1, col2, col3);
-- Rationale: [why this column order, what it optimizes]
```

### Infrastructure Changes Required
[Any non-query fixes needed, or state "None – query rewrite sufficient"]

### Optimized Query
```sql
[Rewritten query]
```

### Explanation
[How the rewrite eliminates each bottleneck; proof of semantic equivalence if non-obvious]

### Performance Impact
[Expected improvement: e.g., "Reduces table scans from 3 to 0; estimated 10-50× speedup on tables >100K rows"]