11.7. Index partiels

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.

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.

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.