Ajuste de SQL (PostgreSQL primeiro)

O que Ajuste de SQL (PostgreSQL primeiro) faz?

Meça antes de mudar qualquer coisa. Uma alteração de ajuste sem um plano de antes/depois e medição de tempo é um palpite.

Procedimento

1. Encontre o alvo: classifique por custo total, não pela chamada individual mais lenta (veja o loop de triagem).
2. Capture a linha de base: EXPLAIN (ANALYZE, BUFFERS) <consulta> em dados do tamanho da produção. Dados de desenvolvimento pequenos escolhem planos diferentes.
3. Localize o nó onde o tempo ou as linhas dão errado (regras de leitura abaixo). Apenas corrija aquele nó.
4. Execute novamente a mesma captura de plano. Mantenha a alteração apenas se ela vencer tanto em tempo quanto em buffers.
5. Registre o índice ou a reescrita e o motivo, junto à migração de esquema.

Envolva EXPLAIN ANALYZE de INSERT/UPDATE/DELETE em BEGIN; ... ROLLBACK; porque ANALYZE realmente executa a instrução.

Lendo um plano

• Leia de baixo para cima, nós internos primeiro. O actual time por nó é por iteração; multiplique por loops para o custo real.
• Compare rows (estimativa) com actual rows. Uma diferença de 10x ou mais é a causa raiz muito mais frequente do que um índice faltando: execute ANALYZE <tabela>, aumente o alvo de estatísticas da coluna, ou adicione estatísticas estendidas (CREATE STATISTICS ... (dependencies, ndistinct)) para colunas correlacionadas.
• Rows Removed by Filter muito maior que as linhas retornadas significa que o índice (ou a falta dele) não é seletivo para este predicado.
• Buffers: shared read é disco ou cache do SO; shared hit é shared_buffers. Leituras altas em execuções repetidas significam que o conjunto de trabalho não cabe na memória.
• Sort Method: external merge Disk: significa que work_mem era muito pequeno para esta ordenação. Aumente por sessão ou por role, não globalmente, porque ele multiplica por nó de ordenação por conexão.
• Nested Loop com uma contagem grande de loops interna e um lado interno sem índice é o clássico join lento. Hash Join com derramamento (Batches: >1) é a mesma história de work_mem.
• Seq Scan é correto quando a consulta lê uma grande fração da tabela. Não adicione um índice para remover um Seq Scan que retorna 20% das linhas.
• Heap Fetches em um Index Only Scan significa que o mapa de visibilidade está desatualizado; execute VACUUM na tabela.

Escolhendo um índice

Indexe apenas o predicado, join ou ORDER BY que você mediu. Cada índice custa tempo de escrita, WAL e trabalho de vacuum.

• btree: padrão para igualdade, intervalo, ordenação. Ordem das colunas: colunas de igualdade primeiro, depois a coluna de intervalo ou ordenação. Skip scan no PG 18 ajuda alguns casos de coluna líder faltante, mas não projete em torno disso.
• Parcial (WHERE status = 'open'): quando consultas visam uma fatia pequena e estável (itens abertos, linhas ainda não processadas). O WHERE da consulta deve implicar o predicado do índice.
• De cobertura (INCLUDE (col)): quando um index-only scan removeria heap fetches para uma consulta quente. Colunas incluídas não são chaves pesquisáveis.
• De expressão (ON (lower(email)), ON ((payload->>'id'))): quando a consulta aplica uma função à coluna. A consulta deve usar a expressão idêntica.
• BRIN: para tabelas enormes de somente anexação onde a coluna correlaciona com a ordem física (timestamps, ids seriais). Índice minúsculo, poda grosseira. Verifique pg_stats.correlation perto de 1; se for baixo, BRIN não podará.
• GIN: contenção jsonb, arrays, texto completo. GiST: intervalos, geometria, restrições de exclusão.
• Crie em tabelas ativas com CREATE INDEX CONCURRENTLY; não pode ser executado dentro de um bloco de transação e uma execução com falha deixa um índice INVÁLIDO para ser removido.
• Remova índices não utilizados apenas após verificar pg_stat_user_indexes.idx_scan durante um ciclo completo de negócios (final do mês para cargas financeiras) e também nas réplicas.

Sargabilidade

Um predicado pode usar um índice apenas se a coluna indexada estiver sozinha de um lado.

• WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' em vez de WHERE extract(year from created_at) = 2026 ou date_trunc(...) = ....
• WHERE col = $1 com tipos correspondentes; casts no lado da coluna como numeric_col = '5'::text bloqueiam o índice. Converta o parâmetro, não a coluna.
• Evite LIKE '%x' com curinga inicial; use pg_trgm GIN ou busca em texto completo.
• OR entre colunas diferentes geralmente perde o índice; tente UNION ALL de dois ramos indexados.
• NOT IN (subconsulta) é hostil a NULL e lento; use NOT EXISTS.
• Prefira EXISTS para semi-joins em vez de DISTINCT após um join, porque ele para na primeira correspondência e não duplica linhas.

Funções janela vs self-joins

Use uma função janela quando o objetivo for um valor por linha a partir de linhas relacionadas no mesmo conjunto: totais acumulados, rank, lag/lead,