Índice é uma das poucas coisas em backend onde uma linha de SQL muda uma resposta de oito segundos pra oito milissegundos. Também é uma das poucas onde criar demais tem custo real e invisível.
O padrão que eu encontro em projeto que cresceu rápido é sempre o mesmo: vinte índices, doze deles nunca lidos, e a query mais cara do sistema fazendo sequential scan numa tabela de quatro milhões de linhas.
Primeiro, descubra o que está doendo
Antes de criar qualquer coisa, ligue a extensão que responde isso.
create extension if not exists pg_stat_statements;
select
calls,
round(mean_exec_time::numeric, 2) as media_ms,
round(total_exec_time::numeric / 1000, 1) as total_s,
query
from pg_stat_statements
order by total_exec_time desc
limit 20;Ordene por tempo total, não por tempo médio. Uma query de 4 segundos chamada dez vezes por dia importa muito menos que uma de 80 milissegundos chamada duzentas mil vezes. A segunda é a que está consumindo o seu banco, e ela nunca aparece em reclamação de usuário.
Leia o plano, não o palpite
explain (analyze, buffers)
select * from ledger_entry
where account_id = '...' and created_at >= now() - interval '30 days';Três coisas pra procurar na saída.
Seq Scan em tabela grande com filtro seletivo. É o caso óbvio de índice faltando.
Divergência entre rows= estimado e actual rows=. Se o planejador acha que vem 30 linhas e vêm 300 mil, ele escolheu a estratégia errada com base em estatística velha. Um ANALYZE resolve, e se voltar a acontecer, o problema é a frequência do autovacuum.
Rows Removed by Filter alto. O índice foi usado, trouxe muita linha e o banco jogou quase tudo fora. Falta uma coluna no índice.
A ordem das colunas em índice composto
É o erro conceitual mais comum, e o mais caro.
create index on orders (created_at, status, tenant_id);Esse índice não serve pra where tenant_id = ? and status = ?. Índice composto funciona como lista telefônica ordenada por sobrenome, depois nome. Buscar só pelo nome não aproveita a ordenação.
A regra: colunas de igualdade primeiro, a de faixa por último.
-- para: where tenant_id = ? and status = ? and created_at >= ?
create index on orders (tenant_id, status, created_at);Um índice (a, b, c) já atende consultas por a, e por a, b. Então não crie os três separados. Isso sozinho costuma eliminar metade dos índices redundantes de um projeto.
Índice parcial: o mais subestimado
Quando você quase sempre consulta um subconjunto pequeno, indexe só ele.
-- 98% dos pedidos já foram processados e ninguém consulta eles por status
create index on orders (tenant_id, created_at)
where status in ('pending', 'processing');O índice fica uma fração do tamanho, cabe na memória e é bem mais rápido de manter na escrita. Em tabela com muito registro histórico e consulta concentrada no recente, a diferença é grande.
O mesmo vale pra soft delete. Se todas as consultas filtram deleted_at is null, o índice deveria ter essa condição.
O que ninguém remove
Índice não é gratuito. Cada INSERT e UPDATE atualiza todos os índices da tabela. Uma tabela de escrita intensa com oito índices escreve nove vezes.
Ache os inúteis:
select
relname as tabela,
indexrelname as indice,
idx_scan as leituras,
pg_size_pretty(pg_relation_size(indexrelid)) as tamanho
from pg_stat_user_indexes
where idx_scan < 50
order by pg_relation_size(indexrelid) desc;idx_scan perto de zero em índice antigo significa que ele nunca foi usado. Antes de derrubar, confirme que as estatísticas não foram zeradas recentemente e que não é um índice de constraint única. Depois, derrube.
Criar índice sem travar a produção
Em tabela grande, create index comum trava escrita durante a criação inteira.
create index concurrently idx_orders_tenant_status
on orders (tenant_id, status, created_at);CONCURRENTLY é mais lento e não bloqueia escrita. Ele também não pode rodar dentro de transação, o que é justamente o problema numa migração de TypeORM ou de qualquer ferramenta que embrulhe tudo em transação. A saída é marcar essa migração como sem transação.
E confira depois: se falhar no meio, o índice fica com estado invalid, presente e não usado.
select indexrelid::regclass from pg_index where not indisvalid;Quando índice não é a resposta
Nem todo problema de performance é índice.
Se a query traz cem mil linhas pra aplicação filtrar, o problema é a query. Se é N+1, o problema é a camada de acesso a dados. Se roda a cada requisição e o dado muda uma vez por dia, o problema é falta de cache. Se soma a tabela inteira todo request, o problema é modelagem, e provavelmente falta uma tabela de agregado.
Índice acelera busca. Ele não conserta desenho.
O ritual mínimo
Uma vez por mês: olhar o top 20 do pg_stat_statements, conferir índices nunca lidos e rodar explain analyze na query mais cara.
Vinte minutos. É a melhor relação entre esforço e resultado que existe em performance de backend, e quase ninguém faz.