Lendo EXPLAIN ANALYZE no PostgreSQL para Iniciantes

1. Visão Geral

EXPLAIN ANALYZE é a ferramenta mais importante para diagnosticar queries lentas no PostgreSQL. Este artigo ajuda você a entender cada parte do output: tipo de node, cost, rows, actual time, e saber quando uma query tem problemas. Após ler, você terá confiança para ler planos de execução e saber se deve adicionar um index ou refatorar a query.

2. EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN apenas mostra o plano estimado (estimated plan) baseado em estatísticas, sem executar a query. EXPLAIN ANALYZE executa de verdade a query e mede o tempo real.

-- Apenas visualiza o plano estimado, sem executar a query
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Executa de verdade e mede o tempo real
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Atenção: EXPLAIN ANALYZE executa a query de verdade, então se for UPDATE ou DELETE, os dados serão modificados. Use dentro de uma transação e faça ROLLBACK se quiser apenas testar:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'SHIPPED' WHERE id = 1;
ROLLBACK;

3. Estrutura de um node no plano

Cada linha no output é um node — um passo no processamento de dados. Exemplo com a tabela orders:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=8 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 99992
Planning Time: 0.120 ms
Execution Time: 12.465 ms

Explicação de cada parte:

• Seq Scan on orders: tipo de node, neste caso é uma varredura sequencial de toda a tabela.
• cost=0.00..1834.00: custo estimado (custo inicial .. custo total), em unidades relativas, não em ms.
• rows=10: número estimado de linhas que o planejador acha que serão retornadas.
• width=72: tamanho médio de cada linha (bytes).
• actual time=0.015..12.430: tempo real (ms) — desde a primeira linha ser retornada até o node completar integralmente.
• rows=8 loops=1: número de linhas reais retornadas, e quantas vezes este node foi executado (loops).
• Rows Removed by Filter: número de linhas que foram lidas mas não corresponderam ao filtro — um número grande aqui é sinal de falta de index.

4. Comparando Seq Scan e Index Scan

4.1. Quando não há index

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=8 loops=1)
  Filter: (customer_id = 42)
Execution Time: 12.465 ms

PostgreSQL precisa ler a tabela inteira e depois filtrar cada linha. Com tabelas grandes, esta é a causa principal de lentidão.

4.2. Depois de adicionar um index

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer_id on orders
  (cost=0.29..8.31 rows=10 width=72)
  (actual time=0.018..0.025 rows=8 loops=1)
  Index Cond: (customer_id = 42)
Execution Time: 0.045 ms

Comparação: o cost caiu de 1834 para 8.31, e o execution time caiu de 12.465 ms para 0.045 ms. Este é um exemplo clássico mostrando como um index permite que PostgreSQL pule direto para as linhas necessárias, sem precisar varrer toda a tabela.

Atenção: PostgreSQL nem sempre usa um index mesmo que ele exista. Se a query retorna a maior parte da tabela (por exemplo, > 20-30% das linhas), o planejador geralmente escolhe Seq Scan porque é mais eficiente do que pular aleatoriamente pelo index e depois voltar à tabela (random I/O).

5. Lendo planos com JOIN

Quando a query tem um join, o plano terá múltiplos nodes aninhados. O node filho é executado primeiro, e seu resultado é passado ao node pai.

EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'VN';

Hash Join  (cost=45.50..520.75 rows=200 width=40)
           (actual time=1.200..8.900 rows=195 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o
        (cost=0.00..420.00 rows=10000 width=12)
        (actual time=0.010..3.500 rows=10000 loops=1)
  ->  Hash  (cost=40.00..40.00 rows=440 width=36)
            (actual time=1.100..1.100 rows=440 loops=1)
        ->  Seq Scan on customers c
              (cost=0.00..40.00 rows=440 width=36)
              (actual time=0.005..0.700 rows=440 loops=1)
              Filter: (country = 'VN'::text)
Execution Time: 9.050 ms

Como ler: Hash Join é o node mais externo (raiz), ele recebe dados de dois nodes filhos (Seq Scan on orders e Hash contendo Seq Scan on customers). PostgreSQL constrói uma hash table a partir da tabela customers filtrada, depois varre orders e faz match na hash table. O tempo actual time do node pai já inclui o tempo dos nodes filhos dentro dele.

6. Sinais de alerta a observar

6.1. Estimativa muito diferente da realidade

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=9500 loops=1)

O planejador estimou rows=10 mas na realidade retornou 9500. Uma discrepância tão grande geralmente significa estatísticas desatualizadas. Corrija com:

ANALYZE orders;

Este comando atualiza as estatísticas de distribuição de dados para que o planejador faça estimativas mais precisas.

6.2. Loops anormalmente grande (Nested Loop)

Nested Loop  (cost=0.43..5000.00 rows=1 width=40)
             (actual time=0.020..450.000 rows=1 loops=1)
  ->  Seq Scan on customers c (actual time=0.010..0.015 rows=1 loops=1)
  ->  Index Scan on orders o
        (actual time=0.002..0.450 rows=1 loops=1000)

loops=1000 significa que o node Index Scan on orders foi chamado 1000 vezes (correspondendo a 1000 linhas do node externo). Se cada chamada custar tempo significativo, o custo total será muito alto mesmo que o actual time do node isolado pareça pequeno. Multiplique actual time por loops para estimar o tempo real total do node.

6.3. Use BUFFERS para ver I/O

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

Index Scan using idx_orders_customer_id on orders
  (cost=0.29..8.31 rows=10 width=72)
  (actual time=0.018..0.025 rows=8 loops=1)
  Index Cond: (customer_id = 42)
  Buffers: shared hit=4
Execution Time: 0.045 ms

shared hit=4 significa que 4 blocos foram lidos do cache (memória), não do disco. Se você vir shared read grande, significa que dados precisaram ser lidos do disco — sinal de cache miss, possivelmente porque shared_buffers é muito pequeno ou a query acessa muito dado “frio”.

7. Usando EXPLAIN em código Java/Spring

Quando debugando performance em uma aplicação Spring Boot, você pode fazer log do plano diretamente via JdbcTemplate:

@Service
public class QueryDiagnosticsService {

    private final JdbcTemplate jdbcTemplate;

    public QueryDiagnosticsService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<String> explainAnalyze(String sql) {
        String query = "EXPLAIN (ANALYZE, FORMAT TEXT) " + sql;
        return jdbcTemplate.queryForList(query, String.class);
    }
}

explainAnalyze recebe a SQL original, a envolve com EXPLAIN (ANALYZE, FORMAT TEXT) e retorna cada linha do plano como String. Use em ambiente dev/staging para fazer log do plano quando detectar uma query lenta.

@Test
void explainAnalyze_shouldReturnPlanWithExecutionTime() {
    QueryDiagnosticsService service = new QueryDiagnosticsService(jdbcTemplate);

    List<String> plan = service.explainAnalyze(
        "SELECT * FROM orders WHERE customer_id = 42"
    );

    assertThat(plan)
        .isNotEmpty()
        .anyMatch(line -> line.contains("Execution Time"));
}

Este teste não faz assert de números específicos (já que o tempo varia de máquina para máquina), apenas confirma que o plano retornado contém a linha Execution Time — suficiente para verificar que a query executou com sucesso e retornou um plano válido.

Atenção: Não execute EXPLAIN ANALYZE em produção com queries INSERT/UPDATE/DELETE sem envolver a transação com ROLLBACK, pois ela será executada de verdade.

8. Quando NÃO usar EXPLAIN ANALYZE

• Query já é rápida e estável (alguns ms, poucos dados) — não precisa otimizar mais, gasta esforço sem ganho.
• Você está na fase inicial de design do schema e ainda não tem dados reais suficientes para as estatísticas refletirem a realidade (com poucos dados, Seq Scan sempre vence Index Scan).
• Query roda em uma tabela temporária (TEMP TABLE) pequena em uma única ocasião — o overhead de análise não compensa o ganho.

9. Conclusão

EXPLAIN ANALYZE mostra a diferença entre o que PostgreSQL acha que vai acontecer (cost, rows estimados) e o que de fato acontece (actual time, rows reais). Ao ler um plano, preste atenção na ordem de execução (de dentro para fora), no tipo de node (Seq Scan ou Index Scan), e especialmente na discrepância entre estimate e actual — essa é a chave para saber se a query precisa de um index, precisa rodar ANALYZE na tabela novamente, ou precisa ser reescrita.