EXPLAIN ANALYZE: lendo o plano e otimizando query

-- 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;

-- 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

-- 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;