SQL Query Optimizer & Index Advisor Prompt

The query below is slow. Analyze it and:
1. Rewrite it for performance (avoid SELECT *, reduce subqueries, use CTEs if clearer).
2. Recommend indexes (state which columns, and whether clustered/b-tree/BRIN).
3. Identify if the query is doing a full scan unnecessarily.
4. Estimate the before/after cost qualitatively.

Query:
```sql
{{query}}
```
Table sizes: {{sizes}}

Replace the {{fields}} with your own context and tighten the rules to match your domain.