11.7. Index partiels

Un index partiel est un index construit sur un sous-ensemble d'une table ; le sous-ensemble est défini par une expression conditionnelle (appelée le prédicat de l'index partiel). L'index ne contient des entrées que pour les lignes de la table qui satisfont au prédicat. Les index partiels sont une fonctionnalité spécialisée mais il existe plusieurs situations où ils sont utiles.

Une raison majeure pour l'utilisation principale des index partiels est d'éviter d'indexer les valeurs trop courantes. Comme une requête qui fait des recherches sur une valeur trop courante (qui correspond à plus de quelques pour-cent des lignes) n'utilisera pas cet index de toute façon, il ne sert à rien de garder ces lignes dans l'index. Cela réduit la taille de l'index, ce qui accélère les requêtes qui l'utilisent. Cela accélère aussi beaucoup d'opérations de mise à jour de la table car l'index n'a pas besoin d'être mis à jour à chaque fois. L'Exemple 11.1, « Mettre en place un index partiel pour exclure les valeurs courantes » montre une application possible de cette idée.

Exemple 11.1. Mettre en place un index partiel pour exclure les valeurs courantes

Supposons que vous enregistrez un journal d'accès à un serveur web dans une base de données. La plupart des accès proviennent de classes d'adresses IP internes à votre organisation, mais certaines viennent d'ailleurs (disons des employés connectés par modem). Si vos recherches sur des adresses IP concernent essentiellement les accès extérieurs, vous n'avez probablement pas besoin d'indexer les classes d'adresses IP qui correspondent au sous-réseau de votre organisation.

Supposons que la table soit comme ceci :

CREATE TABLE access_log (
    url varchar,
    client_ip inet,
    ...
);

Pour créer un index partiel qui corresponde à notre exemple, il faut utiliser une commande comme celle-ci :

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
    WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');

Une requête typique pouvant utiliser cet index est :

SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

Une requête qui ne peut pas l'utiliser est :

SELECT * FROM access_log WHERE client_ip = inet '192.168.100.23';

Observez que ce type d'index partiel nécessite que les valeurs courantes soient prédéterminées. Si la distribution des valeurs est inhérente (du fait de la nature de l'application) et statique (ne changeant pas dans le temps), ce n'est pas trop difficile mais, si les valeurs courantes sont simplement dues au hasard, cela peut demander beaucoup de travail de maintenance pour modifier la définition de l'index de temps en temps.


Une autre utilisation possible d'un index partiel revient à exclure des valeurs de l'index qui ne correspondent pas aux requêtes courantes ; ceci est montré dans l'Exemple 11.2, « Mettre en place un index partiel pour exclure les valeurs inintéressantes ». Cette méthode donne les mêmes avantages que la précédente mais empêche l'accès aux valeurs « sans intérêt ». Évidemment, mettre en place des index partiels pour ce genre de scénario nécessite beaucoup de soin et d'expérimentation.

Exemple 11.2. Mettre en place un index partiel pour exclure les valeurs inintéressantes

Si vous avez une table qui contient des commandes facturées et des commandes non facturées, que les commandes non facturées ne prennent qu'une petite fraction de l'espace dans la table, et que ces commandes non facturées sont les plus utilisées, alors vous pouvez améliorer les performances en créant un index limité aux lignes non facturées. La commande pour créer l'index ressemblerait à ceci :

CREATE INDEX index_commandes_nonfacturees ON commandes (no_commande)
    WHERE facturee is not true;

La requête suivante utilise probablement cet index :

SELECT * FROM commandes WHERE facturee is not true AND no_commande < 10000;

Néanmoins, l'index peut aussi être utilisé dans des requêtes qui n'utilisent pas no_commande, comme :

SELECT * FROM commandes WHERE facturee is not true AND montant > 5000.00;

Ceci n'est pas aussi efficace qu'un index partiel sur la colonne montant car le système doit lire l'index en entier. Néanmoins, s'il y a assez peu de commandes non facturées, l'utilisation de cet index partiel pour trouver les commandes non facturées peut être efficace.

Notez que cette requête ne peut pas utiliser cet index :

SELECT * FROM commandes WHERE no_commande = 3501;

La commande 3501 peut faire partie des commandes facturées ou bien des commandes non facturées.


L'Exemple 11.2, « Mettre en place un index partiel pour exclure les valeurs inintéressantes » illustre aussi le fait que la colonne indexée et la colonne utilisée dans le prédicat ne sont pas nécessairement les mêmes. PostgreSQL™ supporte tous les prédicats sur les index partiels, tant que ceux-ci ne portent que sur des champs de la table indexée. Néanmoins, il faut se rappeler que le prédicat doit correspondre aux conditions utilisées dans les requêtes qui sont supposées profiter de l'index. Pour être précis, un index partiel ne peut être utilisé pour une requête que si le système peut reconnaître que la clause WHERE de la requête implique mathématiquement le prédicat de l'index. PostgreSQL™ n'a pas de méthode sophistiquée de démonstration de théorème pour reconnaître que des expressions apparemment différentes sont mathématiquement équivalentes (non seulement une telle méthode générale de démonstration serait extrêmement complexe à créer mais, en plus, elle serait probablement trop lente pour être d'une quelconque utilité). Le système peut reconnaître des implications d'inégalités simples, par exemple « x < 1 » implique « x < 2 » ; sinon, la condition du prédicat doit correspondre exactement à une partie de la clause WHERE de la requête, sans quoi l'index ne sera pas considéré comme utilisable. La correspondance prend place lors de l'exécution de la planification de la requpete, pas lors de l'exécution. À ce titre, les clauses de requêtes à paramètres ne fonctionneront pas avec un index partiel. Par exemple, une requête préparée avec un paramètre pourrait indiquer « x < ? » qui n'impliquerait jamais « x < 2 » pour toutes les valeurs possibles du paramètre.

Le troisième usage possible des index partiels ne nécessite pas que l'index soit utilisé dans des requêtes. L'idée ici est de créer un index unique sur un sous-ensemble de la table, comme dans l'Exemple 11.3, « Mettre en place un index unique partiel ». Ceci permet de mettre en place une unicité parmi le sous-ensemble des lignes de la table qui satisfont au prédicat, sans contraindre les lignes qui n'y satisfont pas.

Exemple 11.3. Mettre en place un index unique partiel

Supposons que nous ayons une table qui décrive des résultats de tests. Nous voulons nous assurer qu'il n'y a qu'une seule entrée « succès » (succes) pour chaque combinaison de sujet et de résultat, mais il peut y avoir un nombre quelconque d'entrées « echec ». Voici une façon de le faire.

CREATE TABLE tests (
    sujet text,
    resultat text,
    succes boolean,
    ...
);

CREATE UNIQUE INDEX contrainte_tests_reussis ON tests (sujet, resultat)
    WHERE succes;

C'est une méthode très efficace pour le faire quand il y a peu de tests réussis et beaucoup de tests en échec.


Enfin, un index partiel peut aussi être utilisé pour passer outre les choix de plan d'exécution de requête du système. Il peut arriver avec certains jeux de données particuliers que le système utilise un index alors qu'il ne devrait vraiment pas le faire. Dans ce cas, on peut mettre en place l'index de telle façon qu'il ne soit pas utilisé pour la requête qui pose problème. Normalement, PostgreSQL™ fait des choix d'usage d'index raisonnables. Par exemple, il les évite pour rechercher les valeurs communes, si bien que l'exemple précédent n'économise que la taille de l'index, il n'est en fait pas nécessaire pour éviter l'utilisation de l'index. En fait, les choix de plan d'exécution grossièrement incorrects doivent être traités comme des bogues, et être transmis à l'équipe de développement.

Gardez à l'esprit que mettre en place un index partiel indique que vous connaissez vos données au moins aussi bien que l'analyseur de requêtes et, en particulier, que vous savez quand un index peut être profitable. Une telle connaissance nécessite de l'expérience et une bonne compréhension du fonctionnement des index de PostgreSQL™. Dans la plupart des cas, les index partiels ne représentent pas un gros gain par rapport aux index classiques.

Vous trouverez plus d'informations sur les index partiels en lisant [ston89b], [olson93] et [seshadri95].