Nested Loop, Hash Join, Merge Join
Nós de junção combinam conjuntos de linhas de duas entradas. O PostgreSQL 18.4 escolhe nested loop, hash ou merge join com base em contagens de linhas, memória disponível, oportunidades de índice e ordem de classificação.
Receita
Cartão de receita de referência rápida - pronto para copiar e colar.
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '1 day';Quando usar isso: Consultas com muitas junções que disparam CPU ou I/O. Identifique qual algoritmo de junção domina o plano.
Exemplo de Trabalho
CREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(id),
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_created_at_idx ON orders (created_at);
INSERT INTO customers (name)
SELECT 'customer-' || i FROM generate_series(1, 50000) AS i;
INSERT INTO orders (customer_id, created_at)
SELECT (random() * 49999 + 1)::bigint, now() - (random() * interval '30 days')
FROM generate_series(1, 500000);
ANALYZE customers;
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '1 day';O que isso demonstra:
- Nested loop com índice no lado interno para conjuntos externos pequenos
- Hash join quando construir uma tabela hash é mais barato do que sondagens repetidas de índice
- Como filtros seletivos encolhem a entrada externa antes da junção
Mergulho Profundo
Como Funciona
- Nested Loop - Para cada linha externa, escaneia a entrada interna (frequentemente baseada em índice). Vantajoso quando a entrada externa é minúscula ou a interna tem um índice seletivo.
- Hash Join - Constrói uma tabela hash na entrada menor, sonda com a maior. Requer
work_mem; descarrega para disco se excedido (Hash Buckets/Batchesno plano). - Merge Join - Ambas as entradas são ordenadas pelas chaves de junção; mescla como merge-sort. Requer entradas pré-ordenadas ou ordenações explícitas; excelente para grandes equi-joins em dados ordenados.
Sinais de Seleção de Algoritmo
| Algoritmo | Condições favoráveis |
|---|---|
| Nested Loop | Cardinalidade externa pequena; índice na chave de junção interna |
| Hash Join | Conjuntos médios/grandes; equi-join; work_mem suficiente |
| Merge Join | Entradas já ordenadas; grandes junções sequenciais |
Notas SQL
-- Revela a ordem e o tipo da junção
EXPLAIN (VERBOSE, ANALYZE)
SELECT count(*)
FROM orders o
JOIN customers c ON c.id = o.customer_id;
-- Indicador de spill de hash
-- Procure por: "Buckets: ... Batches: 2" no nó Hash Join
SET work_mem = '64MB';Armadilhas
- Nested loop com scan sequencial interno - Desastre em escala: O(n*m). Correção: Indexe a chave de junção interna ou reescreva para hash join através de estatísticas melhores.
- Spill de disco do hash join -
Batches > 1significa quework_memé muito baixo para esse nó. Correção: Aumentework_memapenas para a função de relatório, ou reduza as linhas mais cedo com filtros. - Cross join disfarçado de nested loop - Condição de junção ausente. Correção: Verifique as cláusulas
ON/USINGno SQL gerado pelo ORM. - Merge join com dupla ordenação - Duas ordenações caras quando nenhuma das entradas está ordenada. Correção: Indexe as colunas principais para corresponder às chaves de junção e
ORDER BY. - Estatísticas desatualizadas alterando o tipo de junção - O plano muda após
ANALYZEsem alterações no código. Correção: Capture planos em CI; alerte sobre regressões.
Alternativas
| Alternativa | Usar Quando | Não Usar Quando |
|---|---|---|
| Desnormalizar chaves de junção "quentes" | Fatia de dashboard com muitas leituras | OLTP normalizado com muitas escritas |
| Visão materializada | Junção cara reutilizada horariamente | Consistência em tempo real necessária |
| Subconsulta LATERAL | Padrão Top-N por grupo | Junção simples de duas tabelas grandes |
FAQs
Qual junção é mais rápida?
Depende da cardinalidade e dos índices. Não há um vencedor universal; leia o plano para o tamanho dos seus dados.
O que é um nó Memoize?
O PostgreSQL 14+ armazena em cache os resultados do nested-loop interno para chaves externas repetidas. Ótimo para nested loop com parâmetros internos estáveis.
Posso forçar um hash join?
SET enable_nestloop = off apenas em uma sessão de teste. Corrija estatísticas e índices em vez disso para produção.
Por que merge join em tabelas não ordenadas?
O planejador adiciona nós Sort em ambas as entradas. Verifique o custo da ordenação; um índice pode remover as ordenações.
A ordem da junção importa?
Sim. O PostgreSQL considera reordenar através do otimizador genético em muitas tabelas. Estimativas ruins produzem ordem ruim.
Planos de Semi-join e Anti-join?
EXISTS, IN e NOT EXISTS frequentemente se tornam nós hash semi-join ou nested loop semi-join.
Hash join paralelo?
Grandes construções podem usar Parallel Hash Join no PostgreSQL 18. Requer max_parallel_workers_per_gather > 0 e entradas grandes.
Como as chaves estrangeiras ajudam?
Elas não indexam automaticamente. Você ainda precisa de índices nas colunas de junção filhas para o desempenho do nested loop.
O que é um sinal de aviso de produto cartesiano?
Nested loop com scan sequencial interno e contagens de linhas multiplicando em direção ao quadrado do tamanho do resultado.
O que devo ler em seguida?
Página de agregações Sort e Hash para padrões de spill de memória compartilhados com hash joins.
Relacionado
- Agregações Sort e Hash - Spills de
work_mem - Estudos de Caso de Planos Ruins - Falhas na ordem de junção
- Noções Básicas de Design de Índice - Índices de colunas de junção
- Ajuste de work_mem - Memória por nó
Versões da Pilha: Esta página foi escrita para PostgreSQL 18.4 (estável 18, manutenção 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x e PostGIS 3.5+.