O que é EXPLAIN e como usar pra analisar queries

Imagina que você tá desenvolvendo um app e, do nada, uma das queries vira a mais pesada do seu banco. Você começa a notar travadas e lentidão no app. É aí que entra o EXPLAIN, que te ajuda a sacar onde tudo começou a dar ruim. Otimizar queries com base na análise do EXPLAIN te faz economizar recurso, ganhar tempo e deixar a galera feliz usando seu app.

EXPLAIN é seu jeito de dar uma espiada dentro do PostgreSQL e ver como o banco vai executar a query. Ele mostra se vai rolar um index ou se vai ser aquele scanzão na tabela toda, quais passos o otimizador vai seguir, em que ordem e o tamanho dos resultados intermediários.

Em outras palavras, EXPLAIN te deixa sacar o que esperar da execução da query: se ela é "pesada", quantas linhas devem ser processadas e que recursos vão ser usados. É uma ferramenta indispensável quando a query começa a travar e você precisa descobrir o motivo.

EXPLAIN é tipo uma lanterna no escuro: com ele, dá pra ver o que tá rolando por baixo dos panos e onde tá o problema.

Sintaxe do comando EXPLAIN

Bora ver a sintaxe básica do EXPLAIN:

EXPLAIN sua_query_SQL;

Exemplo de query:

EXPLAIN SELECT * FROM students WHERE age > 20;

Quando você roda esse comando, o PostgreSQL não executa a query. Ele só mostra o plano de execução. É tipo um rascunho antes de construir — bom pra ver o que vai rolar antes de quebrar tudo.

Olha um exemplo de saída:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

Pode parecer assustador, mas relaxa — já já a gente destrincha os principais pedaços.

Entendendo o plano de execução básico

Bora analisar esse resultado aí:

1. Seq Scan on students — isso quer dizer que o PostgreSQL vai escanear a tabela students inteira (scan sequencial). Nem sempre é ruim, mas em tabelas grandes o Seq Scan pode ser lento.

2. cost=0.00..35.00 — é a estimativa de custo pra rodar a operação:

   • Startup Cost: custo inicial da operação (aqui 0.00).
   • Total Cost: custo total até terminar (aqui 35.00).

3. rows=7 — o PostgreSQL acha que o filtro age > 20 vai retornar 7 linhas. Isso é a "cardinalidade". Se você ver estimativas estranhas, pode ser sinal de estatística velha na tabela.

4. width=37 — tamanho médio de uma linha, em bytes.

5. Filter: (age > 20) — diz que o PostgreSQL vai aplicar esse filtro, checando cada linha.

Então, a saída do EXPLAIN te dá uma ideia das estratégias e suposições do PostgreSQL. Dá pra usar isso pra otimizar suas queries.

Opções do comando EXPLAIN

O básico do EXPLAIN já ajuda, mas você pode turbinar ele com essas opções:

ANALYZE

Com essa opção, o PostgreSQL não só mostra o plano, mas executa a query e traz dados reais. Exemplo:

EXPLAIN ANALYZE SELECT * FROM students WHERE age > 20;

Assim dá pra comparar o que o PostgreSQL achou com o que rolou de verdade e ver se bate.

VERBOSE

Mostra mais detalhes, bom pra quem quer analisar a fundo. Exemplo:

EXPLAIN VERBOSE SELECT * FROM students WHERE age > 20;

BUFFERS

Mostra o uso de buffers de memória na execução. Usa junto com ANALYZE:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM students WHERE age > 20;

COSTS

Se quiser esconder ou mostrar info de custo (cost), usa essa opção:

EXPLAIN (COSTS OFF) SELECT * FROM students WHERE age > 20;

FORMAT

A saída do plano pode ser em outros formatos, tipo JSON ou XML. Exemplo:

EXPLAIN (FORMAT JSON) SELECT * FROM students WHERE age > 20;

Exemplo de uso do EXPLAIN

Pensa num banco chamado university com a tabela students. Você quer achar todos os estudantes com mais de 20 anos:

EXPLAIN SELECT * FROM students WHERE age > 20;

A saída pode ser assim:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

Como já falei, isso é um scan sequencial (Seq Scan), que pode ser ruim pra tabelas grandes.

Agora bora criar um índice na coluna age e ver se muda o plano:

CREATE INDEX age_index ON students(age);

EXPLAIN SELECT * FROM students WHERE age > 20;

Saída:

Index Scan using age_index on students  (cost=0.15..4.23 rows=7 width=37)
  Index Cond: (age > 20)

Agora o PostgreSQL usa um index scan (Index Scan), que geralmente é bem mais rápido, principalmente em tabelas grandes.

Perguntas e erros comuns

Por que minha query tá lenta mesmo com índice?

Pode ser que a query retorna muita linha, aí usar índice não compensa. Ou o índice tá ruim ou desatualizado.

E se a saída do EXPLAIN for difícil de entender?

Começa com queries simples e vai estudando cada nó do plano, um de cada vez.

Como saber se a estatística da tabela tá velha?

Roda o comando ANALYZE students

Quando usar EXPLAIN sem ANALYZE?

Quando você só quer ver o plano sem executar de verdade (tipo pra queries que mudam dados).