PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 12.18 » Langage SQL » Index » Index d'expressions

11.7. Index d'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 en utilisant les résultats de 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) = 'valeur';

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

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

Si l'index est déclaré UNIQUE, il empêche la création de lignes dont les valeurs de la colonne col1 ne diffèrent que par la casse, ainsi que celle de lignes dont les valeurs de la colonne col1 sont identiques. Ainsi, les index d'expressions peuvent être utilisés pour appliquer des contraintes qui ne peuvent être définies avec une simple contrainte d'unicité.

Autre exemple. Lorsque des requêtes comme  :

SELECT * FROM personnes WHERE (prenom || ' ' || nom) = 'Jean Dupont';

sont fréquentes, alors il peut être utile de créer un index comme :

CREATE INDEX personnes_noms ON personnes ((prenom || ' ' || nom));

La syntaxe de la commande CREATE INDEX nécessite normalement de mettre des parenthèses autour de l'expression indexée, comme dans l'exemple précédent. Les parenthèses peuvent être omises quand l'expression est un simple 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 non-HOT de ligne. Néanmoins, les expressions d'index ne sont pas recalculées lors d'une recherche par index, car elles sont déjà stockées dans l'index. Dans les deux exemples ci-dessus, le système voit la requête comme un WHERE colonne_indexée = 'constante'. De ce fait, la recherche est aussi rapide que toute autre requête d'index. Ainsi, les index d'expressions sont utiles lorsque la rapidité de recherche est plus importante que la rapidité d'insertion et de mise à jour.