Bien que les index de PostgreSQL™ n'aient pas besoin de maintenance ni d'optimisation, il est important de s'assurer que les index sont effectivement utilisés sur un système en production. On vérifie l'utilisation d'un index pour une requête particulière avec la commande EXPLAIN. Son utilisation dans notre cas est expliquée dans la Section 13.1, « Utiliser EXPLAIN ». Il est aussi possible de rassembler des statistiques globales sur l'utilisation des index sur un serveur en cours de fonctionnement, comme décrit dans la Section 24.2, « Le récupérateur de statistiques ».
Il est difficile de donner une procédure générale pour déterminer quels index doivent être créés. Plusieurs cas typiques ont été cités dans les exemples précédents. Une bonne dose d'expérimentation sera nécessaire dans de nombreux cas. Le reste de cette section donne quelques pistes.
La première chose à faire est de lancer ANALYZE. Cette commande collecte les informations sur la distribution des valeurs dans la table. Cette information est nécessaire pour essayer de deviner le nombre lignes retournées par une requête. L'optimiseur de requêtes en a besoin pour donner des coûts réalistes aux différents plans de requêtes possibles. En l'absence de statistiques réelles, le système utilise quelques valeurs par défaut, qui ont toutes les chances d'être inadaptées. Examiner l'utilisation des index par une application sans avoir lancé ANALYZE préalablement est du coup une cause perdue.
Utilisez des données réelles pour l'expérimentation. Utiliser des données de test pour mettre en place des index vous permettra de trouver les index dont vous avez besoin pour vos données de test, mais c'est tout.
Il est particulièrement néfaste d'utiliser des jeux de données très réduits. Alors qu'une requête sélectionnant 1000 lignes parmi 100000 pourrait utiliser un index, il est peu probable qu'une requête sélectionnant 1 ligne dans une table de 100 lignes le fasse, parce que les 100 lignes tiennent probablement dans une seule page sur le disque, et qu'il n'y a aucun plan d'exécution qui puisse aller plus vite que la lecture d'une seule page.
Soyez aussi vigilant en créant des données de test, ce qui est souvent inévitable quand l'application n'est pas encore en production. Les valeurs qui sont très similaires, complètement aléatoire, ou insérées déjà triées peuvent modifier la distribution des données et fausser les statistiques.
Quand les index ne sont pas utilisés, il peut être utile pour les tests de forcer leur utilisation. Certains paramètres d'exécution du serveur peuvent interdire certains types de plans (voir la Section 17.6.1, « Configuration de la méthode du planificateur »). Par exemple, en interdisant les lectures séquentielles de tables enable_seqscan) et les jointures à boucles imbriquées (enable_nestloop), qui sont les deux plans les plus basiques, on forcera le système à utiliser un plan différent. Si le système continue néanmoins à choisir une lecture séquentielle ou une jointure à boucles imbriquées, alors il y a probablement une raison plus fondamentale qui empêche l'utilisation de l'index ; par exemple que la condition ne correspond pas à l'index. (Les sections précédentes expliquent quelles sortes de requêtes peuvent utiliser quelles sortes d'index.)
Si l'index est effectivement utilisé en forçant son utilisation, alors il y a deux possibilités: Soit le système a raison et l'utilisation de l'index est effectivement inappropriée, soit les coûts estimés des plans de requêtes ne reflètent pas la réalité. Il faut alors comparer la durée de la requête avec et sans index. La commande EXPLAIN ANALYZE peut être utile pour cela.
S'il apparaît que les estimations de coûts sont fausses, il y a de nouveau deux possibilités. Le coût total est calculé à partir du coût par ligne de chaque nœud du plan, multiplié par l'estimation de sélectivité du nœud de plan. Le coût estimé des nœuds de plan peut être ajusté avec des paramètres d'exécution (décrits dans la Section 17.6.2, « Constantes de coût du planificateur »). Une estimation de sélectivité inadaptée est due à des statistiques insuffisantes. Il est peut être possible de les améliorer en optimisant les paramètres de collecte de statistiques. Voir ALTER TABLE.
Si vous n'arrivez pas à ajuster les coûts pour qu'ils représentent mieux la réalité, alors vous devrez forcer l'utilisation de l'index explicitement. Si vous le voulez, Vous pouvez aussi contacter les développeurs de PostgreSQL™ afin qu'ils examinent le problème.
Classes d'opérateurs