Slow Query Optimization with EXPLAIN ANALYZE and Indexes

Analyze and optimize the following slow queries for the [COMPANY NAME] database with [NUMBER] records in the main table ([TABLE NAME]).\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Original slow query:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`sql\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n[PASTE SLOW QUERY HERE]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Environment details:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Database: [PostgreSQL/MySQL version]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Main table: [NUMBER] records\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Existing indexes: [LIST CURRENT INDEXES]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Current execution time: [SECONDS]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Performance target: [SECONDS]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**For each query, provide:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n1. **EXPLAIN ANALYZE diagnosis:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - Interpret the execution plan line by line\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - Identify: Seq Scan vs Index Scan, Nested Loop vs Hash Join, Disk-based Sort\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - Point out the most expensive node (bottleneck)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - Estimate I/O cost\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n2. **Index optimizations:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - Recommended indexes (B-tree, Hash, GIN, BRIN) with justification\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - Composite indexes in the correct order (selectivity)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - Partial indexes where applicable (WHERE is_active = true)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - Estimated impact on INSERT/UPDATE\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n3. **Query rewriting:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - Optimized version with CTEs when they improve readability\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - Replace correlated subqueries with JOINs or EXISTS\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - Use window functions instead of self-joins\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - Optimized LIMIT + OFFSET with keyset pagination\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n4. **Before/after comparison:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - Table with: metric, before, after, improvement %\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - EXPLAIN ANALYZE of the optimized query\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n5. **General best practices:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - When to use materialization (MATERIALIZED VIEW)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - Table partitioning by [date/region]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - Relevant postgresql.conf settings (work_mem, shared_buffers)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n   - VACUUM and ANALYZE routine\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nFocus on production environment with real data.