EXPLAIN e EXPLAIN ANALYZE: entendendo o query planner

1. O que é o Query Planner e por que ele importa?

1.1. Papel do query planner no SGBD

O query planner (ou otimizador de consultas) é o componente do SGBD responsável por determinar a maneira mais eficiente de executar uma consulta SQL. Quando você envia um comando SELECT, o planner analisa múltiplas estratégias possíveis — como usar índices, fazer varreduras sequenciais ou escolher diferentes métodos de junção — e seleciona aquela com o menor custo estimado.

1.2. Como o planner decide o plano de execução

O planner baseia suas decisões em:
- Estatísticas das tabelas: número de linhas, distribuição de valores, nulls, etc.
- Índices disponíveis: sua estrutura (B-tree, Hash, GiST) e seletividade.
- Custos relativos: operações de CPU, I/O de disco e memória.

Cada operação recebe um custo numérico (medido em unidades arbitrárias), e o planner escolhe o plano com o menor custo total.

1.3. Diferença entre plano estimado e plano real

O plano estimado é uma previsão baseada em estatísticas. Já o plano real é o que efetivamente acontece quando a consulta é executada. A diferença entre eles revela imprecisões nas estatísticas ou escolhas ruins do planner.

2. EXPLAIN: lendo o plano de execução estimado

2.1. Sintaxe básica e saída típica

O comando EXPLAIN mostra o plano estimado sem executar a consulta:

EXPLAIN SELECT * FROM clientes WHERE cidade = 'São Paulo';

Saída típica:

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on clientes  (cost=0.00..35.50 rows=10 width=120)
   Filter: (cidade = 'São Paulo'::text)

2.2. Interpretando os campos principais

  • cost: intervalo de custo estimado (início..fim). A primeira parcela é o custo para obter a primeira linha; a segunda, para obter todas as linhas.
  • rows: número estimado de linhas retornadas.
  • width: tamanho médio estimado de cada linha em bytes.

2.3. Exemplo prático: varredura sequencial vs. varredura por índice

Sem índice:

EXPLAIN SELECT * FROM pedidos WHERE data >= '2024-01-01';
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on pedidos  (cost=0.00..458.00 rows=5000 width=80)
   Filter: (data >= '2024-01-01'::date)

Com índice em data:

EXPLAIN SELECT * FROM pedidos WHERE data >= '2024-01-01';
                               QUERY PLAN
----------------------------------------------------------------------
 Index Scan using idx_pedidos_data on pedidos  (cost=0.29..150.34 rows=5000 width=80)
   Index Cond: (data >= '2024-01-01'::date)

O custo caiu de 458 para 150, e o planner agora usa o índice.

3. EXPLAIN ANALYZE: executando e medindo o plano real

3.1. Diferença fundamental

EXPLAIN ANALYZE executa a consulta de fato e mostra o plano real com métricas de desempenho:

EXPLAIN ANALYZE SELECT * FROM clientes WHERE cidade = 'São Paulo';
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Seq Scan on clientes  (cost=0.00..35.50 rows=10 width=120) (actual time=0.015..0.032 rows=8 loops=1)
   Filter: (cidade = 'São Paulo'::text)
   Rows Removed by Filter: 492
 Planning Time: 0.085 ms
 Execution Time: 0.045 ms

3.2. Informações adicionais

  • actual time: tempo real gasto (primeira linha e total) em milissegundos.
  • rows: número real de linhas retornadas.
  • loops: quantas vezes aquele nó foi executado.
  • Planning Time / Execution Time: tempos totais.

3.3. Cuidados com DML

Em comandos UPDATE, DELETE ou INSERT, o EXPLAIN ANALYZE realmente modifica os dados. Para testes seguros, use:

BEGIN;
EXPLAIN ANALYZE DELETE FROM pedidos WHERE data < '2020-01-01';
ROLLBACK;

4. Nós de operadores mais comuns no plano

4.1. Sequential Scan vs. Index Scan vs. Index Only Scan

  • Seq Scan: varredura completa da tabela. Usada quando a tabela é pequena ou a condição não é seletiva.
  • Index Scan: acessa o índice e depois busca as linhas na tabela (heap).
  • Index Only Scan: todas as colunas necessárias estão no índice; evita acesso à tabela.
EXPLAIN ANALYZE SELECT id, nome FROM clientes WHERE id = 100;
                                   QUERY PLAN
----------------------------------------------------------------------------------------
 Index Only Scan using pk_clientes on clientes  (cost=0.29..8.30 rows=1 width=36)
   Index Cond: (id = 100)
   Heap Fetches: 0

4.2. Nós de junção

  • Nested Loop: para cada linha da primeira tabela, varre a segunda. Eficiente quando uma tabela é pequena.
  • Hash Join: constrói uma hash table de uma tabela e varre a outra. Bom para junções de tamanho médio.
  • Merge Join: ordena ambas as tabelas e as intercala. Ideal quando os dados já estão ordenados.
EXPLAIN ANALYZE SELECT * FROM clientes c JOIN pedidos p ON c.id = p.cliente_id;
                                      QUERY PLAN
----------------------------------------------------------------------------------------
 Hash Join  (cost=12.50..145.00 rows=1000 width=200)
   Hash Cond: (p.cliente_id = c.id)
   -> Seq Scan on pedidos p  (cost=0.00..35.00 rows=1000 width=120)
   -> Hash  (cost=10.00..10.00 rows=500 width=80)
         -> Seq Scan on clientes c  (cost=0.00..10.00 rows=500 width=80)

4.3. Nós de agregação e ordenação

  • Sort: ordena os dados (custo adicional de O(n log n)).
  • Aggregate / GroupAggregate: agrupa e agrega valores.
EXPLAIN ANALYZE SELECT cidade, COUNT(*) FROM clientes GROUP BY cidade;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 HashAggregate  (cost=15.00..17.00 rows=200 width=20)
   Group Key: cidade
   -> Seq Scan on clientes  (cost=0.00..10.00 rows=500 width=12)

5. Identificando gargalos com EXPLAIN ANALYZE

5.1. Localizando nós de alto custo

Procure por nós com actual time elevado ou loops muito altos. Um nó executado 10.000 vezes com tempo individual pequeno pode ser o gargalo.

5.2. Diferença entre rows estimadas e rows reais

Se rows estimado for 100 e rows real for 10.000, as estatísticas estão desatualizadas. Execute ANALYZE tabela; para atualizá-las.

5.3. Filtros que causam varreduras desnecessárias

Um Filter: que remove muitas linhas após a varredura indica que a condição poderia ser melhor aproveitada por um índice.

   Filter: (status = 'ativo')
   Rows Removed by Filter: 5000

6. Estratégias de otimização baseadas no plano

6.1. Criando ou ajustando índices

Se um Seq Scan aparece em uma tabela grande com filtro seletivo, crie um índice:

CREATE INDEX idx_clientes_cidade ON clientes(cidade);

6.2. Reescrevendo queries

Subqueries correlacionadas podem ser convertidas em JOINs para melhor desempenho:

-- Ruim: subquery correlacionada
SELECT * FROM clientes c WHERE (SELECT COUNT(*) FROM pedidos p WHERE p.cliente_id = c.id) > 5;

-- Melhor: JOIN com GROUP BY
SELECT c.* FROM clientes c JOIN pedidos p ON c.id = p.cliente_id GROUP BY c.id HAVING COUNT(*) > 5;

6.3. Forçando planos alternativos

Para testes, é possível desabilitar certos tipos de scan:

SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM clientes WHERE cidade = 'São Paulo';

Use isso apenas para diagnóstico; não em produção.

7. Ferramentas e boas práticas para análise contínua

7.1. Visualizadores de plano

O formato JSON do EXPLAIN permite visualização em ferramentas online:

EXPLAIN (FORMAT JSON) SELECT * FROM clientes WHERE cidade = 'São Paulo';

Ferramentas como explain.depesz.com e tatiyants.com/pev ajudam a interpretar visualmente.

7.2. Monitoramento com pg_stat_statements

A extensão pg_stat_statements armazena estatísticas de todas as queries executadas, permitindo identificar as mais lentas:

SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

7.3. Atualizando estatísticas

Execute ANALYZE regularmente (ou configure o autovacuum) para manter as estatísticas atualizadas e planos precisos.

Referências