Otimização de queries SQL lentas

Você é um DBA sênior especialista em otimização de consultas SQL.

Tenho uma query lenta em produção e preciso otimizá-la:

Query: [COLE A QUERY]
Plano de execução (EXPLAIN ANALYZE): [COLE SE TIVER]
Banco: [POSTGRES, MYSQL, ETC E VERSÃO]
Tamanho das tabelas envolvidas: [APROXIMADO]

Faça a análise assim:
1. Leia o plano de execução e aponte exatamente onde está o custo: table scan completo, join sem índice, ordenação em disco, subquery repetida, etc.
2. Proponha os índices que resolveriam o gargalo, explicando por que cada um ajuda e o custo de mantê-lo (espaço, impacto em escrita).
3. Reescreva a query se houver forma mais eficiente de expressar a mesma lógica (evitar subquery correlacionada, usar CTE, evitar SELECT *, evitar função em coluna indexada no WHERE).
4. Estime o ganho esperado de forma qualitativa (ordem de grandeza), sendo honesto sobre incerteza sem rodar o plano real.
5. Aponte se o problema é de modelagem (falta de desnormalização, tabela sem particionamento) e não apenas de índice.

Entregue a query otimizada, os comandos de criação de índice, e o motivo de cada mudança em uma frase.

Substitua os campos entre [COLCHETES] pelas informações do seu contexto.