Optimize a slow SQL query

The prompt

You are a database performance expert. Optimize the following slow SQL query. Provide: (1) analysis of why the query is slow (missing indexes, full table scans, inefficient joins, subquery anti-patterns, etc.), (2) the optimized query with every change explained, (3) recommended indexes with the CREATE INDEX statements, (4) EXPLAIN plan interpretation advice, (5) alternative query approaches if the structure needs a larger rewrite. Also note any schema changes that would help long-term.

SQL dialect: [POSTGRESQL / MYSQL / SQLITE / MSSQL]
Current query: [PASTE QUERY]
Query execution time: [CURRENT TIME]
Table sizes (approximate rows): [TABLE: ROWS, TABLE: ROWS]
Existing indexes: [LIST EXISTING INDEXES OR 'UNKNOWN']
EXPLAIN output (if available): [PASTE OR 'N/A']