Índices e Performance

Tipos de Índice

B-tree (Padrão)

-- B-tree: ideal para igualdade e range (=, <, >, BETWEEN, LIKE 'prefixo%')
CREATE INDEX idx_clientes_nome ON clientes(nome);
CREATE INDEX idx_pedidos_data ON pedidos(criado_em);
CREATE INDEX idx_clientes_email ON clientes(email);

-- Índice multicoluna (ordem importa!)
CREATE INDEX idx_clientes_nome_email ON clientes(nome, email);
-- Usa: WHERE nome = 'João'
-- Usa: WHERE nome = 'João' AND email LIKE '%@'
-- NÃO usa: WHERE email = '[email protected]' (precisa de nome primeiro)

Hash

-- Hash: apenas igualdade (=)
CREATE INDEX idx_pedidos_hash_id ON pedidos USING HASH (id);
-- Útil quando a coluna tem muitos valores únicos e só consultas de igualdade

GiST

-- GiST: dados geométricos, full-text, range types
CREATE EXTENSION IF NOT EXISTS btree_gist;  -- range types

-- Geometric queries
CREATE INDEX idx_locais_coords ON locais USING GIST (coordenadas);
SELECT * FROM locais
WHERE coordenadas <@ BOX '(0,0),(100,100)';  -- dentro do box

-- Range types (evitar overlap)
CREATE TABLE reservas (
    sala_id INTEGER,
    periodo TSTZRANGE,
    EXCLUDE USING GIST (sala_id WITH =, periodo WITH &&)
);

GIN

-- GIN: arrays, JSONB, full-text
CREATE INDEX idx_produtos_tags ON produtos USING GIN (tags);
SELECT * FROM produtos WHERE tags @> ARRAY['promocao'];

CREATE INDEX idx_produtos_metadata ON produtos USING GIN (metadata);
SELECT * FROM produtos WHERE metadata @> '{"cor": "azul"}';

-- Full-text search
CREATE INDEX idx_artigos_texto ON artigos USING GIN (to_tsvector('portuguese', texto));
SELECT * FROM artigos
WHERE to_tsvector('portuguese', texto) @@ to_tsquery('portuguese', 'banco & dados');

BRIN

-- BRIN: dados naturalmente ordenados (logs, séries temporais)
-- Muito menor que B-tree, ideal para grandes tabelas
CREATE INDEX idx_logs_brin ON logs USING BRIN (criado_em)
WITH (pages_per_range = 32);

-- Consulta por range de data
SELECT * FROM logs WHERE criado_em BETWEEN '2024-01-01' AND '2024-01-07';
-- BRIN escaneia blocos relevantes ao invés de índice completo

Covering Indexes

-- INCLUDE: adiciona colunas sem ocupar espaço na árvore de busca
CREATE INDEX idx_pedidos_cliente_data ON pedidos(cliente_id, criado_em)
INCLUDE (total, status);

-- Query coberta pelo índice (index-only scan)
SELECT total, status FROM pedidos
WHERE cliente_id = 123 AND criado_em > '2024-01-01';

Partial Indexes

-- Índice apenas em dados relevantes
CREATE INDEX idx_pedidos_ativos ON pedidos(criado_em)
WHERE status NOT IN ('cancelado', 'entregue');

CREATE INDEX idx_usuarios_ativos ON usuarios(email)
WHERE ativo = true;

-- Menor que índice completo
-- Útil para consultas frequentes com filtro específico

Expression Indexes

-- Índice em expressão/função
CREATE INDEX idx_clientes_nome_lower ON clientes(LOWER(nome));
SELECT * FROM clientes WHERE LOWER(nome) = 'joão';

CREATE INDEX idx_pedidos_mes ON pedidos(EXTRACT(YEAR FROM criado_em), EXTRACT(MONTH FROM criado_em));
SELECT * FROM pedidos
WHERE EXTRACT(YEAR FROM criado_em) = 2024 AND EXTRACT(MONTH FROM criado_em) = 1;

EXPLAIN

-- Análise de plano de execução
EXPLAIN SELECT * FROM clientes WHERE id = 123;

-- Com estatísticas reais (EXECUTA a query!)
EXPLAIN ANALYZE SELECT * FROM pedidos WHERE cliente_id = 456;

-- Com buffers e formato JSON
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT p.id, c.nome
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
WHERE p.status = 'pendente';

Node Types

-- Seq Scan: varredura sequencial (tabela inteira)
EXPLAIN SELECT * FROM pedidos WHERE status = 'pendente';
-- Otimizar com índice parcial

-- Index Scan: varredura no índice + busca na tabela
EXPLAIN SELECT * FROM pedidos WHERE id = 123;

-- Index Only Scan: índice cobre a consulta (sem acessar tabela)
EXPLAIN SELECT cliente_id, criado_em FROM pedidos
WHERE cliente_id = 456;

-- Bitmap Scan: combina múltiplos índices
EXPLAIN SELECT * FROM pedidos
WHERE cliente_id = 123 AND criado_em > '2024-06-01';

-- Nested Loop: para cada linha externa, busca na interna
EXPLAIN ANALYZE SELECT * FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id
WHERE c.id = 123;

-- Hash Join: constrói hash de uma tabela, varre a outra
EXPLAIN ANALYZE SELECT * FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id;

-- Merge Join: ordena ambas, merge sequential
EXPLAIN ANALYZE SELECT * FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id
ORDER BY c.id;

Query Planner — Configuração

-- Parâmetros que afetam o planejador

-- Custo de leitura sequencial vs random
SHOW random_page_cost;         -- default: 4.0 (HDD), 1.1 (SSD)
-- Reduzir para SSD melhora escolha por Index Scan

-- Tamanho efetivo do cache do sistema
SHOW effective_cache_size;     -- default: 4GB
-- Aumentar se servidor dedicado PostgreSQL

-- Memória para sorting e hash
SHOW work_mem;                 -- default: 4MB (por operação!)
-- Aumentar com cuidado (cada query pode usar várias operações)

SHOW maintenance_work_mem;     -- default: 64MB
-- Usado em VACUUM, CREATE INDEX, ADD FOREIGN KEY

VACUUM

-- Vacuum: recupera espaço de linhas mortas
VACUUM;                          -- apenas marca espaço como reutilizável
VACUUM FULL;                     -- recupera espaço para o SO (bloqueia tabela!)
VACUUM ANALYZE;                  -- vacuum + atualiza estatísticas
ANALYZE;                         -- apenas atualiza estatísticas do planner

-- Verificar bloat de tabelas
SELECT
    schemaname,
    tablename,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / NULLIF(n_live_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Configurar autovacuum por tabela
ALTER TABLE pedidos SET (
    autovacuum_vacuum_scale_factor = 0.01,       -- 1% de linhas mortas
    autovacuum_vacuum_threshold = 1000,           -- mínimo antes de vacuum
    autovacuum_analyze_scale_factor = 0.005,      -- 0.5% para analyze
    autovacuum_vacuum_cost_limit = 1000           -- mais agressivo
);

-- Monitorar autovacuum
SELECT
    relname,
    last_autovacuum,
    last_autoanalyze,
    n_dead_tup
FROM pg_stat_user_tables;

Lab: Análise de Performance com EXPLAIN

-- 03-performance-lab.sql
DROP TABLE IF EXISTS test_perf CASCADE;
CREATE TABLE test_perf AS
SELECT
    generate_series(1, 1000000) AS id,
    md5(random()::TEXT) AS hash,
    random() * 1000 AS valor,
    NOW() - (random() * 365 || ' days')::INTERVAL AS data,
    CASE WHEN random() < 0.5 THEN 'ativo' ELSE 'inativo' END AS status;

-- SEM ÍNDICE
EXPLAIN ANALYZE SELECT * FROM test_perf WHERE id = 500000;
EXPLAIN ANALYZE SELECT * FROM test_perf WHERE valor BETWEEN 100 AND 200;
EXPLAIN ANALYZE SELECT * FROM test_perf WHERE status = 'ativo';

-- COM ÍNDICE B-tree
CREATE INDEX idx_perf_id ON test_perf(id);
CREATE INDEX idx_perf_valor ON test_perf(valor);
CREATE INDEX idx_perf_status ON test_perf(status) WHERE status = 'ativo';
EXPLAIN ANALYZE SELECT * FROM test_perf WHERE id = 500000;
EXPLAIN ANALYZE SELECT * FROM test_perf WHERE valor BETWEEN 100 AND 200;
EXPLAIN ANALYZE SELECT * FROM test_perf WHERE status = 'ativo';

-- COM ÍNDICE BRIN (para dados ordenados)
CREATE INDEX idx_perf_data_brin ON test_perf USING BRIN(data);
EXPLAIN ANALYZE SELECT * FROM test_perf WHERE data > NOW() - INTERVAL '30 days';

-- VACUUM e estatísticas
VACUUM ANALYZE test_perf;
SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'test_perf';

-- Ver tamanho do índice
SELECT
    indexrelid::regclass AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'test_perf';

Escolha o tipo de índice certo: B-tree para range/igualdade, GIN para JSONB/arrays, GiST para geometria/range, BRIN para logs. Partial indexes reduzem tamanho. Covering indexes (INCLUDE) habilitam index-only scans. EXPLAIN ANALYZE mostra o plano real.