PostgreSQL eliminando índices não utilizados

No PostgreSQL, os índices são tabelas de pesquisa especiais que o mecanismo de busca do banco de dados pode usar para acelerar as operações de recuperação de dados. Eles são usados para localizar dados rapidamente sem que você precise pesquisar cada linha em uma tabela do banco de dados.

Uso

Os índices são usados para aumentar a velocidade das operações de recuperação de dados, mas podem tornar as operações de gravação mais lentas, como INSERT, UPDATE e DELETE, devido à sobrecarga de manutenção adicional, como a atualização do índice a cada modificação de dados. Eles devem ser descartados se não forem usados para melhorar o desempenho do banco de dados e reduzir os custos de armazenamento.

DROP INDEX IF EXISTS index_name;

Nessa sintaxe, o site DROP INDEX IF EXISTS index_name remove o índice se ele existir, evitando mensagens de erro se o índice não estiver presente.

Exemplos

1. Criação de índices básicos

CREATE INDEX idx_customer_name
ON customers (customer_name);

Este exemplo cria um índice na coluna customer_name da tabela customers para melhorar o desempenho da consulta envolvendo esse campo.

2. Eliminação de um índice não utilizado

DROP INDEX IF EXISTS idx_customer_name;

Esse comando remove o índice idx_customer_name, liberando recursos se esse índice não estiver proporcionando nenhum benefício de desempenho.

3. Identificação e eliminação de índices não utilizados

SELECT indexrelid::regclass AS index_name,
       idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

A primeira consulta ajuda a identificar os índices que nunca foram usados (idx_scan = 0). Aqui, idx_scan representa o número de vezes que o índice foi usado em uma consulta. Substitua idx_unused pelo nome real do índice identificado na consulta:

DROP INDEX IF EXISTS idx_unused;

Dicas e práticas recomendadas

• Monitore regularmente o uso do índice. Use exibições do sistema como pg_stat_user_indexes para rastrear o uso do índice e identificar candidatos para remoção.
• Avalie a necessidade de um índice. Certifique-se de que cada índice beneficie significativamente o desempenho da consulta antes de mantê-lo.
• Use a cláusula IF EXISTS. Isso evita erros se o índice não existir quando você tentar eliminá-lo.
• Considere o impacto nas operações de gravação. O excesso de índices pode tornar as operações INSERT, UPDATE e DELETE mais lentas devido à sobrecarga de manutenção adicional.
• Tipos de índices. Entender que diferentes tipos de índices (por exemplo, árvore B, hash, GIN, GiST) podem ser mais adequados para casos de uso específicos.
• Adicione advertências para DROP INDEX. A eliminação de um índice pode aumentar os tempos de consulta para consultas que anteriormente dependiam dele; sempre teste primeiro em um ambiente que não seja de produção.
• Tenha em mente o armazenamento. Os índices não utilizados consomem espaço em disco e devem ser descartados para otimizar a eficiência do armazenamento.