Interpretando EXPLAIN ANALYZE no PostgreSQL: guia prático para iniciantes

1) Introducao ao problema

O que é EXPLAIN ANALYZE e por que importa

EXPLAIN ANALYZE é a ferramenta primária do PostgreSQL para entender o que o otimizador e o executor fazem quando uma query é executada. Ele mostra o plano de execução escolhido pelo otimizador e, quando usado com ANALYZE, executa a query e fornece tempos reais e contadores de linhas. Para quem está começando, interpretar esse output faz a diferença entre aplicar um índice que não resolve nada ou reescrever a consulta para reduzir 10 segundos para 50 ms.

Analogia simples para iniciantes

Pense no banco de dados como um sistema de entrega: a query é um pedido, o otimizador escolhe a rota, e EXPLAIN ANALYZE é o GPS que te mostra a rota escolhida e o tempo real gasto em cada trecho. Às vezes o GPS (otimizador) escolhe uma rota que passa por bairros perigosos (scans sequenciais sobre tabelas grandes) enquanto uma rota alternativa com um atalho (índice aplicado corretamente) seria muito mais rápida.

O objetivo deste artigo é ensinar do zero como rodar EXPLAIN ANALYZE, ler os pontos principais do plano, reconhecer problemas comuns e aplicar correções básicas com exemplos práticos e verificáveis.

TIP: Sempre teste EXPLAIN ANALYZE em um ambiente com dados representativos. Planos variam muito com a distribuição de dados e estatísticas.

2) Pre-requisitos e setup

Ferramentas e versões

Você precisa de um servidor PostgreSQL (versão 12+ recomendada) e um cliente psql ou qualquer ferramenta que permita executar SQL. Para repetir os exemplos rapidamente localmente, use Docker com a imagem oficial postgres ou um ambiente local. Também assumimos permissões para criar tabelas e indices.

Base de dados de exemplo

Criaremos uma base de dados pequena, mas com tamanho suficiente para observar diferenças entre um scan sequencial e um index scan. Os exemplos a seguir funcionam num psql conectado ao banco correto.

Contexto antes do primeiro bloco de código: este bloco cria uma tabela de usuários e popula com 200k registros sintéticos. Execute em uma database vazia ou schema de testes.

-- Cria tabela de exemplo e popula com dados sintéticos
CREATE TABLE users (
  id serial PRIMARY KEY,
  email text NOT NULL,
  full_name text,
  created_at timestamp default now(),
  country_code char(2)
);

-- Popula com 200000 linhas (ajuste conforme RAM/disco)
INSERT INTO users (email, full_name, created_at, country_code)
SELECT
  'user' || i || '@example.com',
  'User ' || i,
  now() - (random()*365 || ' days')::interval,
  (ARRAY['us','br','de','in','cn','uk'])[1 + (i % 6)]
FROM generate_series(1,200000) AS s(i);

O que este bloco faz: cria uma tabela e insere 200.000 linhas geradas por generate_series, com distribuição simples por country_code. Após rodar, você tem dados suficientes para testar planos.

Ferramentas úteis

Recomendo também habilitar extensões que ajudam no diagnóstico, como pg_stat_statements (se possível). Mas para começar, EXPLAIN ANALYZE é suficiente.

3) Tutorial guiado passo a passo

3.1 Execução básica do EXPLAIN ANALYZE

Vamos executar uma query de busca por email. Primeiro, a versão simples sem índice para mostrar o comportamento ruim. Aqui mostramos o jeito errado: confiar que a query é "simples" e não verificar o plano.

Contexto antes do código: a query abaixo procura um email específico numa tabela sem índices adicionais. Em tabelas grandes, isso causa seq scan.

-- Query 'errada' sem índice
EXPLAIN ANALYZE
SELECT id, email, full_name FROM users WHERE email = 'user150000@example.com';

O que esperar: se não houver índice na coluna email, o PostgreSQL fará um Seq Scan (scan sequencial) na tabela inteira. A saída do EXPLAIN ANALYZE trará o tempo total e o número de linhas lidas. Abaixo um exemplo simplificado do output.

Seq Scan on users  (cost=0.00..17000.00 rows=1 width=100) (actual time=30.123..30.125 rows=1 loops=1)
  Filter: (email = 'user150000@example.com')
  Rows Removed by Filter: 199999
Planning Time: 0.123 ms
Execution Time: 30.184 ms

Interpretação: o plano mostra que foi um Seq Scan, que removeu muitas linhas. Execution Time ~30 ms é razoável aqui, mas com milhões de linhas pode ser segundos. O erro foi considerar que colunas text sem índice são rápidas.

3.2 O jeito certo: criando índice e reavaliando

Corrigir é simples: criar um índice para a coluna usada no WHERE e rerodar o EXPLAIN ANALYZE. Aqui mostramos o passo certo depois do code review do erro.

-- Cria índice apropriado e reexecuta o EXPLAIN ANALYZE
CREATE INDEX idx_users_email ON users (email);

EXPLAIN ANALYZE
SELECT id, email, full_name FROM users WHERE email = 'user150000@example.com';

O que acontece: com o índice existe a chance de um Index Scan ou Bitmap Index Scan acontecer em vez de Seq Scan. Exemplo de saída otimizada:

Index Scan using idx_users_email on users  (cost=0.29..8.30 rows=1 width=100) (actual time=0.020..0.022 rows=1 loops=1)
  Index Cond: (email = 'user150000@example.com')
Planning Time: 0.090 ms
Execution Time: 0.055 ms

Comparação clara: de ~30 ms para ~0.05 ms. O index scan reduz drasticamente o custo de leitura porque evita varrer toda a tabela. Esse é o tipo de mudança que EXPLAIN ANALYZE deixa visível.

3.3 Joins: reconhecimento de nested loop vs hash join

Exemplo comum onde o iniciante erra: fazer joins sem entender o tamanho das tabelas e sem índices nas colunas de junção. Vamos criar uma tabela de orders e mostrar um join ruim e melhorado.

-- Cria tabela orders e popula com dados
CREATE TABLE orders (
  id serial PRIMARY KEY,
  user_id integer NOT NULL,
  amount numeric(10,2),
  created_at timestamp default now()
);

INSERT INTO orders (user_id, amount, created_at)
SELECT (random()*200000)::int + 1, (random()*1000)::numeric(10,2), now() - (random()*365 || ' days')::interval
FROM generate_series(1,500000) AS s(i);

O que este bloco faz: cria uma tabela orders com 500k registros e associa cada pedido a um user_id entre 1 e 200000. Isso cria um cenário de join realista.

3.4 Exemplos de planos de join (errado x certo)

Join ruim: sem índices nas colunas de join, o otimizador pode optar por um nested loop causando muitas operações repetidas.

-- Join sem índices
EXPLAIN ANALYZE
SELECT u.id, u.email, COUNT(o.id) as total_orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country_code = 'br'
GROUP BY u.id, u.email
LIMIT 5;

Saída provável (simplificada):

Nested Loop  (cost=... ) (actual time=2000.000..2000.200 rows=5 loops=1)
  -> Seq Scan on users  (filter: country_code = 'br')
  -> Seq Scan on orders  (filter: user_id = u.id)
Execution Time: 2000.500 ms

Identificamos o problema: nested loop com scans sequenciais em orders — isso explode o tempo. Solução: index em orders.user_id e, possivelmente, usar hash aggregate/hash join dependendo do volume.

-- Correção: criar índice e reavaliar
CREATE INDEX idx_orders_user_id ON orders (user_id);

EXPLAIN ANALYZE
SELECT u.id, u.email, COUNT(o.id) as total_orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country_code = 'br'
GROUP BY u.id, u.email
LIMIT 5;

Saída provável após o índice:

Hash Join  (cost=... ) (actual time=10.000..10.050 rows=5 loops=1)
  Hash Cond: (o.user_id = u.id)
  -> Seq Scan on orders
  -> Hash  (cost=... )
      -> Seq Scan on users  (filter: country_code = 'br')
Execution Time: 10.150 ms

Interpretação: o plano mudou para Hash Join (ou Index Scan dependendo das estatísticas). O tempo caiu drasticamente porque o índice permitiu agrupar e juntar de maneira mais eficiente.

3.5 Erros comuns e armadilhas

Aqui estão armadilhas que vejo com frequência em code reviews de queries:

• Confiar em índices mal escolhidos: índice em uma coluna de alta cardinalidade geralmente ajuda; em colunas com poucas opções (p.ex. boolean) não ajuda.
• Não atualizar estatísticas: se distributions mudam, rode ANALYZE para atualizar as estatísticas que o otimizador usa.
• Exigir ORDER BY sem índice: sorting pode causar trabalho extra; criar índice que suporte ORDER BY pode evitar sort excessivo.
• Usar funções na coluna do WHERE (p.ex. lower(col) = 'x') impede o uso de índices simples; prefira índices funcionais se necessário.

DICA PRÁTICA: rode EXPLAIN (ANALYZE, BUFFERS, VERBOSE) para ter informações adicionais como leitura de buffers (índice vs disco) e entender se o tempo é I/O bound.

4) Resultado final funcionando

Checklist para um resultado aceitável

Depois de aplicar correções, valide com estes passos:

1. Rodar EXPLAIN ANALYZE antes e depois da mudança.
2. Comparar Execution Time, Rows, e tipo de plan node (Seq Scan vs Index Scan vs Hash Join).
3. Verificar contadores de buffers para confirmar leituras de disco vs cache.
4. Se disponível, comparar com métricas de produção (p.ex. p99 latência) em ambiente de teste apropriado.

Contexto: abaixo um exemplo final mostrando o uso de EXPLAIN (ANALYZE, BUFFERS) para confirmar que o índice reduziu leituras de blocos do disco.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM users WHERE email = 'user150000@example.com';

Exemplo simplificado de saída:

Index Scan using idx_users_email on users  (cost=... ) (actual time=0.030..0.032 rows=1 loops=1)
  Index Cond: (email = 'user150000@example.com')
  Buffers: shared hit=2
Planning Time: 0.100 ms
Execution Time: 0.080 ms

Interpretação: Buffers shared hit indica leitura via cache (rápida). Se víssemos shared read=... e local read=..., pode indicar leituras do disco físico - sinal de I/O mais lento.

Conferindo com dados reais

Para ter confiança que a melhoria é significativa, repita as consultas algumas vezes (com e sem caches) e em horários diferentes. Lembre-se de limpar o cache do sistema de arquivos se for comparar leituras a frio, mas isso geralmente requer privilégios administrativos e não é recomendado em produção sem cuidado.

5) Desafio para o leitor praticar

Exercício 1 — encontrar e corrigir um plano ruim

Objetivo: identifique uma consulta lenta e aplique EXPLAIN ANALYZE para entender o plano. Em seguida, proponha e aplique uma correção (índice, reescrita da query ou alteração na modelagem) e valide com EXPLAIN ANALYZE antes/depois.

1. Crie uma tabela products com 1 milhão de linhas (use generate_series).
2. Execute uma query com filtro por uma coluna categórica e observe se há seq scan.
3. Teste criar índices B-tree e índices parciais (partial index) e compare os planos.

Exercício 2 — join pesado e escolha de estratégia

Objetivo: crie duas tabelas grandes e experimente joins com e sem índices. Observe quando o PostgreSQL usa nested loop, hash join ou merge join, e por quê. Tente forçar um plano (SET enable_hashjoin = off) para ver diferenças e raciocine se a mudança é boa ou ruim.

Recomendações para validar: capture o EXPLAIN ANALYZE com BUFFERS e compare tempos e leituras.

Conclusao prática

Resumo das ações concretas

EXPLAIN ANALYZE não é apenas para curiosos: é uma ferramenta de diagnóstico. Sempre siga o fluxo: reproduza a query, rode EXPLAIN ANALYZE, identifique nodes caros (Seq Scan, Nested Loop), corrija (índice, reescrita, estatísticas) e valide novamente.

Pontos-chave para lembrar

• Seq Scan em tabela grande pode indicar falta de índice ou cardinalidade que não ajuda o índice.
• Index Scan geralmente é ótimo para buscas pontuais; Bitmap Index Scan é comum para conjuntos maiores.
• Hash Join é eficaz para junções grandes; Nested Loop é eficiente quando uma das tabelas é pequena ou quando um índice suporta a busca rápida.

Finalizando: pratique com dados reais, mantenha as estatísticas atualizadas com ANALYZE e use EXPLAIN (ANALYZE, BUFFERS, VERBOSE) quando necessário. Em code reviews, procure sempre o "antes e depois" do EXPLAIN ANALYZE — é a evidência objetiva de que a mudança melhorou o desempenho.

Este guia forneceu exemplos práticos, comandos concretos e uma mentalidade de code review: mostre o jeito errado, explique o porquê e forneça a correção com verificação por EXPLAIN ANALYZE.

Boa prática: mantenha um repositório ou notas com queries problemáticas e seus respectivos planos antes/depois. Isso vira um catálogo de soluções para problemas recorrentes no seu sistema.