PostgreSQL: EXPLAIN ANALYZE e otimização de queries

-- Sintaxe básica:
EXPLAIN ANALYZE SELECT * FROM pedidos WHERE usuario_id = 123;

-- Com mais detalhes:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
  SELECT p.*, u.nome
  FROM pedidos p
  JOIN usuarios u ON u.id = p.usuario_id
  WHERE p.criado_em > NOW() - INTERVAL '30 days'
  ORDER BY p.criado_em DESC
  LIMIT 20;

-- Resultado a analisar:
-- Seq Scan       → varredura completa da tabela (ruim para tabelas grandes)
-- Index Scan     → usando índice (bom)
-- Index Only Scan → usando apenas o índice, sem ir na tabela (ótimo)
-- Nested Loop    → JOIN por loop (ruim para muitas linhas)
-- Hash Join      → JOIN por hash (bom para tabelas médias)
-- Merge Join     → JOIN por merge (bom para dados ordenados)

-- "rows=X" vs "actual rows=Y" — se muito diferente: ANALYZE a tabela:
ANALYZE pedidos;

-- Identificar queries lentas sem saber qual é o gargalo:
SELECT query, mean_exec_time, calls, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Índice simples (mais comum):
CREATE INDEX idx_pedidos_usuario_id ON pedidos(usuario_id);

-- Índice composto (ordem importa — coluna mais seletiva primeiro):
CREATE INDEX idx_pedidos_status_criado ON pedidos(status, criado_em DESC);
-- Usado em: WHERE status = 'pago' ORDER BY criado_em DESC

-- Índice parcial (apenas subconjunto de linhas — menor e mais rápido):
CREATE INDEX idx_pedidos_pendentes ON pedidos(usuario_id)
  WHERE status = 'pendente';
-- Usado em: WHERE status = 'pendente' AND usuario_id = 123

-- Índice para busca por texto (LIKE):
CREATE INDEX idx_produtos_nome ON produtos USING gin(nome gin_trgm_ops);
-- Requer: CREATE EXTENSION pg_trgm;
-- Usado em: WHERE nome LIKE '%camisa%'

-- Índice para JSON (JSONB):
CREATE INDEX idx_eventos_tipo ON eventos USING gin(payload);
CREATE INDEX idx_eventos_tipo_spec ON eventos((payload->>'tipo'));

-- Índice covering (inclui colunas extras para Index Only Scan):
CREATE INDEX idx_pedidos_usuario_covering
  ON pedidos(usuario_id) INCLUDE (total, status, criado_em);

-- Ver índices e seu uso:
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;  -- índices com zero scans podem ser removidos

-- Antipadrão: subquery correlacionada (executa N vezes):
SELECT u.nome,
  (SELECT COUNT(*) FROM pedidos p WHERE p.usuario_id = u.id) AS total_pedidos
FROM usuarios u;

-- Correto: JOIN com aggregação:
SELECT u.nome, COUNT(p.id) AS total_pedidos
FROM usuarios u
LEFT JOIN pedidos p ON p.usuario_id = u.id
GROUP BY u.id, u.nome;

-- CTE para reutilizar resultado (PostgreSQL 12+ materializa automaticamente):
WITH usuarios_ativos AS (
  SELECT id FROM usuarios
  WHERE ultimo_acesso > NOW() - INTERVAL '30 days'
),
pedidos_recentes AS (
  SELECT usuario_id, SUM(total) AS receita
  FROM pedidos
  WHERE criado_em > NOW() - INTERVAL '30 days'
  GROUP BY usuario_id
)
SELECT u.nome, COALESCE(pr.receita, 0) AS receita_30d
FROM usuarios_ativos ua
JOIN usuarios u ON u.id = ua.id
LEFT JOIN pedidos_recentes pr ON pr.usuario_id = ua.id
ORDER BY receita_30d DESC;

-- Forçar materialização de CTE (útil quando planner faz escolha ruim):
WITH minha_cte AS MATERIALIZED (
  SELECT ... FROM tabela_grande WHERE condicao_seletiva
)
SELECT * FROM minha_cte WHERE ...

-- Problema N+1: uma query por registro (1 + N queries)
-- Antipadrão em Node.js com Prisma/TypeORM:
const usuarios = await db.usuario.findMany()
for (const usuario of usuarios) {
  // Executa 1 query para cada usuário!
  const pedidos = await db.pedido.findMany({ where: { usuarioId: usuario.id } })
}

-- Correto: eager loading (1 query com JOIN):
const usuarios = await db.usuario.findMany({
  include: { pedidos: true }  // Prisma: gera JOIN automático
})

-- SQL equivalente:
SELECT u.*, p.*
FROM usuarios u
LEFT JOIN pedidos p ON p.usuario_id = u.id;

-- Detectar N+1 com pg_stat_statements:
SELECT query, calls
FROM pg_stat_statements
WHERE query LIKE '%WHERE usuario_id%'
ORDER BY calls DESC
LIMIT 5;
-- Se mesma query aparece com calls muito alto em curto período: N+1

-- Habilitar pg_stat_statements:
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
-- Depois: CREATE EXTENSION pg_stat_statements;

-- Verificar tabelas com muitas linhas mortas (necessitam VACUUM):
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Forçar VACUUM em tabela específica:
VACUUM ANALYZE pedidos;

-- VACUUM FULL (reorganiza a tabela — bloqueia escritas, use com cuidado):
VACUUM FULL pedidos;  -- apenas em manutenção planejada

-- Verificar bloat de índices:
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) AS tamanho
FROM pg_indexes
WHERE tablename = 'pedidos'
ORDER BY pg_relation_size(indexname::regclass) DESC;

-- Recriar índice sem bloquear (PostgreSQL 12+):
REINDEX INDEX CONCURRENTLY idx_pedidos_usuario_id;

-- Ajustar autovacuum para tabelas de alta escrita:
ALTER TABLE pedidos SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- vacuumar ao atingir 1% de linhas mortas
  autovacuum_analyze_scale_factor = 0.005
);