Capstone: tuning de workload — query de 30s pra 50ms

-- 1. IDENTIFIQUE (pg_stat_statements)
SELECT query, calls, round(total_exec_time::numeric, 2) AS total_ms,
       round((total_exec_time / calls)::numeric, 2) AS mean_ms,
       rows
FROM pg_stat_statements
WHERE total_exec_time > 10000
ORDER BY total_exec_time DESC LIMIT 20;

-- 2. DIAGNOSTIQUE (EXPLAIN ANALYZE)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(*) FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'BR' AND o.created_at > '2026-01-01'
GROUP BY u.name ORDER BY 2 DESC LIMIT 10;

-- Ler plano: Seq Scan? Rows estimate off 100x? Sort external?

-- 3. HIPÓTESE + AÇÃO
-- Hipótese A: falta índice em (country, created_at)
CREATE INDEX CONCURRENTLY idx_users_country ON users (country);
CREATE INDEX CONCURRENTLY idx_orders_created_user ON orders (created_at, user_id);

-- 4. MEDIR depois
ANALYZE users; ANALYZE orders;
EXPLAIN ANALYZE ...

-- 5. DOCUMENTAR
-- Query: SELECT u.name, COUNT(*) ...
-- Before: 28.5s, Seq Scan users, Nested Loop
-- After: 45ms, Index Scan both sides, Hash Join
-- Change: 2 indexes added, ANALYZE run
-- Impact: 633x speedup in total db time (pg_stat_statements)