EXPLAIN ANALYZE é o raio-X de uma query PostgreSQL. Saber ler o output — identificar Seq Scans desnecessários, JOINs com estimativas ruins, sorts evitáveis — transforma debug de performance de tentativa e erro em diagnóstico preciso.

EXPLAIN vs EXPLAIN ANALYZE

-- EXPLAIN — só estima, NÃO executa
EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 42;
-- Seq Scan on pedidos  (cost=0.00..850.00 rows=12 width=48)
--   Filter: (cliente_id = 42)

-- EXPLAIN ANALYZE — executa e mede tempos reais
EXPLAIN ANALYZE SELECT * FROM pedidos WHERE cliente_id = 42;
-- Seq Scan on pedidos  (cost=0.00..850.00 rows=12 width=48)
--                      (actual time=0.015..8.234 rows=156 loops=1)
--   Filter: (cliente_id = 42)
--   Rows Removed by Filter: 9844
-- Planning Time: 0.3 ms
-- Execution Time: 8.5 ms

-- ⚠️ EXPLAIN ANALYZE EXECUTA a query — cuidado com INSERT/UPDATE/DELETE!
-- Para mutations: envolver em transação e ROLLBACK
BEGIN;
EXPLAIN ANALYZE DELETE FROM pedidos WHERE status = 'cancelado';
ROLLBACK;

-- Opções completas para diagnóstico:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT p.id, c.nome, SUM(ip.preco * ip.quantidade) AS total
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id
JOIN itens_pedido ip ON ip.pedido_id = p.id
WHERE p.criado_em >= '2024-01-01'
GROUP BY p.id, c.nome;

Lendo o output: nós e custos

-- Exemplo de output anotado:
-- Hash Join  (cost=125.00..890.00 rows=1250 width=60)
--            (actual time=5.2..45.8 rows=1180 loops=1)
--   Hash Cond: (p.cliente_id = c.id)
--   ->  Seq Scan on pedidos p  (cost=0.00..750.00 rows=10000 width=32)
--                               (actual time=0.01..12.3 rows=10000 loops=1)
--   ->  Hash  (cost=75.00..75.00 rows=4000 width=28)
--             (actual time=2.1..2.1 rows=4000 loops=1)
--         ->  Seq Scan on clientes c  (cost=0.00..75.00 rows=4000 width=28)
--                                     (actual time=0.01..1.2 rows=4000 loops=1)
-- Planning Time: 1.2 ms
-- Execution Time: 46.5 ms

-- Interpretação das colunas:
-- cost=startup..total: unidades de "custo I/O estimado"
-- rows: número de linhas estimado
-- width: largura média em bytes por linha
-- actual time=startup..total: milissegundos reais (min..max se loops>1)
-- actual rows: linhas reais retornadas
-- loops: quantas vezes o nó foi executado (nested loop pode ser 1000x)

-- BUFFERS mostra:
-- shared hit: leituras do shared_buffers (cache em memória) ← bom
-- shared read: leituras do disco ← ruim se muito alto
-- shared written: blocos sujos escritos

Scan types e quando cada um aparece

Scan Type	Quando aparece	Custo relativo
Seq Scan	Sem índice, ou muitas linhas retornadas (>10-20% da tabela)	Baixo por linha, alto total
Index Scan	Índice existe, poucas linhas, random_page_cost baixo	Alto por linha, baixo total
Index Only Scan	Covering index — não precisa acessar heap	Mais eficiente que Index Scan
Bitmap Index Scan	Índice existe mas retorna muitas linhas — agrupa I/Os	Intermediário
Bitmap Heap Scan	Após Bitmap Index Scan — acessa heap por bloco	Melhor que Index Scan para médias quantidades

Scan TypeSeq Scan

Quando apareceSem índice, ou muitas linhas retornadas (>10-20% da tabela)

Custo relativoBaixo por linha, alto total

Scan TypeIndex Scan

Quando apareceÍndice existe, poucas linhas, random_page_cost baixo

Custo relativoAlto por linha, baixo total

Scan TypeIndex Only Scan

Quando apareceCovering index — não precisa acessar heap

Custo relativoMais eficiente que Index Scan

Scan TypeBitmap Index Scan

Quando apareceÍndice existe mas retorna muitas linhas — agrupa I/Os

Custo relativoIntermediário

Scan TypeBitmap Heap Scan

Quando apareceApós Bitmap Index Scan — acessa heap por bloco

Custo relativoMelhor que Index Scan para médias quantidades

-- Identificar queries lentas automaticamente:
-- postgresql.conf:
-- log_min_duration_statement = 1000  (loga queries > 1s)
-- auto_explain.log_min_duration = 500  (loga planos de queries > 500ms)

-- Encontrar tabelas sem autovacuum recente (estatísticas desatualizadas):
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE last_autoanalyze < NOW() - INTERVAL '1 day'
   OR last_autoanalyze IS NULL
ORDER BY n_dead_tup DESC;

-- Forçar atualização de estatísticas:
ANALYZE tabela;      -- atualiza estatísticas da tabela
VACUUM ANALYZE;      -- vacuum + analyze em todo o banco

-- pg_stat_statements — queries mais lentas:
SELECT
    query,
    calls,
    total_exec_time / calls AS avg_ms,
    rows / calls AS avg_rows
FROM pg_stat_statements
WHERE calls > 100
ORDER BY avg_ms DESC
LIMIT 20;

✅

Workflow de otimização: (1) identifique a query lenta com pg_stat_statements. (2) rode EXPLAIN (ANALYZE, BUFFERS). (3) procure: Seq Scans em tabelas grandes, loops muito altos em Nested Loop, estimativas muito diferentes dos reais. (4) crie índices ou ANALYZE para estatísticas. (5) verifique melhora. Não otimize sem medir — o planner frequentemente está certo.

💡

Próximo: Transações e isolation levels — ACID de verdade: o que dirty read, phantom read e serialization anomaly significam na prática.

EXPLAIN vs EXPLAIN ANALYZE

-- EXPLAIN — só estima, NÃO executa
EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 42;
-- Seq Scan on pedidos  (cost=0.00..850.00 rows=12 width=48)
--   Filter: (cliente_id = 42)

-- EXPLAIN ANALYZE — executa e mede tempos reais
EXPLAIN ANALYZE SELECT * FROM pedidos WHERE cliente_id = 42;
-- Seq Scan on pedidos  (cost=0.00..850.00 rows=12 width=48)
--                      (actual time=0.015..8.234 rows=156 loops=1)
--   Filter: (cliente_id = 42)
--   Rows Removed by Filter: 9844
-- Planning Time: 0.3 ms
-- Execution Time: 8.5 ms

-- ⚠️ EXPLAIN ANALYZE EXECUTA a query — cuidado com INSERT/UPDATE/DELETE!
-- Para mutations: envolver em transação e ROLLBACK
BEGIN;
EXPLAIN ANALYZE DELETE FROM pedidos WHERE status = 'cancelado';
ROLLBACK;

-- Opções completas para diagnóstico:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT p.id, c.nome, SUM(ip.preco * ip.quantidade) AS total
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id
JOIN itens_pedido ip ON ip.pedido_id = p.id
WHERE p.criado_em >= '2024-01-01'
GROUP BY p.id, c.nome;

Lendo o output: nós e custos

-- Exemplo de output anotado:
-- Hash Join  (cost=125.00..890.00 rows=1250 width=60)
--            (actual time=5.2..45.8 rows=1180 loops=1)
--   Hash Cond: (p.cliente_id = c.id)
--   ->  Seq Scan on pedidos p  (cost=0.00..750.00 rows=10000 width=32)
--                               (actual time=0.01..12.3 rows=10000 loops=1)
--   ->  Hash  (cost=75.00..75.00 rows=4000 width=28)
--             (actual time=2.1..2.1 rows=4000 loops=1)
--         ->  Seq Scan on clientes c  (cost=0.00..75.00 rows=4000 width=28)
--                                     (actual time=0.01..1.2 rows=4000 loops=1)
-- Planning Time: 1.2 ms
-- Execution Time: 46.5 ms

-- Interpretação das colunas:
-- cost=startup..total: unidades de "custo I/O estimado"
-- rows: número de linhas estimado
-- width: largura média em bytes por linha
-- actual time=startup..total: milissegundos reais (min..max se loops>1)
-- actual rows: linhas reais retornadas
-- loops: quantas vezes o nó foi executado (nested loop pode ser 1000x)

-- BUFFERS mostra:
-- shared hit: leituras do shared_buffers (cache em memória) ← bom
-- shared read: leituras do disco ← ruim se muito alto
-- shared written: blocos sujos escritos

Scan types e quando cada um aparece

Scan Type	Quando aparece	Custo relativo
Seq Scan	Sem índice, ou muitas linhas retornadas (>10-20% da tabela)	Baixo por linha, alto total
Index Scan	Índice existe, poucas linhas, random_page_cost baixo	Alto por linha, baixo total
Index Only Scan	Covering index — não precisa acessar heap	Mais eficiente que Index Scan
Bitmap Index Scan	Índice existe mas retorna muitas linhas — agrupa I/Os	Intermediário
Bitmap Heap Scan	Após Bitmap Index Scan — acessa heap por bloco	Melhor que Index Scan para médias quantidades

Scan TypeSeq Scan

Quando apareceSem índice, ou muitas linhas retornadas (>10-20% da tabela)

Custo relativoBaixo por linha, alto total

Scan TypeIndex Scan

Quando apareceÍndice existe, poucas linhas, random_page_cost baixo

Custo relativoAlto por linha, baixo total

Scan TypeIndex Only Scan

Quando apareceCovering index — não precisa acessar heap

Custo relativoMais eficiente que Index Scan

Scan TypeBitmap Index Scan

Quando apareceÍndice existe mas retorna muitas linhas — agrupa I/Os

Custo relativoIntermediário

Scan TypeBitmap Heap Scan

Quando apareceApós Bitmap Index Scan — acessa heap por bloco

Custo relativoMelhor que Index Scan para médias quantidades

-- Identificar queries lentas automaticamente:
-- postgresql.conf:
-- log_min_duration_statement = 1000  (loga queries > 1s)
-- auto_explain.log_min_duration = 500  (loga planos de queries > 500ms)

-- Encontrar tabelas sem autovacuum recente (estatísticas desatualizadas):
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE last_autoanalyze < NOW() - INTERVAL '1 day'
   OR last_autoanalyze IS NULL
ORDER BY n_dead_tup DESC;

-- Forçar atualização de estatísticas:
ANALYZE tabela;      -- atualiza estatísticas da tabela
VACUUM ANALYZE;      -- vacuum + analyze em todo o banco

-- pg_stat_statements — queries mais lentas:
SELECT
    query,
    calls,
    total_exec_time / calls AS avg_ms,
    rows / calls AS avg_rows
FROM pg_stat_statements
WHERE calls > 100
ORDER BY avg_ms DESC
LIMIT 20;

EXPLAIN ANALYZE: lendo o plano e otimizando query

EXPLAIN vs EXPLAIN ANALYZE

Lendo o output: nós e custos

Scan types e quando cada um aparece

Discussão

EXPLAIN ANALYZE: lendo o plano e otimizando query

EXPLAIN vs EXPLAIN ANALYZE

Lendo o output: nós e custos

Scan types e quando cada um aparece

Discussão