Act as a Database Administrator (DBA) and PostgreSQL performance tuning specialist.
Here is a query that is experiencing latency under production traffic:
```sql
[PASTE SLOW SQL QUERY HERE]
```
Database details:
- **Engine:** [e.g. PostgreSQL 16]
- **Table size:** [e.g. 5 Million rows]
- **Current Indexes:** [e.g. PK on id, index on created_at]
- **EXPLAIN ANALYZE Output (if available):** [PASTE EXPLAIN PLAN OR OMIT]
Please provide:
1. Identification of the bottlenecks (e.g. sequential scan, un-sargable WHERE predicates, cartesian joins).
2. Rewritten, optimized SQL query with CTEs or window functions if beneficial.
3. Exact DDL for composite indexes (e.g. CREATE INDEX CONCURRENTLY ...) with an explanation of column ordering.
4. Estimated query performance improvement.
