PostgreSQL lento: como identificar e otimizar queries

# Adicionar ao postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

# Reiniciar o PostgreSQL (necessário para shared_preload_libraries)
docker compose restart postgres

# Ativar a extensão no banco
docker compose exec postgres psql -U postgres -d meu_banco \
  -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

# Ver as 10 queries mais lentas (por tempo total)
docker compose exec postgres psql -U postgres -d meu_banco -c "
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;"

-- Analisar uma query específica
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.email, COUNT(o.id) as total_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.email
ORDER BY total_orders DESC;

-- O que procurar no output:
-- "Seq Scan" em tabela grande = candidato a índice
-- "rows=10000" vs "actual rows=1" = estatísticas desatualizadas (ANALYZE)
-- "Hash Join" com buffers altos = considere índice na coluna de join
-- Tempo > 100ms em nó interno = gargalo identificado

-- Ver tabelas com Seq Scans frequentes
SELECT
  schemaname, tablename,
  seq_scan, seq_tup_read,
  idx_scan, idx_tup_fetch,
  round(seq_scan::numeric / NULLIF(seq_scan + idx_scan, 0) * 100, 1) AS seq_pct
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_tup_read DESC
LIMIT 10;

-- Criar índice sem bloquear (CONCURRENTLY)
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at);

-- Índice parcial (só indexa linhas relevantes — menor, mais rápido)
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders(created_at)
  WHERE status = 'pending';

-- Índice composto (para queries com múltiplos filtros)
CREATE INDEX CONCURRENTLY idx_orders_user_status
  ON orders(user_id, status);

-- Sinal de N+1: mesma query parametrizada com milhares de calls
-- Ex: SELECT * FROM users WHERE id = $1 com 5000 calls em 1 minuto

-- Solução: reescrever com JOIN ou IN
-- N+1 (ruim):
-- SELECT * FROM posts WHERE user_id = 1;
-- SELECT * FROM posts WHERE user_id = 2;
-- (repete N vezes)

-- Com JOIN (correto):
SELECT u.email, p.title, p.created_at
FROM users u
JOIN posts p ON p.user_id = u.id
WHERE u.id = ANY($1::int[]);   -- um array de IDs

-- Verificar índices existentes na tabela
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'posts';

-- Remover índices duplicados ou não utilizados
SELECT indexrelid::regclass AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Atualizar estatísticas de todas as tabelas
ANALYZE VERBOSE;

-- Resetar pg_stat_statements (zera contadores)
SELECT pg_stat_statements_reset();

-- Monitorar queries ativas em tempo real
SELECT pid, now() - query_start AS duracao, state, left(query, 100) AS query
FROM pg_stat_activity
WHERE state != 'idle' AND query_start IS NOT NULL
ORDER BY duracao DESC;

-- Matar query travada (substituir PID)
SELECT pg_terminate_backend(12345);