Auditoria de índices do Postgres com pg_stat: encontre índices lentos e não utilizados

O que é (e o que não é) uma auditoria de índices do Postgres

Uma auditoria de índices do Postgres é um veredito baseado no que seu banco realmente faz em produção. Responde perguntas práticas: quais consultas são lentas, quais índices são usados, quais nunca são tocados e para onde vai o tempo.

O objetivo não é colecionar índices “legais de ter”. É acelerar os caminhos mais ativos e reduzir custo evitável.

Índices muitas vezes parecem adequados em dev porque o tráfego de desenvolvimento é organizado: tabelas pequenas, consultas previsíveis e alguns caminhos felizes. A produção é mais bagunçada. Tabelas crescem, filtros e ordenações variam, casos de borda aparecem e muitos usuários atingem os mesmos endpoints ao mesmo tempo. Uma consulta que parece “rápida o suficiente” com 10.000 linhas pode quebrar com 10 milhões se começar a fazer varreduras completas ou grandes ordenações.

Uma boa auditoria ajuda você a:

• Identificar padrões específicos de consultas lentas sob carga real
• Confirmar quais índices estão ajudando hoje
• Encontrar índices que custam armazenamento e tornam gravações mais lentas sem retorno
• Decidir o que adicionar ou ajustar e como provar que ajudou

“Estatísticas reais de consultas” significa comportamento medido pelo workload ao vivo: com que frequência uma consulta roda, quanto tempo leva, quantas linhas retorna e se há picos. Adivinhar apenas pelo esquema é arriscado. Um esquema pode sugerir o que poderia ser indexado, mas não diz quais filtros os usuários realmente usam ou quais consultas dominam o tempo do banco.

Também é importante manter claros os trade-offs. Índices não são gratuitos:

• Eles aceleram leituras (SELECT) quando casam com seus filtros, joins e ordem.
• Podem desacelerar gravações (INSERT/UPDATE/DELETE) porque toda alteração precisa atualizar índices.
• Aumentam armazenamento e trabalho de manutenção (vacuum, bloat, backups).

Portanto, auditoria não é “colocar índices em todo lugar”. É “colocar os certos, remover o desperdício e verificar resultados com estatísticas.” Isso aparece muito em codebases geradas por IA (por exemplo de Cursor, Replit, ou v0), onde consultas frequentemente são lançadas sem indexação cuidadosa. O app funciona num demo, depois o tráfego de produção encontra os caminhos lentos rapidamente.

Antes de começar: garanta que suas estatísticas são confiáveis

Uma auditoria de índices vale pelo que as estatísticas indicam. Se seu banco reiniciou, teve failover ou estatísticas foram resetadas, você pode acabar adicionando o índice errado ou apagando um útil.

Primeiro, confirme que você consegue ler as views necessárias. A maioria dos sinais vem de views do sistema como pg_stat_*. Em Postgres gerenciado você pode não ser superuser, mas normalmente consegue ler as pg_stat comuns com uma role voltada para monitoramento.

Antes de puxar números, faça algumas checagens rápidas:

• Garanta que você consegue consultar pg_stat_database, pg_stat_user_tables e pg_stat_user_indexes sem erros.
• Verifique se as estatísticas foram resetadas recentemente (após restart, failover ou reset manual).
• Confirme se as queries da sua app não estão sendo em grande parte contornadas por uma camada de cache (isso pode esconder a carga real do banco).
• Rode a auditoria no mesmo banco que serve o tráfego real.

Se puder usar, pg_stat_statements torna a auditoria muito mais confiável porque agrega comportamento real de consultas ao longo do tempo (chamadas, tempo total, tempo médio). Ele deve estar instalado e habilitado previamente e só rastreia consultas depois de começar a coletar.

Escolha uma janela de observação que corresponda ao uso da aplicação. Um padrão razoável é alguns ciclos reais de negócio, incluindo horas de pico.

Evite usar uma janela distorcida a menos que a distorção seja o problema que você quer resolver. Não faça uma auditoria logo após:

• Um deploy grande que mudou padrões de consulta
• Um backfill ou migração que inundou o banco
• Uma importação em massa que não é tráfego normal
• Um incidente em que o sistema estava degradado

Uma armadilha comum: uma funcionalidade recém-implantada acidentalmente dispara um loop N+1. Suas estatísticas vão pedir índices, mesmo que a correção real seja parar a explosão de consultas.

As views pg_stat que você vai usar (mapa rápido)

Uma auditoria prática começa com um princípio: confie no que seu banco observou sob tráfego real.

Essas views cobrem a maior parte do necessário:

• pg_stat_statements: um placar de consultas normalizadas com totais e médias de tempo, chamadas e linhas. Aqui você encontra quais padrões queimam mais latência.
• pg_stat_user_tables: como cada tabela é acessada. O sinal chave é se ela é majoritariamente acessada via índices ou via varreduras sequenciais.
• pg_stat_user_indexes: com que frequência cada índice é usado. Útil para identificar índices que parecem importantes mas quase não são usados.
• pg_statio_user_tables: se leituras de tabela vêm do cache ou do disco. Leituras altas do disco frequentemente correlacionam com lentidão visível ao usuário.
• pg_statio_user_indexes: mesma visão cache vs disco, mas para páginas de índice.

Um mal-entendido comum: contagens de uso e impacto não são a mesma coisa.

• Contagens de uso (como “quantas vezes um índice foi escaneado”) mostram popularidade, não tempo salvo.
• Sinais de tempo e I/O (de pg_stat_statements e das views pg_statio_*) mostram dor.

Um bom fluxo é: encontre formas de consulta caras em pg_stat_statements, então use estatísticas de tabela e índice para entender por que essas consultas são lentas (varreduras de tabela, leituras de disco, baixa seletividade etc.).

Lembre-se também que estatísticas não são permanentes. Elas resetam no restart e podem ser resetadas manualmente. “Não usado” às vezes só significa “não usado desde o último reset”, então certifique-se de que sua janela cubra o tráfego normal.

Passo a passo: rode uma auditoria rápida de índices em 30–60 minutos

Esta é uma auditoria rápida baseada em sinais de tráfego real. Se você herdou um app onde páginas ficam lentas ou dão timeout, esses snapshots ajudam a focar no que os usuários realmente atingem.

Passo 1: Puxe suas consultas mais lentas (tempo total e tempo médio).

-- Requires pg_stat_statements
-- Postgres 13+ uses *_exec_time columns
SELECT
  queryid,
  calls,
  total_exec_time AS total_ms,
  mean_exec_time  AS mean_ms,
  rows,
  shared_blks_read,
  shared_blks_hit,
  left(query, 120) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

SELECT
  queryid,
  calls,
  total_exec_time AS total_ms,
  mean_exec_time  AS mean_ms,
  rows,
  shared_blks_read,
  shared_blks_hit,
  left(query, 120) AS query_sample
FROM pg_stat_statements
WHERE calls >= 20
ORDER BY mean_exec_time DESC
LIMIT 20;

Use a primeira query para encontrar o que consome mais tempo total ao longo do dia. Use a segunda para encontrar statements lentos que podem causar picos de latência ao usuário.

Passo 2: Encontre tabelas com muitas leituras sequenciais. Estas são candidatas comuns a “índice faltando ou formato de consulta errado”.

SELECT
  schemaname,
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan,
  n_live_tup,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 20;

Passo 3: Liste índices que são não usados ou quase não usados. Índices grandes com baixo idx_scan são suspeitos.

SELECT
  schemaname,
  relname      AS table_name,
  indexrelname AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC
LIMIT 30;

Passo 4: Identifique tabelas com muitos writes onde indexes extras podem te atrapalhar. Cada índice a mais faz inserts e updates fazerem mais trabalho.

SELECT
  schemaname,
  relname,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_tup_hot_upd,
  (n_tup_ins + n_tup_upd + n_tup_del) AS total_writes
FROM pg_stat_user_tables
ORDER BY total_writes DESC
LIMIT 20;

Passo 5: Salve snapshots com timestamp para comparar antes e depois. Armazene resultados em lugar consistente (CSV ou uma tabela pequena) e anote o que mudou.

CREATE SCHEMA IF NOT EXISTS audit;
CREATE TABLE IF NOT EXISTS audit.slow_queries_snap (
  captured_at timestamptz NOT NULL,
  queryid     bigint,
  calls       bigint,
  total_ms    double precision,
  mean_ms     double precision,
  query_sample text
);

INSERT INTO audit.slow_queries_snap
SELECT
  now(), queryid, calls,
  total_exec_time, mean_exec_time,
  left(query, 200)
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 50;

Como identificar índices faltantes usando sinais reais de tráfego

Um índice faltante geralmente aparece como duas coisas ao mesmo tempo:

• Uma tabela grande sendo escaneada repetidamente
• Um pequeno conjunto de padrões de consulta aparecendo repetidamente como caros

Comece pelos sinais a nível de tabela. Em pg_stat_user_tables, um alto seq_scan (especialmente combinado com alto seq_tup_read) em uma tabela grande é um forte indício de que consultas estão filtrando ou fazendo joins sem um índice. Não é prova, mas indica onde olhar.

Então conecte isso a padrões de consulta em pg_stat_statements. Você busca padrões repetidos:

• Filtros comuns em WHERE (por exemplo, user_id, created_at, tenant_id)
• Chaves de JOIN (foreign keys frequentemente são culpadas)
• Colunas em ORDER BY que forçam ordenações grandes
• Consultas com LIMIT que ainda leem muito porque não conseguem usar um índice efetivamente

Uma regra prática: indexe colunas seletivas primeiro. Uma coluna seletiva tem muitos valores distintos, então reduz a busca rapidamente. WHERE user_id = 42 geralmente é seletiva. WHERE status = 'active' muitas vezes não é.

Índices multi-coluna podem ser a correção certa, mas a ordem importa porque Postgres usa a parte mais à esquerda de um índice btree de forma mais eficaz. Se sua consulta comum é:

SELECT * FROM orders
WHERE user_id = $1 AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

Um índice como (user_id, created_at) casa com esse padrão. O inverso (created_at, user_id) frequentemente ajuda menos porque não restringe para um usuário específico cedo.

Como encontrar índices não usados (e quando não removê-los)

Índices não usados são traiçoeiros: adicionam custo nas escritas, ocupam disco e tornam vacuum e backups mais pesados. O objetivo não é deletar índices agressivamente. É remover os que nunca ajudam seu workload real.

Comece por pg_stat_user_indexes. Uma primeira passada básica é:

• idx_scan = 0 desde o último reset de estatísticas (candidato)
• Escaneamentos muito baixos comparados ao quanto a tabela é escrita (candidato)

Depois, valide com contexto. Alguns índices existem por correção, não por velocidade:

• Índices que suportam PRIMARY KEY ou UNIQUE
• Índices críticos para checagens de foreign key
• Índices usados para regras da aplicação (por exemplo, “uma assinatura ativa por usuário”)
• Índices necessários para jobs raros mas importantes (relatórios, exports, forense)

Um erro clássico: ver idx_scan = 0 para orders_created_at_idx, apagar e depois descobrir que uma conciliação mensal depende dele. Se você olhar só uma semana de estatísticas, pode tomar a decisão errada.

Um processo cauteloso que funciona bem:

1. Faça uma lista curta de candidatos (tabela, índice, por que você acha que está sem uso).
2. Reavalie após um ciclo completo de negócio (muitas vezes 2–4 semanas).
3. Se ainda parecer sem uso, remova durante uma janela planejada e observe latência e tempos de escrita.
4. Mantenha a definição do índice para poder recriá-lo rapidamente.

Encontre os piores criminosos de latência pelas estatísticas de consulta

Se você quer o maior ganho rápido, ordene consultas reais pelo tempo total que consomem. pg_stat_statements ajuda a ver o que é caro durante o dia, não só o que é lento ocasionalmente.

Duas situações para comparar:

• Uma consulta muito lenta (2 segundos) que roda duas vezes por dia
• Uma consulta “média” (40 ms) que roda 10.000 vezes por dia

A segunda normalmente prejudica mais.

Aqui vai um ponto de partida simples. Nomes de colunas variam por versão do Postgres (você pode ver total_time/mean_time em vez de total_exec_time/mean_exec_time):

SELECT
  queryid,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  shared_blks_hit,
  shared_blks_read,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Então rode novamente ordenando por tempo médio para achar statements lentos pontuais:

SELECT
  queryid,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  shared_blks_hit,
  shared_blks_read,
  query
FROM pg_stat_statements
WHERE calls > 50
ORDER BY mean_exec_time DESC
LIMIT 20;

Para cada consulta candidata, capture alguns fatos antes de mudar índices:

• Texto da query e queryid
• Calls, tempo total, tempo médio
• Linhas retornadas (ela retorna muito mais do que a app precisa?)
• shared_blks_hit vs shared_blks_read (está majoritariamente em cache ou fazendo muitas leituras de disco?)
• Um EXPLAIN (ANALYZE, BUFFERS) rápido em um ambiente seguro

Índices nem sempre são a resposta. Alta latência também vem de joins ruins que multiplicam linhas, paginação ausente, buscar grandes blobs JSON ou fazer trabalho demais dentro do SQL.

Validar e projetar o índice certo (sem adivinhação)

Depois de identificar uma consulta lenta, não crie um índice imediatamente. Primeiro confirme o que o banco está realmente fazendo para aquela forma de consulta em produção. A forma mais rápida é testar a consulta exata (com parâmetros realistas) usando EXPLAIN (ANALYZE, BUFFERS).

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 42
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;

Leia o plano como uma história:

• Seq Scan significa que o Postgres está lendo grande parte da tabela e filtrando depois. Isso é aceitável em tabelas pequenas, mas geralmente lento em tabelas grandes.
• Index Scan ou Index Only Scan significa que o Postgres pode pular para as linhas necessárias.
• BUFFERS mostra quanto trabalho foi feito em memória vs disco, o que muitas vezes explica por que uma query é rápida às vezes e lenta sob pico.

Ao projetar um índice, faça-o casar com como a consulta filtra e ordena:

• Índice de coluna única quando uma coluna faz a maior parte do filtro (por exemplo user_id).
• Índice composto quando você filtra por várias colunas juntas (por exemplo (user_id, status)). A ordem das colunas importa.
• Índice parcial quando você sempre consulta um subconjunto (por exemplo apenas status = 'open'). Índice menor, menor custo de escrita.
• Incluir a coluna de ordenação quando isso evita uma ordenação grande (por exemplo (user_id, status, created_at)).

Mantenha mudanças pequenas e reversíveis:

1. Adicione um índice de cada vez direcionado a uma consulta de alta latência.
2. Rode EXPLAIN (ANALYZE, BUFFERS) novamente para confirmar que o plano melhorou.
3. Meça de novo em uma janela comparável.
4. Se o tempo de escrita ou lock piorar, reverta.

Se você herdou código gerado por IA, a própria consulta frequentemente é o problema real (filtros ausentes, buscando muitas linhas, ordenando conjuntos enormes). Corrija a consulta primeiro quando for o caso.

Erros comuns que fazem auditorias de índices darem errado

Auditorias falham quando você trata uma estatística isolada como “a verdade” e age rápido demais.

Erros comuns:

• Dropar um índice que protege correção. Índices que suportam PRIMARY KEY ou UNIQUE importam mesmo que pareçam “não usados.” Alguns índices também são críticos para checagens de foreign key.
• Perseguir idx_scan sem olhar para tempo e linhas. Uma contagem baixa não significa automaticamente “seguro para dropar” e uma contagem alta não significa automaticamente “deve manter”.
• Adicionar índices sobrepostos para cada variação levemente diferente de consulta. Isso leva a quase-duplicatas, maior custo de escrita e mais manutenção.
• Culpar índices quando o problema real é bloat ou estatísticas desatualizadas. Se autovacuum e analyze estiverem atrasados, planos podem degradar e varreduras ficar mais lentas.
• Testar durante horas tranquilas e assumir que o pico melhora. Aquecimento de cache, concorrência e pressão de escrita mudam o comportamento dos planos.

Faça uma mudança por vez, meça antes e depois sob carga similar e mantenha um plano de rollback.

Checklist rápido e próximos passos

Uma auditoria vale se você puder repeti-la. Mantenha um relatório simples que você rode hoje e depois rode novamente após cada mudança para provar o ganho (ou capturar uma regressão).

Seu relatório repetível de 30 minutos

• Capture top consultas por tempo total e por tempo médio (mantenha pequeno, como 20 cada).
• Observe tabelas grandes com altas leituras sequenciais.
• Mantenha uma lista curta de revisão de índices que parecem não usados, com uma justificativa de uma linha.
• Proponha até três mudanças (adicionar, remover ou ajustar) e vincule cada uma a uma consulta ou tabela específica.
• Rode o mesmo relatório após o deploy e compare tempo, chamadas, linhas e scans.

Antes de dropar qualquer coisa, faça uma checagem de exceção: o índice sustenta uma primary key ou unique, suporta uma foreign key, ou existe para um job raro mas importante?

Quando precisa de um segundo par de olhos

Se seu app foi gerado por ferramentas como Replit, Cursor ou v0, problemas de performance frequentemente vêm de uma mistura de índices faltantes, consultas ineficientes e padrões inseguros. FixMyMess (fixmymess.ai) foca em diagnosticar e reparar codebases geradas por IA, incluindo problemas de consulta e esquema que só aparecem sob carga de produção. Se estiver travado, uma auditoria direcionada pode ajudar a escolher o menor conjunto de mudanças que realmente reduz a latência.