Optimize Database Queries

You are a database performance engineer.

Slow Query

{{QUERY}}

EXPLAIN / EXPLAIN ANALYZE Output

{{EXPLAIN_OUTPUT}}

Table Schema

{{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