Otimização de queries usando CTE

Hoje a gente vai mergulhar no mundo (empolgante e um pouco assustador) da otimização de queries usando CTE (Common Table Expressions). Se tu já pegou o jeito de criar CTE (a gente falou disso nas aulas anteriores), agora é hora de sacar os detalhes internos, as pegadinhas e como tirar o máximo de eficiência delas.

De primeira, CTE parece perfeito: o código fica limpo, fácil de escrever, dá pra separar em blocos lógicos. Mas tem um detalhe (às vezes nem tão pequeno assim). O PostgreSQL tem uma estratégia específica pra lidar com CTE, e isso afeta a performance.

Quando o PostgreSQL vê um WITH, normalmente ele materializa o resultado do CTE. Isso quer dizer que os dados retornados pelo CTE são calculados e salvos como uma tabela temporária, que depois é usada na query principal. Isso é massa pra reusar, mas pode virar problema se:

1. O volume de dados no CTE é gigante e só uma parte é usada no final.
2. O CTE é chamado muitas vezes, aumentando o custo.
3. A gente cria CTEs complexos à toa, sem necessidade real.

Bora entender a materialização

Materialização é o processo onde o PostgreSQL salva o resultado do CTE na memória ou no disco (depende do tamanho dos dados). Ou seja, os dados são buscados só uma vez, mas se tu usa o CTE só em um lugar, materializar pode ser desnecessário. Por exemplo:

WITH conjunto_grande AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM conjunto_grande
WHERE grade > 90;

Nesse caso, o PostgreSQL primeiro cria uma tabela temporária com o resultado completo do CTE (grade > 60), e só depois filtra as linhas onde grade > 90. Isso adiciona um passo intermediário desnecessário e afeta a performance.

Como evitar materialização desnecessária?

Desde o PostgreSQL 12, dá pra evitar materialização do CTE quando não precisa. Pra isso, usa as palavras MATERIALIZED (padrão) ou NOT MATERIALIZED. Olha só:

WITH conjunto_grande AS NOT MATERIALIZED (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM conjunto_grande
WHERE grade > 90;

Aqui a gente diz pro PostgreSQL não materializar o conjunto_grande, mas sim embutir a query direto na expressão principal. Isso deixa a query mais rápida, porque não cria tabela temporária no meio do caminho.

Quando materializar é bom?

Não pensa que materializar é sempre ruim! Se tu usa o CTE várias vezes na mesma query ou precisa calcular ele de forma independente, materializar pode ser útil. Exemplo:

WITH exemplo_materializado AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)

SELECT student_id
FROM exemplo_materializado
WHERE grade > 90

UNION ALL

SELECT student_id
FROM exemplo_materializado
WHERE grade < 70;

Aqui, materializar evita recalcular o filtro grade > 60 toda hora.

Otimização de queries usando índices

Pra CTE rodar mais rápido, usa índices nas tabelas base de onde tu puxa os dados. Tipo assim:

CREATE INDEX idx_students_grades_grade ON students_grades(grade);

WITH alunos_filtrados AS (
    SELECT student_id, grade
    FROM students_grades
    WHERE grade > 90
)
SELECT *
FROM alunos_filtrados;

O índice na coluna grade faz o PostgreSQL buscar mais rápido as linhas que batem com grade > 90. Isso é ainda mais importante quando a tabela é grandona.

Dividindo CTEs grandes em menores

Se o CTE retorna muitos dados que depois vão ser filtrados ou agregados, é melhor dividir em etapas. Em vez de um CTEzão complicado, faz vários pequenos:

Ruim (CTE grande):

WITH consulta_grande AS (
    SELECT s.student_id, AVG(g.grade) AS avg_grade
    FROM students s
    JOIN grades g ON s.student_id = g.student_id
    WHERE g.subject_id = 101 AND g.grade > 85
    GROUP BY s.student_id
)
SELECT *
FROM consulta_grande
WHERE avg_grade > 90;

Melhor (dividido em etapas):

WITH notas_filtradas AS (
    SELECT student_id, grade
    FROM grades
    WHERE subject_id = 101 AND grade > 85
),
medias_alunos AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM notas_filtradas
    GROUP BY student_id
)
SELECT *
FROM medias_alunos
WHERE avg_grade > 90;

Assim o PostgreSQL consegue otimizar melhor a execução da query.

Exemplo prático: analisando estrutura e otimizando

Bora ver um exemplo mais complexo. Temos tabelas de estudantes, cursos e notas. Queremos achar estudantes com média alta e mostrar a lista deles junto com os cursos:

WITH feras AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
cursos_alunos AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)
SELECT fa.student_id, fa.avg_grade, ca.course_name
FROM feras fa
JOIN cursos_alunos ca ON fa.student_id = ca.student_id;

Essa query pode ficar mais rápida se tu criar índices nas tabelas grades e enrollments, acelerando o filtro e o join.

Monitoring: analisando performance

Pra sacar se a query tá eficiente, usa EXPLAIN ou EXPLAIN ANALYZE. Tipo assim:

EXPLAIN ANALYZE
WITH feras AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM feras;

Essa query mostra quanto tempo cada passo leva e te ajuda a ver onde dá pra melhorar a performance.

Mais detalhes sobre EXPLAIN ANALYZE a gente vê nos próximos níveis :P

Erros comuns na otimização de CTE

1. Esquecer dos índices. Se tu filtra dados no CTE mas a tabela base não tem índice, a performance vai pro saco.
2. Usar CTEs grandes demais. Se uma query faz coisa demais, pode acabar materializando um monte de dados desnecessários.
3. Abusar do NOT MATERIALIZED. Em alguns casos, materializar é necessário pra não recalcular o CTE toda hora.
4. Ignorar o monitoring. Sem analisar com EXPLAIN, tu pode nem perceber que as queries tão lentas.

Agora tu já tá pronto pra otimizar queries usando CTE, fugindo das armadilhas e ganhando performance! Lembra que CTE é uma ferramenta, não uma solução mágica. Usa com consciência e eles vão ser teus melhores parceiros no PostgreSQL.