E agora, o que fazer com os índices que não são usados?

Encontrar o índice ideal para sua nem sempre é tarefa fácil.

Normalmente envolve tentativa e erro para encontrar uma boa combinação de índices.

Existem diversas ferramentas que podem “salvar o dia” e nos ajudar a identificar queries lentas. É como um passe de mágica: habamos um índice, e de repente aquele SELECT problemático começa a fluir.

Bom, o que muitas vezes esquecemos é verificar se a combinação de índices continua sendo útil com o passar do tempo. Isso também deve ser investigado, se necessário, otimizado.

Como identificar índices não utilizados?

Tão importante quanto criar o índice e identificar se eles ainda está sendo usado.

O PostgreSQL registra cada acesso a um índice. Podemos buscar essas informações e usá-las para verificar se um índice está sendo usado ou não.

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes ORDER BY idx_scan;

Você identificou na imagem acima que há um índice não são utilizado, entre outros que receberam acessos. O idx_scan está zerado, e seu disco está necessitando de espaço. Mas… pode apagar?

É nesse momento que precisamos de cautela. Antes do DROP INDEX, é bom seguir alguns passos de validação

Primeiro: certeza de que ele não está sendo usado?

Como vimos, um índice com idx_scan = 0 não significa necessariamente que ele está inútil. Pode ser que:

• o índice tenha sido criado recentemente;

• seja utilizado por uma parte do sistema que roda apenas em horários específicos; ou

• o mais comum: seja um índice exclusivo (UNIQUE) usado apenas para garantir a integridade dos dados em operações de INSERT, e esse tipo de uso não é registrado em idx_scan.

Diante disso, precisamos refinar a query para ignorar os índices exclusivos, que servem apenas para manter a integridade dos dados.

 SELECT schemaname
, relname
, indexrelname
, idx_scan
FROM pg_stat_user_indexes i
LEFT JOIN pg_constraint c
ON i.indexrelid = c.conindid
WHERE c.contype IS NULL
ORDER BY idx_scan DESC;

Mas posso remover direto?

 Poder, você pode. Mas o problema é: e se você estiver errado?

Alguns índices que apresentam uso podem estar mostrando um uso histórico, sem utilização atual. Queries, por alternativa, pode está usando outros índices, que facilmente poderiam utilizar outros, com um custo melhor ou igual…

Uma abordagem recomendada é monitorar esses números regulamente observando a evolução ao longo do tempo, tanto no banco de dados principal (master) quanto em qualquer no de replica.

Também é importante lembrar que os índices são replicados para os standbys e podem estar sendo usados por eles, às vezes até de forma exclusiva.

No fim, você pode decidir excluir o índice. E se houvesse uma maneira de excluir e depois restaurar instantaneamente, caso algo desse “errado”?

Reconstruir um índice pode ser custoso e levar horas, dependendo do volume da tabela — e isso pode ser um pouco assustador.

Mas, como já dizia um personagem icônico: Palma, palma, Não criemos cânico!

 Então, dá pra apagar sem apagar?

Sim! Desativando o índice temporariamente

O PostgreSQL permite que você torne um índice “inválido”. Isso faz com que o otimizador pare de usá-lo nas consultas, mas ele continua sendo atualizado normalmente por INSERT, UPDATE e DELETE. Assim, é como se você tivesse removido o índice, mas do ponto de vista das consultas, e pode retorna-los com um comando simples.

Preparando o terreno

Primeiro, verifique se já existem índices inválidos no seu banco (isso pode indicar alguma outra anomalia ou operação inacabada):

SELECT ir.relname AS indexname, it.relname AS tablename, n.nspname AS schemaname
FROM pg_index i
JOIN pg_class ir ON ir.oid = i.indexrelid
JOIN pg_class it ON it.oid = i.indrelid
JOIN pg_namespace n ON n.oid = it.relnamespace
WHERE NOT i.indisvalid;

Se existir índices inválidos, guarde essa lista. Ela vai te ajudar a distinguir os índices que você mesmo desativou dos que já estavam assim por outro motivo.

Criando as funções mágicas:

-- Desativa o índice (como se tivesse removido)
CREATE OR REPLACE FUNCTION trial_drop_index(iname TEXT) RETURNS VOID
LANGUAGE SQL AS $$
UPDATE pg_index SET indisvalid = false WHERE indexrelid = $1::regclass;

-- Reativa o índice (caso perceba que fez besteira)
CREATE OR REPLACE FUNCTION trial_undrop_index(iname TEXT) RETURNS VOID
LANGUAGE SQL AS $$
UPDATE pg_index SET indisvalid = true WHERE indexrelid = $1::regclass;
$$;

Simples assim. Com isso, você pode testar o impacto real de remover o índice, sem correr o risco de ficar horas esperando ele ser recriado depois.

Como isso funciona?

Essa abordagem aproveita um detalhe interno do PostgreSQL. Quando criamos um índice com CREATE INDEX CONCURRENTLY, ele só passa a ser considerado “válido” após uma verificação final de consistência. Durante esse tempo, ele já é atualizado, mas não é usado.

A ideia aqui é a mesma: ao definir indisvalid = false, você desliga o índice para o otimizador, mas continua mantendo ele atualizado. Isso significa que: Nenhuma consulta vai usar o índice. Mas se você quiser voltar atrás, pode simplesmente reativá-lo — sem precisar recriar nada.

Remover de vez? Só com segurança.

Se após o teste o índice não fez falta, aí sim, DROP INDEX sem culpa. Só não se esqueça de testar isso em ambientes com carga real e com monitoramento. Um comportamento estranho pode aparecer dias depois — então dê um tempo antes de bater o martelo.