PostgreSQL além do básico: índices, EXPLAIN e JSONB na prática

Saber SELECT, JOIN e GROUP BY coloca qualquer desenvolvedor em operação. O que separa o uso básico do avançado de PostgreSQL é entender como o banco executa suas queries — e como convencê-lo a executá-las melhor. Este artigo cobre os três temas que mais impactam performance em produção: leitura de planos com EXPLAIN ANALYZE, escolha do tipo de índice correto e uso responsável de JSONB.

EXPLAIN ANALYZE: lendo o que o banco realmente fez

EXPLAIN mostra o plano que o otimizador escolheu; EXPLAIN ANALYZE executa a query de verdade e compara estimativa com realidade. Os três nós de scan que você precisa reconhecer:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email FROM usuarios WHERE email = 'ana@exemplo.com';

-- Antes do índice:
-- Seq Scan on usuarios  (cost=0.00..18334.00 rows=1)
--   Filter: (email = 'ana@exemplo.com')
--   Execution Time: 142.318 ms

CREATE INDEX idx_usuarios_email ON usuarios (email);

-- Depois do índice:
-- Index Scan using idx_usuarios_email on usuarios  (cost=0.43..8.45 rows=1)
--   Index Cond: (email = 'ana@exemplo.com')
--   Execution Time: 0.061 ms

Tipos de índice: B-tree não é o único

O B-tree é o índice padrão e atende igualdade e ranges em dados ordenáveis. Mas PostgreSQL oferece estruturas especializadas que mudam ordens de magnitude em casos específicos:

-- Índice GIN para busca dentro de coluna JSONB
CREATE INDEX idx_eventos_payload ON eventos USING GIN (payload);

-- Agora esta query usa o índice:
SELECT * FROM eventos WHERE payload @> '{"tipo": "pagamento"}';

-- Índice parcial: só indexa o que interessa (menor e mais rápido)
CREATE INDEX idx_pedidos_abertos ON pedidos (created_at)
WHERE status = 'aberto';

-- Índice por expressão: acelera busca case-insensitive
CREATE INDEX idx_usuarios_email_lower ON usuarios (lower(email));
SELECT * FROM usuarios WHERE lower(email) = 'ana@exemplo.com';

JSONB sem destruir a performance

JSONB é legítimo para dados genuinamente semiestruturados: payloads de webhook, preferências de usuário, metadados variáveis por tenant. O problema é o anti-padrão de usar JSONB para escapar de modelagem — campos que toda linha tem e que aparecem em WHEREs frequentes merecem colunas de verdade, com tipo, NOT NULL e índice B-tree.

• Use JSONB (não JSON): o formato binário indexável e sem duplicatas de chave.
• Busca por chaves arbitrárias: índice GIN na coluna inteira.
• Busca sempre pelo mesmo caminho: índice B-tree por expressão, ex. (payload->>'tipo').
• Validação de estrutura: aplique CHECK com jsonb_typeof ou valide na aplicação; o banco não impõe schema dentro do JSONB.

-- Índice de expressão para o caminho mais consultado
CREATE INDEX idx_eventos_tipo ON eventos ((payload->>'tipo'));

-- Extração de campos: -> retorna jsonb, ->> retorna text
SELECT payload->>'tipo'   AS tipo,
       payload->'dados'->>'valor' AS valor
FROM eventos
WHERE payload->>'tipo' = 'pagamento';

Manutenção: VACUUM, estatísticas e índices mortos

PostgreSQL usa MVCC: updates criam novas versões das linhas e as antigas ficam como 'dead tuples' até o autovacuum limpar. Em tabelas com muito update, monitore bloat e ajuste autovacuum_vacuum_scale_factor. Estatísticas desatualizadas fazem o otimizador escolher planos ruins — ANALYZE na tabela resolve. E índices que nenhuma query usa continuam custando escrita: consulte pg_stat_user_indexes para encontrá-los.

-- Índices que nunca foram usados desde o último reset de estatísticas
SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS tamanho
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Conclusão

Performance em PostgreSQL é um loop de três passos: medir com EXPLAIN ANALYZE, escolher a estrutura de índice adequada ao padrão de acesso e manter estatísticas e vacuum saudáveis. JSONB é uma ferramenta poderosa quando usada para dados realmente variáveis — não como substituto de modelagem. Domine esses três pilares e a grande maioria dos problemas de lentidão deixa de ser mistério.

Perguntas frequentes

Fontes consultadas

• PostgreSQL Docs — EXPLAIN
• PostgreSQL Docs — Index Types
• PostgreSQL Docs — JSON Types
• use The Index, Luke — guia de indexação