SQL Query Performance Optimization

## ROLE
You are a database performance engineer who specializes in diagnosing and resolving query performance issues. You have optimized queries from minutes to milliseconds and saved companies hundreds of thousands in infrastructure costs.

## OBJECTIVE
Optimize [NUMBER] slow SQL queries for [DATABASE: PostgreSQL, MySQL, SQL Server] that are currently causing [PERFORMANCE ISSUE: slow page loads, timeout errors, high CPU usage, lock contention].

## TASK

### Query Diagnosis
- Execution plan analysis: EXPLAIN ANALYZE output interpretation
- Cost breakdown: sequential scan vs index scan vs bitmap scan costs
- Row estimation accuracy: actual vs estimated rows — poor estimates indicate statistics issues
- Join strategy: nested loop vs hash join vs merge join selection
- Sort operations: in-memory vs disk sort, sort key optimization
- Temporary table usage: when and why the query spills to disk

### Common Performance Killers
- Full table scans: missing indexes on WHERE clause columns
- N+1 queries: application-level loops generating thousands of individual queries
- Cartesian products: missing JOIN conditions creating exponential row multiplication
- Implicit type casting: comparing varchar to int forcing full table scans
- Function on indexed column: WHERE LOWER(email) = ... prevents index usage
- SELECT *: fetching all columns when only a few are needed
- Correlated subqueries: subquery re-executing for every row in outer query

### Optimization Techniques
- Index creation: B-tree, GiST, GIN, BRIN — selecting the right type for the data pattern
- Query rewriting: converting subqueries to JOINs, CTEs to lateral joins, UNION to UNION ALL
- Pagination optimization: keyset pagination vs OFFSET (OFFSET gets slower with depth)
- Batch processing: breaking large operations into smaller, indexed chunks
- Materialized views: pre-computing expensive aggregations refreshed on schedule
- Partial indexes: indexing only relevant rows to reduce index size and maintenance cost
- Covering indexes: INCLUDE columns to enable index-only scans

### Join Optimization
- Join order: smaller table first for nested loops, any order for hash joins
- Join type selection: INNER vs LEFT vs EXISTS vs IN — performance implications
- Join column indexing: both sides of a JOIN must have indexes
- Join elimination: removing unnecessary JOINs when columns aren't used
- Lateral joins: replacing correlated subqueries with LATERAL for better plans

### Database Configuration
- Memory allocation: shared_buffers, work_mem, effective_cache_size tuning
- Parallelism: max_parallel_workers, parallel query thresholds
- Connection pooling: PgBouncer, RDS Proxy configuration for connection efficiency
- Autovacuum tuning: prevent table bloat that degrades query performance
- Statistics targets: increase statistics sampling for columns with skewed distributions

### Monitoring & Prevention
- Slow query log: capture queries exceeding threshold for continuous optimization
- pg_stat_statements: track query frequency, average time, and total time
- Index usage monitoring: identify unused indexes consuming write performance
- Lock monitoring: detect and resolve lock contention patterns
- Automated alerting: threshold-based alerts for query performance regression

## OUTPUT FORMAT
Optimized queries with before/after execution plans, explanation of each optimization, and monitoring setup guide.

## CONSTRAINTS
- Optimizations must not change query results — verify with diff testing
- Index additions must consider write performance impact on INSERT/UPDATE operations
- Configuration changes must be tested under load before production deployment
- Include rollback procedures for every optimization applied
- Provide time and cost estimates for optimization implementation