Prompt para query optimization em SQL

"Você é um especialista em otimização de consultas SQL. Analise a seguinte consulta complexa, que acessa múltiplas tabelas grandes e utiliza subconsultas correlacionadas, e proponha refatorações detalhadas para melhorar significativamente seu desempenho. Inclua exemplos de índices recomendados, se aplicável, e justifique cada alteração, explicando como ela contribui para reduzir o custo de I/O, o uso de CPU e/ou o tempo de execução.

**Consulta a ser otimizada:**

```sql
SELECT
    c.nome_cliente,
    p.nome_produto,
    SUM(li.quantidade * li.preco_unitario) AS valor_total_item,
    (SELECT AVG(li2.quantidade * li2.preco_unitario)
     FROM tb_linhas_pedido li2
     WHERE li2.id_produto = li.id_produto
       AND li2.data_pedido BETWEEN '2023-01-01' AND '2023-12-31') AS media_valor_anual_produto
FROM
    tb_clientes c
JOIN
    tb_pedidos ped ON c.id_cliente = ped.id_cliente
JOIN
    tb_linhas_pedido li ON ped.id_pedido = li.id_pedido
JOIN
    tb_produtos p ON li.id_produto = p.id_produto
WHERE
    ped.data_pedido >= '2023-01-01'
GROUP BY
    c.nome_cliente,
    p.nome_produto
HAVING
    SUM(li.quantidade * li.preco_unitario) > 1000
ORDER BY
    valor_total_item DESC;
```

**Espera-se na sua resposta:**

1.  **Análise da consulta atual:** Pontos fracos e gargalos de desempenho.
2.  **Versão otimizada da consulta:** Uma ou mais alternativas à consulta original.
3.  **Justificativa detalhada:** Explicação clara de cada mudança e seu impacto na performance (ex: remoção de subconsultas correlacionadas, uso de CTEs, funções de janela, desnormalização estratégica, etc.).
4.  **Sugestão de índices