11.5. Index sur des expressions

Une colonne d'index ne correspond pas nécessairement exactement à une colonne de la table associée, mais peut être une fonction ou une expression scalaire calculée à partir d'une ou plusieurs colonnes de la table. Cette fonctionnalité est utile pour obtenir un accès rapide aux tables basé sur les résultat des calculs.

Par exemple, une façon classique de faire des comparaisons indépendantes de la casse est d'utiliser la fonction lower:

SELECT * FROM test1 WHERE lower(col1) = 'value';

Si un index a été défini sur le résultat de lower(col1), cette requête peut l'utiliser. Cet index est créé avec la commande:

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

Si nous avions déclaré cet index UNIQUE, il empêcherait la création de lignes dont la valeur de la colonne col1 ne diffère que par la casse. Ainsi, les index sur les expressions peuvent être utilisés pour vérifier des contraintes qui ne peuvent êtres définies avec une simple contrainte.

Un autre exemple, si vous faites souvent des requêtes comme celle-ci:

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

alors il peut être utile de créer un index comme celui-ci:

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

La syntaxe de la commande CREATE INDEX nécessite normalement de mettre des parenthèses autour de l'expression indexés, comme dans l'exemple précédent. Les parenthèses peuvent être omises quand l'expression est juste un appel de fonction, comme dans le premier exemple.

Les expressions d'index sont relativement coûteuses à calculer, car l'expression doit être recalculée à chaque insertion ou mise à jour de chaque ligne. C'est pourquoi les index basés sur des expressions ne doivent être utilisés que quand les requêtes qui les exécutent sont très fréquentes.