PostgreSQL abandonne les index inutilisés

Dans PostgreSQL, les index sont des tableaux de recherche spéciaux que le moteur de recherche de la base de données peut utiliser pour accélérer les opérations de recherche de données. Ils sont utilisés pour localiser rapidement des données sans avoir à rechercher chaque ligne d'un tableau de base de données.

Utilisation

Les index sont utilisés pour améliorer la vitesse des opérations de recherche de données, mais ils peuvent ralentir les opérations d'écriture telles que INSERT, UPDATE, et DELETE en raison des frais de maintenance supplémentaires tels que la mise à jour de l'index à chaque modification des données. Ils doivent être supprimés s'ils ne sont pas utilisés afin d'améliorer les performances de la base de données et de réduire les coûts de stockage.

DROP INDEX IF EXISTS index_name;

Dans cette syntaxe, DROP INDEX IF EXISTS index_name supprime l'index s'il existe, ce qui évite les messages d'erreur si l'index n'est pas présent.

Exemples

1. Création d'un index de base

CREATE INDEX idx_customer_name
ON customers (customer_name);

Cet exemple crée un index sur la colonne customer_name du tableau customers afin d'améliorer les performances des requêtes portant sur ce champ.

2. Abandon d'un index inutilisé

DROP INDEX IF EXISTS idx_customer_name;

Cette commande supprime l'index idx_customer_name, libérant ainsi des ressources si cet index n'apporte aucun avantage en termes de performances.

3. Identification et suppression des index inutilisés

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

La première requête permet d'identifier les index qui n'ont jamais été utilisés (idx_scan = 0). Ici, idx_scan représente le nombre de fois où l'index a été utilisé dans une requête. Remplacez idx_unused par le nom de l'index identifié à partir de la requête :

DROP INDEX IF EXISTS idx_unused;

Conseils et bonnes pratiques

• Contrôlez régulièrement l'utilisation de l'index. Utilisez des vues du système comme pg_stat_user_indexes pour suivre l'utilisation des index et repérer les candidats à la suppression.
• Évaluez la nécessité d'un index. Assurez-vous que chaque index contribue de manière significative aux performances de la requête avant de le conserver.
• Utilisez la clause IF EXISTS. Cela permet d'éviter les erreurs si l'index n'existe pas lorsque l'on tente de le supprimer.
• Tenez compte de l'impact sur les opérations d'écriture. Un trop grand nombre d'index peut ralentir les opérations de INSERT, UPDATE et DELETE en raison des frais de maintenance supplémentaires.
• Types d'index. Comprendre que différents types d'index (par exemple, B-tree, hash, GIN, GiST) peuvent être plus adaptés à des cas d'utilisation spécifiques.
• Ajoutez des mises en garde à l'adresse DROP INDEX. L'abandon d'un index peut allonger les temps d'interrogation des requêtes qui s'appuyaient auparavant sur cet index ; commencez toujours par tester dans un environnement de non-production.
• Pensez à l'entreposage. Les index inutilisés consomment de l'espace disque et doivent être supprimés pour optimiser l'efficacité du stockage.