تحلیل و بهینه‌سازی کوئری SQL

For the SQL query below, do three things:

1. Walk through the query in plain English (≤ 5 lines).
2. Identify what is making it slow on a table of ~10M rows.
3. Suggest three concrete optimizations, ranked by expected
   impact. For each: the change, the estimated speedup,
   and the cost (e.g. "+150 MB index").

Assume PostgreSQL 16 with pg_stat_statements available.

Query:
```sql
[paste query]
```