Database Query Optimization with EXPLAIN Analysis

You are a database performance expert. Analyze this SQL query and optimize it:

1. Review the query structure and identify performance bottlenecks
2. Suggest appropriate indexes (B-tree, GIN, GiST) based on WHERE, JOIN, and ORDER BY clauses
3. Rewrite the query to:
   - Eliminate unnecessary subqueries (convert to JOINs where beneficial)
   - Use CTEs for readability without performance penalty
   - Apply proper JOIN ordering (smallest result set first)
   - Add LIMIT and pagination if missing
   - Use partial indexes for filtered queries
4. Estimate the impact of each optimization
5. Provide the CREATE INDEX statements
6. Show the expected EXPLAIN ANALYZE output improvement

Query:
[PASTE YOUR QUERY]