Indexation sur expression

      Lorsque vous trouvez dans un plan d’exécution un balayage complet de table alors que vous attendiez un passage par un index, c’est peut-être que le passage par l’index est impossible. Un exemple souvent donné est celui d’une recherche sur les noms en minuscule alors que ce sont les noms sans transformation qui sont indexés. Il serait plus simple d’effectuer les transformations lors des insertions mais ce n’est 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), c’est à dire sur les résultats d’une expression ou d’une 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 lorsqu’elle est appelée ET qu’elle donnera toujours le même résultat si les mêmes arguments sont utilisés.
      Si une fonction fournie par défaut avec PostgreSQL n’est pas marquée IMMUTABLE, c’est qu’il y a une excellente raison. Ne modifiez PAS son degré de volatilité mais créez votre propre fonction, en vous assurant bien qu’elle 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 d’index, pensez toujours à renseigner explicitement le degré de volatilité lorsque vous créez une fonction. En dehors même de l’aspect indexation, la règle est toujours de donner le maximum d’informations à PostgreSQL pour obtenir les meilleures performances.
      Démonstration avec PostgreSQL 10 :

select version(); version ---------------------------------------------------------------------------------------------------------------- PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit (1 ligne) CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE EXTENSION create table geants as with recursive serie(i, r) as (select 55000, random() UNION ALL select i - 1, random() from serie where i > 10001) select 55001 - i idg, uuid_generate_v4() idgu, case when r < 0.025 then 'gerbi' when r < 0.05 then 'gérbi' when r < 0.075 then 'Gerbi' when r < 0.10 then 'Gérbi' else md5(random()::text) end nmg, current_timestamp - (ceil(i/3) || ' minutes')::interval + (trunc(random() * 100 + 1) || ' days')::interval dtn, 200 + (trunc(random() * 200 + 1)) taille, upper(md5(random()::text)) devise, case when random() < 0.001 then true else false end berserk from serie; SELECT 45000 analyze geants; ANALYZE create index on geants(nmg); CREATE INDEX explain select avg(taille) from geants where lower(replace(nmg, 'é', 'e')) = 'gerbi'; QUERY PLAN ----------------------------------------------------------------------------- Aggregate (cost=1552.07..1552.08 rows=1 width=8) -> Seq Scan on geants (cost=0.00..1551.50 rows=225 width=8) Filter: (lower(replace(nmg, 'é'::text, 'e'::text)) = 'gerbi'::text) (3 lignes) \timing Chronométrage activé. select avg(taille) from geants where lower(replace(nmg, 'é', 'e')) = 'gerbi'; avg ------------------ 301.345370978332 (1 ligne) Temps : 90,763 ms \sf replace CREATE OR REPLACE FUNCTION pg_catalog.replace(text, text, text) RETURNS text LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS $function$replace_text$function$ \sf lower(text) CREATE OR REPLACE FUNCTION pg_catalog.lower(text) RETURNS text LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS $function$lower$function$ create index on geants(lower(replace(nmg, 'é', 'e'))); CREATE INDEX analyze geants; ANALYZE explain select avg(taille) from geants where lower(replace(nmg, 'é', 'e')) = 'gerbi'; QUERY PLAN ------------------------------------------------------------------------------------------- Aggregate (cost=1019.73..1019.74 rows=1 width=8) -> Bitmap Heap Scan on geants (cost=163.97..1008.26 rows=4588 width=8) Recheck Cond: (lower(replace(nmg, 'é'::text, 'e'::text)) = 'gerbi'::text) -> Bitmap Index Scan on geants_lower_idx1 (cost=0.00..162.82 rows=4588 width=0) Index Cond: (lower(replace(nmg, 'é'::text, 'e'::text)) = 'gerbi'::text) (5 lignes) select count(*) from geants where lower(replace(nmg, 'é', 'e')) = 'gerbi'; count ------- 4569 (1 ligne) select avg(taille) from geants where lower(replace(nmg, 'é', 'e')) = 'gerbi'; avg ------------------ 301.345370978332 (1 ligne) Temps : 5,634 ms


      Comme prévu, l’index 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. L’index créé, geants_lower_idx1, est alors utilisé par le planner et les temps d’exécution se révèlent dans l’exemple effectivement meilleurs (6ms contre 91ms).
      Un point très important est également à noter. Avant la création de l’index, 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). C’est 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 lorsqu’il existe des expression-based index. Tout enrichissement des statistiques donne au planner (optimiseur) une vision plus conforme de la réalité. Même si l’expression-based index n’est 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.

Mise à jour : 12/10/2017