Como ler o EXPLAIN ANALYZE no PostgreSQL: um guia de campo para planos de execução

Toda consulta lenta no PostgreSQL tem uma explicação, e ela está no plano de execução. O problema é que a saída do EXPLAIN ANALYZE parece um paredão de números. Este guia ensina a lê-la como um DBA experiente — indo direto aos quatro problemas por trás da maioria das consultas lentas.

EXPLAIN vs EXPLAIN ANALYZE

O EXPLAIN imprime o plano que o planejador pretende usar, com custos e contagens de linhas estimados — ele não executa a consulta. O EXPLAIN ANALYZE de fato a executa e adiciona os tempos e contagens reais, para você comparar expectativa com realidade. Essa comparação é o jogo inteiro.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;

Sempre adicione BUFFERS — ele revela quanto dado veio do cache (shared hit) versus disco (read), que muitas vezes é a verdadeira história por trás de uma consulta lenta.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'x' WHERE id = 1;
ROLLBACK;

O número mais importante: linhas estimadas vs reais

Seq Scan on orders  (cost=0.00..18334.00 rows=12 width=64)
                    (actual time=0.011..52.3 rows=48000 loops=1)

O planejador esperava 12 linhas; vieram 48.000. Esse erro de estimativa de 4.000× é a causa raiz da maioria dos planos ruins — o PostgreSQL escolheu uma estratégia que só faz sentido para um punhado de linhas. Corrija rodando ANALYZE orders; para atualizar as estatísticas, aumentando o statistics target, ou reescrevendo uma condição que o planejador não consegue estimar (funções sobre colunas, predicados correlacionados).

Tipos de scan, do melhor ao pior

• Index Only Scan — respondido inteiramente pelo índice. O mais rápido.
• Index Scan — o índice encontra as linhas e depois busca na heap.
• Bitmap Heap Scan — bom para seletividade média; reúne os matches e lê a heap em ordem física.
• Seq Scan — lê a tabela inteira. Correto para tabelas pequenas ou filtros pouco seletivos; um sinal de alerta em tabela grande com WHERE seletivo.

Três padrões que indicam problema

1. Sort derramando em disco

Sort  (actual rows=2000000 ...)
  Sort Method: external merge  Disk: 168000kB

external merge … Disk significa que o work_mem era pequeno demais e a ordenação derramou. Aumente o work_mem na sessão ou crie um índice que entregue as linhas já ordenadas.

2. Nested Loop com lado interno grande

Um Nested Loop é ótimo quando o lado externo é minúsculo. Se loops= é grande e cada iteração varre muitas linhas, o planejador errou a estimativa — um Hash Join ou Merge Join seria bem mais barato. Corrija a estimativa e o método de join costuma se corrigir sozinho.

3. Rows Removed by Filter

Seq Scan on events
  Filter: (status = 'error')
  Rows Removed by Filter: 9913000

Ler 10M de linhas para manter 87.000 é um caso clássico para um índice (possivelmente parcial) em status.

Uma ordem prática de leitura

1. Encontre o nó com a maior diferença de actual time — leia o plano de dentro para fora, começando pelo nó mais profundo.
2. Compare linhas estimadas vs reais nesse nó. Diferença grande? Corrija as estatísticas primeiro.
3. Procure sorts com Disk e Rows Removed by Filter alto.
4. Só então considere adicionar um índice — e rode EXPLAIN ANALYZE de novo para confirmar que ele é usado.

Lendo planos que você nunca rodou

O difícil em produção é que a consulta lenta já terminou — quando você a copia para o EXPLAIN ANALYZE, as condições já mudaram. O PG Monitoring captura o plano real de cada execução lenta no momento em que ela acontece e acompanha mudanças de plano ao longo do tempo, então você vê o plano exato que foi lento, não uma reconstrução.