Performance Tuning

EXPLAIN — Plano de Execução

-- Ver como o banco executa a query
EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 1;
EXPLAIN ANALYZE SELECT * FROM pedidos WHERE cliente_id = 1;  -- executa de verdade

-- Saída:
-- Seq Scan on pedidos  (cost=0.00..35.50 rows=6 width=44)
--   Filter: (cliente_id = 1)
--   Planning Time: 0.12 ms
--   Execution Time: 0.34 ms

Tipos de Scan

Seq Scan: varredura sequencial (tabela inteira) — lento em tabelas grandes
Index Scan: busca por índice + acesso à tabela
Index Only Scan: índice contém todos os dados necessários (mais rápido)
Bitmap Index Scan: combina múltiplos índices

-- Identificar queries lentas locks
SELECT 
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state,
    wait_event
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC
LIMIT 10;

Índices para Performance

-- Índice composto: coluna de igualdade primeiro, range depois
-- ❌ Ruim: CREATE INDEX ON pedidos (data, cliente_id);
-- ✅ Bom:  CREATE INDEX ON pedidos (cliente_id, data);

-- Índice covering (inclui colunas extras)
CREATE INDEX idx_pedidos_cobertura 
ON pedidos (cliente_id, data_pedido DESC) 
INCLUDE (total, status);
-- Index Only Scan — não precisa acessar a tabela

-- Ver índices não usados
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- nunca usado
ORDER BY tablename;

Query Optimization

-- 1. WHERE vs HAVING (filtre cedo!)
-- ❌ Ruim: filtra depois
SELECT cliente_id, COUNT(*) 
FROM pedidos 
GROUP BY cliente_id 
HAVING cliente_id = 1;

-- ✅ Bom: filtra antes
SELECT cliente_id, COUNT(*) 
FROM pedidos 
WHERE cliente_id = 1
GROUP BY cliente_id;

-- 2. Evitar funções em colunas no WHERE
-- ❌ Ruim: função na coluna (não usa índice)
SELECT * FROM pedidos WHERE DATE(data_pedido) = '2024-06-01';

-- ✅ Bom: range query (usa índice)
SELECT * FROM pedidos 
WHERE data_pedido >= '2024-06-01' AND data_pedido < '2024-06-02';

-- 3. LIKE com % no início não usa índice
-- ❌ Ruim: SELECT * FROM produtos WHERE nome LIKE '%smartphone%';
-- ✅ Bom: SELECT * FROM produtos WHERE nome LIKE 'smartphone%';
-- ✅ Alternativa: full-text search (GIN index)

-- 4. EXPLAIN ANALYZE para encontrar bottlenecks

Bulk Operations

-- Batch INSERT
INSERT INTO logs (data, mensagem) 
SELECT generate_series(
    '2024-01-01'::timestamp,
    '2024-12-31'::timestamp,
    '1 hour'::interval
), 'log entry';

-- COPY (mais rápido que INSERT)
-- Export
COPY pedidos TO '/tmp/pedidos.csv' WITH (FORMAT CSV, HEADER);

-- Import
COPY pedidos_temp FROM '/tmp/pedidos.csv' WITH (FORMAT CSV, HEADER);

-- Desabilitar índices + constraints antes de bulk insert
ALTER TABLE pedidos SET UNLOGGED;  -- PostgreSQL
-- ... bulk insert ...
ALTER TABLE pedidos SET LOGGED;

Partitioning

-- Partition by range (PostgreSQL)
CREATE TABLE pedidos (
    id SERIAL,
    data_pedido DATE NOT NULL,
    total DECIMAL(10,2)
) PARTITION BY RANGE (data_pedido);

CREATE TABLE pedidos_2024_q1
    PARTITION OF pedidos
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE pedidos_2024_q2
    PARTITION OF pedidos
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Partition pruning: banco só escaneia partições relevantes
SELECT * FROM pedidos WHERE data_pedido BETWEEN '2024-02-01' AND '2024-03-01';
-- Só escaneia pedidos_2024_q1

Vacuum e Estatísticas

-- PostgreSQL: VACUUM recupera espaço (apaga tuplas mortas)
VACUUM pedidos;                    -- libera espaço (não bloqueia)
VACUUM FULL pedidos;               -- compacta (bloqueia!)
ANALYZE pedidos;                   -- atualiza estatísticas
VACUUM ANALYZE pedidos;            -- ambos

-- Autovacuum config
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_analyze_threshold;

-- Monitoramento
SELECT schemaname, tablename, n_dead_tup, n_live_tup,
       round(n_dead_tup * 100.0 / GREATEST(n_live_tup + n_dead_tup, 1), 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY dead_pct DESC;

EXPLAIN ANALYZE é a ferramenta #1 para performance. Filtre cedo (WHERE antes de GROUP BY). Evite funções em colunas no WHERE. Índices covering e partition para tabelas grandes. VACUUM/ANALYZE mantém estatísticas atualizadas.