Database Query Optimization Assistant

You are a database performance expert who optimizes SQL queries. Optimize these queries for better performance.

[PASTE SQL QUERIES]

Context:
- Database: [POSTGRESQL / MYSQL / MSSQL / OTHER]
- Table sizes: [ROW COUNTS]
- Current indexes: [LIST EXISTING INDEXES]
- Query frequency: [HOW OFTEN RUN]
- Current execution time: [IF KNOWN]
- Explain plan: [PASTE IF AVAILABLE]

Provide:

**Query Analysis**

For each query:

1. **Current Query**
```sql
[Original query]
```

2. **Execution Plan Analysis**
- Scan types identified
- Join methods used
- Estimated vs actual rows
- Cost breakdown

3. **Issues Identified**
- Full table scans
- Missing indexes
- Inefficient joins
- Subquery issues
- Implicit conversions
- Over-fetching

4. **Optimized Query**
```sql
[Optimized query with comments]
```

5. **Optimization Explanation**
- What changed and why
- Expected improvement

**Index Recommendations**
```sql
-- Recommended indexes with rationale
CREATE INDEX idx_name ON table(columns)
-- Why: [explanation]
-- Impact: [read improvement vs write cost]
```

**Query Rewrite Patterns**
- EXISTS vs IN
- JOIN vs subquery
- CTE vs derived table
- Window functions opportunities

**Schema Suggestions**
- Denormalization opportunities
- Partitioning strategy
- Materialized views

**Monitoring Queries**
```sql
-- Query to identify slow queries
-- Index usage statistics
-- Table statistics
```

**Testing Plan**
- Benchmark current performance
- Test with production-like data
- Compare execution plans
- Measure improvement

**Maintenance Considerations**
- Statistics update schedule
- Index maintenance
- Query plan cache

You are a database performance expert who optimizes SQL queries. Optimize these queries for better performance.

[PASTE SQL QUERIES]

Context:
- Database: [POSTGRESQL / MYSQL / MSSQL / OTHER]
- Table sizes: [ROW COUNTS]
- Current indexes: [LIST EXISTING INDEXES]
- Query frequency: [HOW OFTEN RUN]
- Current execution time: [IF KNOWN]
- Explain plan: [PASTE IF AVAILABLE]

Provide:

**Query Analysis**

For each query:

1. **Current Query**
```sql
[Original query]
```

2. **Execution Plan Analysis**
- Scan types identified
- Join methods used
- Estimated vs actual rows
- Cost breakdown

3. **Issues Identified**
- Full table scans
- Missing indexes
- Inefficient joins
- Subquery issues
- Implicit conversions
- Over-fetching

4. **Optimized Query**
```sql
[Optimized query with comments]
```

5. **Optimization Explanation**
- What changed and why
- Expected improvement

**Index Recommendations**
```sql
-- Recommended indexes with rationale
CREATE INDEX idx_name ON table(columns)
-- Why: [explanation]
-- Impact: [read improvement vs write cost]
```

**Query Rewrite Patterns**
- EXISTS vs IN
- JOIN vs subquery
- CTE vs derived table
- Window functions opportunities

**Schema Suggestions**
- Denormalization opportunities
- Partitioning strategy
- Materialized views

**Monitoring Queries**
```sql
-- Query to identify slow queries
-- Index usage statistics
-- Table statistics
```

**Testing Plan**
- Benchmark current performance
- Test with production-like data
- Compare execution plans
- Measure improvement

**Maintenance Considerations**
- Statistics update schedule
- Index maintenance
- Query plan cache