EXPLAIN ANALYZE deveria ser obrigatório

Se você escreve SQL e nunca rodou EXPLAIN ANALYZE, você não escreve SQL.

Você adivinha.

A query "funciona" no dev com 200 linhas. Em prod, com 20 milhões, ela derruba o banco.

A diferença entre quem entende o banco e quem só usa

Junior roda a query. Senior roda o plano da query.

Senior abre o EXPLAIN ANALYZE antes de mandar PR.

Não porque é chique. Porque é a única forma de saber o que o Postgres realmente vai fazer com aquele SQL.

O resto é fé.

O básico do plano

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Saída típica:

Index Scan using orders_customer_id_idx on orders
  (cost=0.43..8.45 rows=1 width=128)
  (actual time=0.021..0.034 rows=1 loops=1)
Planning Time: 0.112 ms
Execution Time: 0.058 ms

Cada linha do plano é um nó. O plano é uma árvore. Lê-se de dentro pra fora, de baixo pra cima.

Sort
 └── Hash Join
      ├── Seq Scan on orders
      └── Hash
           └── Seq Scan on customers

O nó folha executa primeiro. O resultado sobe.

Decore isso. Sem isso, o resto é ruído.

Os números que importam

Pegue qualquer nó:

Index Scan using orders_customer_id_idx on orders
  (cost=0.43..8.45 rows=1 width=128)
  (actual time=0.021..0.034 rows=1 loops=1)

cost=0.43..8.45 — custo estimado em unidades arbitrárias do planner. Startup..total.
rows=1 — quantas linhas o planner acha que vai retornar.
actual time=0.021..0.034 — tempo real em ms. Startup..por execução.
rows=1 loops=1 — quantas linhas realmente vieram, vezes quantas execuções.

A unidade de custo não é ms. Não traduza. É só pra o planner comparar caminhos entre si.

O que você quer comparar é rows (estimado) vs rows no actual.

A divergência que mata

Regra de ouro:

Se rows estimado e rows real divergem em ordem de grandeza, suas estatísticas estão podres.

Exemplo clássico:

Seq Scan on orders
  (cost=... rows=12 width=...)
  (actual time=... rows=987432 loops=1)

Planner achou 12. Vieram quase um milhão.

Resultado: ele escolheu Nested Loop achando que ia rodar 12 vezes. Rodou um milhão. Query trava.

Causa quase sempre é uma de duas:

ANALYZE não rodou recentemente nessa tabela.
A distribuição da coluna é enviesada e o histograma padrão não capta.

Solução imediata:

ANALYZE orders;

Solução real: aumentar default_statistics_target na coluna, ou criar estatísticas estendidas (CREATE STATISTICS).

Seq Scan, Index Scan, Bitmap Heap Scan

Os três que você vê 95% do tempo. Decore.

Seq Scan

Lê a tabela inteira, linha por linha.

Seq Scan on users  (cost=... rows=1000000 width=...)
  Filter: (email = 'foo@bar.com')

Tabela pequena: ótimo. Tabela grande sem índice: catástrofe.

Não é sempre ruim. Se a query retorna muita linha (digamos, >5% da tabela), o planner escolhe Seq Scan de propósito — é mais barato que pular pelo índice mil vezes.

Index Scan

Caminha pelo índice e busca cada linha correspondente no heap (a tabela em si).

Index Scan using users_email_idx on users
  Index Cond: (email = 'foo@bar.com')

Ótimo para poucas linhas. Ruim se voltar muitas — cada linha é um pulo aleatório no disco.

Bitmap Heap Scan

Meio-termo. Postgres lê o índice, monta um bitmap das páginas a visitar, depois lê o heap em ordem física.

Bitmap Heap Scan on orders
  Recheck Cond: (status = 'pending')
  -> Bitmap Index Scan on orders_status_idx
       Index Cond: (status = 'pending')

Aparece quando o filtro retorna "muitas, mas não a tabela inteira". É o Postgres dizendo: "vou usar o índice, mas economizar IO."

Os joins: Nested Loop vs Hash vs Merge

Nested Loop

Para cada linha de A, busca em B.

Nested Loop
  -> Seq Scan on a
  -> Index Scan on b
       Index Cond: b.a_id = a.id

Ótimo se A é pequeno. Inferno se A tem 1 milhão de linhas.

Hash Join

Constrói um hash table com a tabela menor, depois varre a maior.

Hash Join
  -> Seq Scan on big_table
  -> Hash
       -> Seq Scan on small_table

Bom para tabelas médias/grandes. Custa memória (work_mem).

Merge Join

Ordena os dois lados e funde. Útil quando os dados já vêm ordenados (por índice, por exemplo).

Regra prática: se você vê Nested Loop com milhões de linhas no lado externo, achou seu gargalo.

BUFFERS: o que ninguém olha

Use sempre:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

A saída ganha linhas como:

Buffers: shared hit=12 read=4823

hit — páginas que estavam no cache (rápido).
read — páginas que vieram do disco (lento).

Se read é alto e a query é frequente, ou o cache não está cabendo a tabela, ou você está fazendo IO desnecessário.

Tempo não é tudo. Duas queries com tempos parecidos podem ter pegadas de IO totalmente diferentes — e em prod, sob concorrência, a com mais IO degrada todo mundo.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)

Combinação que eu uso por padrão:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT u.id, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > now() - interval '30 days'
GROUP BY u.id;

ANALYZE — executa de verdade.
BUFFERS — mostra IO.
VERBOSE — mostra colunas, schemas, output de cada nó.
SETTINGS — mostra parâmetros não-default que afetam o plano.

Aviso: ANALYZE executa a query. Não rode EXPLAIN ANALYZE DELETE se não quer apagar. Embrulhe numa transação:

BEGIN;
EXPLAIN ANALYZE DELETE FROM users WHERE ...;
ROLLBACK;

Quando o planner está errado

Acontece. O Postgres é bom, mas não é perfeito.

Sinais:

estimativa de linhas absurdamente longe do real
escolhe Nested Loop quando deveria ser Hash
ignora um índice que existe

Antes de "forçar" qualquer coisa:

ANALYZE tabela;

Depois, se persistir:

aumente default_statistics_target na coluna problemática
crie CREATE STATISTICS se há correlação entre colunas
reescreva a query (CTE materializada, subquery, lateral join)

Última instância: parâmetros de sessão como SET enable_nestloop = off; — útil para diagnosticar, nunca para deixar em produção.

Dentro do Rails

.explain em qualquer relation:

User.where(active: true).joins(:orders).explain

Saída idêntica ao psql. Vai pro stdout do console.

Para ANALYZE, em Rails 7+:

User.where(active: true).explain(:analyze, :buffers)

Em versões anteriores, vai direto no banco. Não confie só no .explain simples — ele não executa, só estima. Pra pegar a divergência estimado-vs-real, precisa do ANALYZE.

Bonus: combine com ActiveRecord::Base.logger para ver a query exata sendo gerada antes de explicar.

Padrões que aparecem toda semana

`Seq Scan` em tabela grande com filtro seletivo

Falta índice. Crie.

CREATE INDEX CONCURRENTLY orders_status_idx ON orders(status);

`Index Scan` retornando milhões de linhas

Índice errado para o caso. Talvez você precise de Bitmap, ou de um índice composto, ou nem deveria ter filtro nessa coluna.

`Nested Loop` com `loops=1000000`

O lado externo está estourado. Provavelmente uma estimativa errada. Roda ANALYZE e olha de novo.

`Sort` com `external merge Disk`

Faltou work_mem. A ordenação derramou pra disco.

Sort Method: external merge  Disk: 41216kB

Aumente work_mem para a sessão ou crie índice que já entregue ordenado.

`Hash` com `Batches: 8`

Mesmo problema, hash não coube em memória, virou batches.

A grande virada de chave

Você não otimiza SQL escrevendo SQL "mais bonito".

Você otimiza lendo o plano e mudando a forma como o Postgres vai executar.

Toda otimização real de query passa por:

1. EXPLAIN ANALYZE BUFFERS
   ↓
2. Identifica nó mais caro
   ↓
3. Entende por que ele é caro (estimativa? IO? join errado?)
   ↓
4. Muda algo (índice, query, estatística)
   ↓
5. EXPLAIN ANALYZE de novo — confirma

Sem o passo 1 e 5, é palpite.

Conclusão

Olhar pro plano não é opcional.

É a única forma honesta de saber o que seu banco está fazendo.

A query "rápida no dev e lenta em prod" é quase sempre uma query que ninguém olhou o plano em prod — com os dados de prod, com as estatísticas de prod, com a concorrência de prod.

Decore os três scans. Decore os três joins. Ative BUFFERS por padrão. Desconfie de divergência entre estimado e real.

Faz isso por seis meses e você vira a pessoa que o time chama quando algo está lento.

Não porque você é gênio.

Porque você lê o plano.