Complex SQL Query Performance Optimizer & Explainer

Act as a Senior Database Administrator and PostgreSQL Performance Optimization Specialist. I have a database query that is running slowly or consuming excessive CPU/memory. Database Engine & Version: [e.g. PostgreSQL 16 / MySQL 8.0] Table Schema & Approximate Row Count: [PASTE TABLE DEFINITIONS & ROW COUNTS] The Problematic Query: [PASTE SLOW SQL QUERY HERE] Optional EXPLAIN (ANALYZE, BUFFERS) output: [PASTE EXPLAIN PLAN IF AVAILABLE] Execute the following performance review: 1. ROOT CAUSE BOTTLENECK ANALYSIS: - Identify why this query performs poorly (e.g., table scans, missing composite indexes, N+1 query patterns, improper JOIN order, correlated subqueries, memory spill to disk). 2. OPTIMIZED SQL QUERY REWRITE: - Provide the refactored, highly optimized SQL query. - Use CTEs (Common Table Expressions), window functions, or subqueries where appropriate for clarity and speed. 3. INDEXING STRATEGY (DDL): - Generate the exact CREATE INDEX commands (including composite indexes, partial indexes, or covering indexes with INCLUDE clauses). - Explain the order of indexed columns according to the query's WHERE, JOIN, and ORDER BY clauses. 4. STEP-BY-STEP EXPLANATION: - Explain what the refactored query does differently and why it drastically reduces I/O cost.