Como usar explain analyze no PostgreSQL para entender planos de execução

1. Introdução ao EXPLAIN e EXPLAIN ANALYZE

O PostgreSQL oferece duas ferramentas fundamentais para entender como suas consultas são executadas: EXPLAIN e EXPLAIN ANALYZE. A diferença crucial entre elas está no fato de que EXPLAIN mostra apenas o plano estimado pelo otimizador, enquanto EXPLAIN ANALYZE executa a consulta de fato e apresenta métricas reais de desempenho.

Um plano de execução é a estratégia que o PostgreSQL escolhe para resolver uma consulta SQL. Ele determina a ordem de acesso às tabelas, quais índices utilizar, como realizar junções e como agregar dados. Compreender esse plano é essencial para identificar gargalos de performance e otimizar consultas lentas.

As variações mais comuns incluem:
- EXPLAIN (ANALYZE, BUFFERS) — adiciona informações sobre uso de cache
- EXPLAIN (ANALYZE, FORMAT JSON) — saída estruturada para ferramentas de visualização
- EXPLAIN (ANALYZE, COSTS OFF) — foco apenas nos tempos reais

2. Lendo a Saída do EXPLAIN ANALYZE

A saída do EXPLAIN ANALYZE possui estrutura hierárquica, onde cada linha representa um nó do plano. Vamos analisar um exemplo prático:

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

Seq Scan on orders  (cost=0.00..1843.00 rows=1 width=36) (actual time=0.023..12.450 rows=5 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 99995
  Buffers: shared hit=100 read=50
Planning Time: 0.085 ms
Execution Time: 12.523 ms

Componentes essenciais:
- cost: estimativa do otimizador (custo inicial..custo total)
- rows: número estimado de linhas retornadas
- actual time: tempo real (início..fim) em milissegundos
- loops: quantas vezes o nó foi executado
- Buffers: páginas lidas do cache (hit) ou do disco (read)
- Rows Removed by Filter: linhas descartadas por condições WHERE

O gargalo principal geralmente está no nó com maior tempo total (actual time * loops). No exemplo acima, o Sequential Scan varreu 100.000 linhas para encontrar apenas 5, indicando ausência de índice adequado.

3. Operadores Comuns e Seus Significados

Sequential Scan vs. Index Scan vs. Index Only Scan

-- Sequential Scan (varredura completa da tabela)
Seq Scan on products  (cost=0.00..2350.00 rows=50000 width=40)

-- Index Scan (acesso via índice, busca na tabela)
Index Scan using idx_products_category on products  (cost=0.29..8.31 rows=1 width=40)
  Index Cond: (category_id = 5)

-- Index Only Scan (apenas índice, sem acessar tabela)
Index Only Scan using idx_products_sku on products  (cost=0.29..4.30 rows=1 width=20)

Join Operators

-- Nested Loop (para poucas linhas na tabela externa)
Nested Loop  (cost=0.30..15.20 rows=10 width=76)
  ->  Seq Scan on customers  (cost=0.00..5.00 rows=1 width=40)
  ->  Index Scan using idx_orders_customer on orders  (cost=0.29..10.20 rows=5 width=36)

-- Hash Join (para tabelas grandes sem índice)
Hash Join  (cost=150.00..450.00 rows=1000 width=76)
  Hash Cond: (c.id = o.customer_id)
  ->  Seq Scan on customers c  (cost=0.00..100.00 rows=5000 width=40)
  ->  Hash  (cost=100.00..100.00 rows=5000 width=36)

-- Merge Join (para dados ordenados)
Merge Join  (cost=200.00..500.00 rows=1000 width=76)
  Merge Cond: (c.id = o.customer_id)

4. Analisando Métricas de Performance

Tempo de execução vs. tempo de planejamento

O Planning Time representa o tempo que o otimizador gastou para gerar o plano. O Execution Time é o tempo real de execução. Se o planning time for muito alto, considere usar prepared statements.

Buffers e cache

Buffers: shared hit=100 read=50 dirtied=5
  • shared hit: páginas encontradas no cache (bom)
  • shared read: páginas lidas do disco (evitar)
  • shared dirtied: páginas modificadas durante a consulta

Uma consulta ideal deve ter predominantemente "shared hit", indicando que os dados estão em memória.

Rows Removed by Filter

Este indicador mostra quantas linhas foram descartadas por condições WHERE. Valores altos em relação ao retorno indicam baixa seletividade do índice ou falta de índice apropriado.

5. Estratégias para Otimizar com Base no Plano

Quando adicionar um índice

-- Plano problemático
Seq Scan on orders  (cost=0.00..1843.00 rows=5 width=36)
  Filter: (status = 'pending' AND created_at > '2024-01-01')

-- Solução: criar índice composto
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

Ajustando consultas

Subqueries podem ser transformadas em JOINs para melhor performance:

-- Subquery ineficiente
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- Reescrita com JOIN
SELECT DISTINCT c.* FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.total > 1000;

Forçando planos alternativos

Em casos de diagnóstico, é possível desabilitar operadores específicos:

-- Forçar uso de Index Scan em vez de Sequential Scan
SET enable_seqscan = OFF;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

6. Casos Práticos de Diagnóstico

Caso 1: Nested Loop ineficiente

Nested Loop  (cost=0.30..5000.00 rows=1000 width=76) (actual time=0.050..150.000 rows=1000 loops=1)
  ->  Seq Scan on customers  (cost=0.00..100.00 rows=5000 width=40) (actual time=0.020..5.000 rows=5000 loops=1)
  ->  Index Scan using idx_orders_customer on orders  (cost=0.29..0.98 rows=1 width=36) (actual time=0.030..0.030 rows=1 loops=5000)

O problema: 5000 loops no Index Scan. Solução: adicionar índice em orders.customer_id e considerar Hash Join.

Caso 2: Hash Join com spill to disk

Hash Join  (cost=500.00..2000.00 rows=50000 width=76) (actual time=100.000..500.000 rows=50000 loops=1)
  Hash Cond: (c.id = o.customer_id)
  ->  Seq Scan on customers  (cost=0.00..100.00 rows=5000 width=40)
  ->  Hash  (cost=300.00..300.00 rows=50000 width=36) (actual time=50.000..50.000 rows=50000 loops=1)
        Buckets: 4096  Batches: 4  Memory Usage: 2048kB

O indicador "Batches: 4" mostra que a hash table não coube na memória e houve escrita em disco. Solução: aumentar work_mem.

Caso 3: Index Scan não sendo utilizado

Seq Scan on products  (cost=0.00..500.00 rows=1000 width=40) (actual time=0.050..50.000 rows=1000 loops=1)
  Filter: (price > 100 AND category = 'eletrônicos')

Verifique se o índice existe e se as estatísticas estão atualizadas:

ANALYZE products;
EXPLAIN ANALYZE SELECT * FROM products WHERE price > 100 AND category = 'eletrônicos';

7. Ferramentas e Boas Práticas para Uso Contínuo

Monitoramento com pg_stat_statements

-- Habilitar extensão
CREATE EXTENSION pg_stat_statements;

-- Consultar queries mais lentas
SELECT query, mean_exec_time, calls, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

auto_explain para logging automático

-- No postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '500ms'  -- loga queries acima de 500ms
auto_explain.log_analyze = on
auto_explain.log_buffers = on

Visualização gráfica

  • pgAdmin: ferramenta nativa com visualização visual de planos
  • explain.dalibo.com: analisador online que coloriza e explica cada nó

Checklist para análise sistemática

  1. Execute EXPLAIN (ANALYZE, BUFFERS)
  2. Identifique o nó com maior tempo total
  3. Verifique Rows Removed by Filter vs. rows retornadas
  4. Analise Buffers: muitos read indicam falta de cache
  5. Confira se índices estão sendo utilizados
  6. Avalie se o tipo de join é adequado ao volume de dados
  7. Verifique estatísticas com ANALYZE se necessário

Referências