Lorsque vous trouvez dans un plan dexécution un balayage complet de table alors que vous attendiez un passage par un index, cest peut-être que le passage par lindex est impossible. Un exemple souvent donné est celui dune recherche sur les noms en minuscule alors que ce sont les noms sans transformation qui sont indexés. Il serait plus simple deffectuer les transformations lors des insertions mais ce nest pas toujours possible ni même souhaitable.
Dans ce cas, PostgreSQL permet, comme Oracle Database, de créer un index sur une expression (expression-based indexes ou function-based indexes), cest à dire sur les résultats dune expression ou dune fonction appliquée sur une ou plusieurs colonnes. Cette fonction doit être DETERMINISTIC pour Oracle, IMMUTABLE pour PostgreSQL. La possibilité de créer un index basé sur une fonction dépend en effet de son degré de volatilité, notion décrite avec précision dans la documentation PostgreSQL. En résumé, IMMUTABLE signifie que la fonction ne modifiera pas la base de données lorsquelle est appelée ET quelle donnera toujours le même résultat si les mêmes arguments sont utilisés.
Si une fonction fournie par défaut avec PostgreSQL nest pas marquée IMMUTABLE, cest quil y a une excellente raison. Ne modifiez PAS son degré de volatilité mais créez votre propre fonction, en vous assurant bien quelle répond réellement aux critères pour être IMMUTABLE.
De manière générale, même si vous ne comptez pas créer dindex, pensez toujours à renseigner explicitement le degré de volatilité lorsque vous créez une fonction. En dehors même de laspect indexation, la règle est toujours de donner le maximum dinformations à PostgreSQL pour obtenir les meilleures performances.
Démonstration avec PostgreSQL 10 :
Comme prévu, lindex créé directement sur la colonne nmg de geants ne permettait pas déviter le balayage complet de la table (Seq Scan on geants).
Les fonctions replace et lower étant marquées IMMUTABLE, il est possible de créer un index basé sur ces fonctions. Lindex créé, geants_lower_idx1, est alors utilisé par le planner et les temps dexécution se révèlent dans lexemple effectivement meilleurs (6ms contre 91ms).
Un point très important est également à noter. Avant la création de lindex, le nombre de lignes estimées après filtrage était 225 (rows=225). Après la création, ce chiffre passe à 4588 lignes (rows=4588). Cest une bien meilleure estimation puisque le chiffre réel est 4569.
Les statistisques obtenues par ANALYZE sur une table sont calculées sur les colonnes brutes mais aussi sur les expressions lorsquil existe des expression-based index. Tout enrichissement des statistiques donne au planner (optimiseur) une vision plus conforme de la réalité. Même si lexpression-based index nest pas utilisé, sa présence peut donc avoir un effet positif dans diverses situations, par exemple dans le cadre de jointures afin de choisir la meilleure méthode etc.