PostgreSQL: encontre o índice certo com EXPLAIN

1. Reduza o problema a uma consulta#

Uma API lenta pode estar esperando conexões, serializando dados demais ou executando centenas de consultas pequenas. Antes de criar um índice, identifique a consulta que consome tempo e quantas vezes ela é executada. Guarde parâmetros representativos sem copiar dados sensíveis para os logs.

Imagine uma lista de jobs por workspace e status, ordenada do mais recente para o mais antigo. O padrão inclui filtro, ordenação e limite. É esse conjunto que você precisa otimizar. Testar só um SELECT por identificador não diz nada sobre a tela que realmente está com problema. Reproduza o problema no ambiente de testes autorizado com uma distribuição de dados representativa.

2. Diferencie estimativas de execução real#

O EXPLAIN mostra o plano estimado. O EXPLAIN ANALYZE executa o comando e acrescenta observações reais; use-o com cuidado mesmo em SELECTs que chamam funções. Em escritas, uma transação com rollback não garante reverter efeitos externos de funções. Comece com leituras controladas e um orçamento de tempo.

-- Em staging autorizado: $1 e $2 são parâmetros do driver.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, title
FROM jobs
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC, id DESC
LIMIT 50;

Compare linhas estimadas e reais, repetições de cada nó e buffers. O custo do plano não é expresso em milissegundos. Os tempos dos nós superiores incluem o trabalho dos filhos: somá-los sem cuidado conta o trabalho duas vezes. Uma diferença grande de cardinalidade pode indicar estatísticas insuficientes ou uma distribuição enviesada.

Documentação: PostgreSQL: como interpretar o EXPLAIN ↗

3. Faça o índice atender ao padrão de acesso#

Para esse padrão, um candidato é um B-tree que comece por tenant_id e status, seguido de created_at e id. As duas primeiras colunas correspondem a igualdades e as seguintes à ordenação. É uma hipótese de trabalho, não uma receita para todas as consultas da tabela. Verifique também as telas que filtram de outra forma.

-- Executar fora de um bloco de transação.
CREATE INDEX CONCURRENTLY jobs_tenant_status_created_id_idx
ON jobs (tenant_id, status, created_at DESC, id DESC);

O índice ocupa espaço e adiciona trabalho a inserts e updates. Não coloque todas as colunas como cobertura por reflexo. Compare o plano e o comportamento de escrita antes e depois. Um scan sequencial pode ser razoável se a consulta precisa de boa parte da tabela.

Documentação: PostgreSQL: índices multicoluna ↗ · PostgreSQL: CREATE INDEX e construção concorrente ↗

4. Use uma ordenação estável ao paginar#

O created_at pode se repetir. Adicionar um identificador único como critério de desempate permite descrever uma posição sem ambiguidade. Em ordem decrescente, a próxima página pede tuplas menores que a última recebida. Este exemplo assume que created_at e id não aceitam NULL e mantêm o valor durante a navegação.

SELECT id, created_at, title
FROM jobs
WHERE tenant_id = $1 AND status = $2
  AND (created_at, id) < ($3, $4)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Valide o cursor e vincule-o aos filtros e à ordenação. O tenant continua vindo da sessão autorizada, nunca do cursor. Se datas ou filtros mudam durante a navegação, o conjunto pode variar: essa técnica evita o custo do OFFSET profundo, mas não cria um snapshot transacional entre requisições.

5. Confira o ganho sob carga#

Uma leitura isolada depois de aquecer o cache não representa todas as requisições. Compare várias execuções e percentis do endpoint, com concorrência razoável e tenants de tamanhos diferentes. Observe conexões ocupadas, tempos de lock, CPU, leituras físicas e o volume devolvido ao cliente.

Registre a versão do schema, o plano e as condições do teste. Se você não consegue reproduzir o volume com segurança, documente essa limitação na decisão. Não transforme uma medição pequena em promessa de capacidade.

6. Entregue uma migração verificável#

A entrega inclui conferir se o índice é válido, se o plano relevante consegue usá-lo e se não há regressão nas escritas. Defina uma forma de reverter junto com o time que opera o banco. Evite combinar a mudança com outras migrações difíceis de separar, porque você perderia clareza ao atribuir o resultado.

Observe de novo quando os dados crescerem ou a consulta mudar. O índice certo hoje pode deixar de ser suficiente se o filtro mais frequente mudar. Um provedor gerenciado facilita a operação, mas não conhece o contrato do seu endpoint: essa parte do design continua sendo responsabilidade da aplicação.