Como ler planos de consulta: EXPLAIN e EXPLAIN (ANALYZE, BUFFERS)

Quando uma consulta de repente fica lenta, adivinhar é perda de tempo: pergunte ao banco de dados o que ele realmente planeja fazer. No PostgreSQL é para isso que serve o EXPLAIN: ele imprime o plano de execução que o planejador escolheu. E o EXPLAIN (ANALYZE, BUFFERS) vai além, executando de fato a consulta e relatando os tempos reais e os acessos ao disco. Este artigo percorre como ler um plano, em que o Seq Scan difere do Index Scan, por que as estimativas divergem da realidade e como um plano revela um índice ausente.

Vamos usar um pequeno esquema de comércio eletrônico: users, orders e employees.

EXPLAIN vs EXPLAIN ANALYZE

O EXPLAIN apenas estima o plano sem executar a consulta. O EXPLAIN ANALYZE realmente a executa (cuidado com INSERT/UPDATE/DELETE!) e anexa números reais.

-- plan and estimates only; the query does NOT run
EXPLAIN
SELECT * FROM orders WHERE user_id = 42;

-- real execution + timings + cache/disk reads
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42;

Uma linha superior típica de um plano:

Seq Scan on orders  (cost=0.00..1834.00 rows=120 width=64)
                    (actual time=0.015..12.4 rows=118 loops=1)

O que importa aqui:

• cost=0.00..1834.00 — custo estimado em unidades arbitrárias: custo de inicialização e custo total. Não são milissegundos.
• rows=120 — quantas linhas o planejador espera.
• actual time=... e actual rows=118 — o que realmente aconteceu.
• loops=1 — quantas vezes o nó foi executado (crucial dentro de loops aninhados).

Para rodar o EXPLAIN ANALYZE em uma consulta que altera dados sem efeitos colaterais, envolva-a em uma transação:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM orders WHERE status = 'cancelled';
ROLLBACK;  -- nothing is actually deleted

Seq Scan vs Index Scan

Um Seq Scan (varredura sequencial) lê a tabela inteira linha por linha. Isso nem sempre é ruim: se você precisa da maior parte da tabela, lê-la de uma vez é mais barato do que ficar saltando por um índice.

Um Index Scan usa um índice para localizar com precisão as linhas correspondentes. Ele vence quando o predicado seleciona uma fração pequena da tabela (um filtro seletivo).

-- selective: one user out of a million -> Index Scan
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

-- non-selective: half the table -> a Seq Scan is expected and fine
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'paid';

Existe também o Bitmap Heap Scan, um meio-termo: o PostgreSQL reúne as correspondências do índice em um bitmap e depois lê a tabela em lotes na ordem física. O planejador o escolhe para uma seletividade "média": de centenas a milhares de linhas.

Resista à tentação de "consertar" o planejador com hints; em vez disso, forneça estatísticas atualizadas:

ANALYZE orders;  -- rebuild the data distribution statistics

Pegadinha: o MySQL também tem EXPLAIN ANALYZE (desde a 8.0), mas o formato de saída é diferente e os tempos são relatados por nó em tempo real. O ClickHouse usa EXPLAIN PLAN / EXPLAIN PIPELINE e não tem o conceito de índice em nível de linha; em vez disso, ele tem uma chave primária esparsa e o salto de grânulos.

Linhas estimadas vs reais

A habilidade mais valiosa de todas é comparar rows (estimativa) com actual rows (realidade). Uma diferença grande significa que o planejador tinha estatísticas ruins e pode ter escolhido um plano fraco.

EXPLAIN ANALYZE
SELECT *
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.country = 'DE' AND o.created_at > '2026-01-01';

Se você ver algo como:

Nested Loop  (rows=5 ... actual rows=48000 loops=1)

— isso é um sinal de alerta. O planejador esperava 5 linhas e escolheu um Nested Loop, mas obteve 48000. Nesse volume, um Hash Join teria sido muito mais barato. Causas comuns da diferença:

• estatísticas desatualizadas — corrigidas com ANALYZE;
• colunas correlacionadas (por exemplo, city e country) — ajudadas por CREATE STATISTICS;
• expressões complexas no filtro que não têm estatísticas.

Leia as estimativas de baixo para cima: um erro em um nó folha se infla por toda a árvore do join.

Como detectar um índice ausente

Um índice ausente tem uma assinatura muito reconhecível: um Seq Scan sobre uma tabela grande com um predicado seletivo em Filter, onde Rows Removed by Filter é enorme.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM employees WHERE email = 'anna@example.com';

Seq Scan on employees  (cost=0.00..21000 rows=1 ...)
  Filter: (email = 'anna@example.com')
  Rows Removed by Filter: 499999
  Buffers: shared read=8200

Os sinais de alerta:

• um Seq Scan onde você esperava uma única linha (rows=1);
• Rows Removed by Filter: 499999 — o motor varreu metade da tabela à toa;
• Buffers: shared read=8200 — muitos blocos trazidos do disco.

A correção é um índice sobre a coluna filtrada:

CREATE INDEX idx_employees_email ON employees (email);

Depois disso, o mesmo plano se transforma em um Index Scan com Buffers: shared read=4 e tempos na casa dos microssegundos. Para consultas que precisam de apenas algumas colunas, considere um índice de cobertura:

CREATE INDEX idx_orders_user_created
  ON orders (user_id, created_at) INCLUDE (status);

Pegadinha: um índice não será usado se a coluna estiver envolvida em uma função (WHERE lower(email) = ...); o planejador não consegue fazer a correspondência. Ou construa um índice funcional (CREATE INDEX ... ON employees (lower(email))), ou tire a função do predicado.

Crie o hábito de rodar EXPLAIN (ANALYZE, BUFFERS) em qualquer consulta lenta antes de recorrer a um índice ou reescrever o SQL. O plano diz exatamente para onde o tempo vai, e a partir daí é engenharia, não adivinhação.