Joins e índices no PostgreSQL com plano de consulta

Um join combina linhas relacionadas de duas fontes. Nesta aula, você vai ligar alunos a matrículas, manter alunos sem matrícula quando a pergunta exigir e criar um índice candidato. A prova não será “a consulta rodou”: você vai comparar a saída esperada e observar o plano do PostgreSQL.

Pense em duas listas num evento. Uma contém participantes; outra, inscrições em oficinas. O número da pessoa permite juntar as linhas certas. INNER JOIN mostra pares existentes; LEFT JOIN também mantém quem está na lista da esquerda sem par. O limite da analogia é que o otimizador pode escolher diferentes algoritmos de join, e custo depende de volume, distribuição, filtros e memória.

Escreva a relação no ON

Para listar matrículas com o nome do aluno:

SELECT
  m.id AS matricula_id,
  a.nome AS aluno,
  m.status
FROM matriculas AS m
INNER JOIN alunos AS a
  ON a.id = m.aluno_id
ORDER BY m.id;

ON descreve como as linhas correspondem. Aliases curtos reduzem repetição sem apagar o significado. Qualifique colunas como m.id e a.id quando o nome existe em mais de uma tabela.

O INNER JOIN não mostra alunos sem matrícula. Para um relatório que precisa incluí-los, mude a direção da pergunta:

SELECT
  a.id,
  a.nome,
  count(m.id) AS total_matriculas
FROM alunos AS a
LEFT JOIN matriculas AS m
  ON m.aluno_id = a.id
GROUP BY a.id, a.nome
ORDER BY a.nome;

count(m.id) produz zero quando não há matrícula, pois count ignora NULL. count(*) contaria a linha preservada do aluno e daria um resultado diferente. Escolha a expressão de contagem de acordo com o que está sendo contado.

Num LEFT JOIN, a posição de um filtro muda a resposta. Colocar m.status = 'ativa' no ON preserva alunos sem matrícula ativa; colocar a mesma condição no WHERE remove linhas cujo lado direito ficou NULL e pode fazer o resultado se comportar como inner join. Escreva primeiro quem precisa permanecer na saída e teste um aluno sem par.

Reproduza um produto cartesiano

Este comando omite a condição de ligação:

SELECT a.nome, m.id
FROM alunos AS a, matriculas AS m;

Cada aluno combina com cada matrícula. Com 100 alunos e 500 matrículas, saem 50 mil linhas. Esse é o erro controlado: execute somente com poucas linhas de teste, compare a contagem e corrija com JOIN ... ON. Um resultado grande não é prova de que o banco “duplicou dados”; a consulta pediu todas as combinações.

Crie índice para uma consulta, não para uma tabela

Primary keys e restrições unique criam índices de suporte. Uma foreign key não cria automaticamente um índice na coluna que referencia. Como joins e remoções do pai procuram matriculas.aluno_id, este candidato pode ajudar:

CREATE INDEX matriculas_aluno_id_idx
ON matriculas (aluno_id);

Se a consulta filtra por aluno e status, um índice composto pode ser mais útil, mas a ordem deve seguir as condições reais. Cada índice ocupa espaço e cobra manutenção em INSERT, UPDATE e DELETE.

Use o plano:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status
FROM matriculas
WHERE aluno_id = 42;

ANALYZE executa a consulta. Aqui é um SELECT; tenha cuidado com comandos que modificam dados. Compare linhas estimadas e reais, tempo, buffers e o tipo de varredura. Em tabela pequena, uma leitura sequencial pode ser a decisão correta. Não force um índice apenas para ver seu nome no plano.

O simulador SQL do curso não executa joins, agregações, índices ou EXPLAIN; ele aceita somente uma tabela e uma parte básica de SELECT. Por isso esta aula não oferece laboratório enganoso. Sua missão é montar três alunos e duas matrículas num PostgreSQL isolado, prever a saída de INNER e LEFT JOIN, reproduzir o produto cartesiano e comparar o plano antes e depois do índice. Na aula final, você aplicará essas peças a um banco de pedidos.