Il est (assez) bien connu qu'il faut se méfier de l'application de fonctions sur des colonnes dans les clauses de restriction des requêtes (WHERE). Si vous ne pouvez l'éviter alors vous avez la possibilité de créer un index sur l'expression.
Mais est-il vraiment neutre d'utiliser des fonctions dans les clauses WHERE quand elles ne sont PAS appliquées sur des colonnes ?
Tout dépend en fait de la fonction et du contexte. Démonstration :
select version();
version
--------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.4 (Ubuntu 10.4-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
create table geants(idge integer generated by default as identity, nmge text);
CREATE TABLE
insert into geants(nmge) select substr(md5(random()::text),1,4) from generate_series(1,20000000);
INSERT 0 20000000
vacuum analyze geants;
VACUUM
\ef lower(text)
CREATE OR REPLACE FUNCTION pg_catalog.lower(text)
RETURNS text
LANGUAGE internal
IMMUTABLE PARALLEL SAFE STRICT
AS $function$lower$function$
create or replace function lower_i(arg text) returns text as
$$
begin
return lower(arg);
end;
$$ language plpgsql immutable;
create or replace function lower_s(arg text) returns text as
$$
begin
return lower(arg);
end;
$$ language plpgsql stable;
create or replace function lower_v(arg text) returns text as
$$
begin
return lower(arg);
end;
$$ language plpgsql;
Nous allons considérer la fonction lower. Elle est IMMUTABLE à l'origine mais nous allons l'utiliser pour créer des fonctions personnelles marquées respectivement IMMUTABLE, STABLE et VOLATILE.
Les fonctions marquées IMMUTABLE renvoient toujours le même résultat lorsqu'on les appelle avec les mêmes arguments. A arguments constants, le moteur PostgreSQL peut toujours, sans se poser davantage de questions, remplacer directement un appel à une fonction IMMUTABLE par son résultat.
Les fonctions marquées STABLE renvoient toujours le même résultat lorsqu'on les appelle avec les mêmes arguments pour une instruction donnée. Le moteur PostgreSQL a la possibilité d'optimiser le nombre d'appels à la fonction STABLE pour une instruction donnée.
Les fonctions marquées VOLATILE peuvent renvoyer des résultats différents même lorsqu'on les appelle avec les mêmes arguments pour une instruction donnée. Le moteur PostgreSQL n'a aucune possibilité d'optimiser le nombre d'appels à la fonction VOLATILE pour une instruction donnée.
La table geants n'a aucun index sur nmge. Quelles sont les différence de performance dans l'utilisation des 4 fonctions lower lorsqu'elles sont utilisées sur une chaîne de caractères au niveau d'un test d'égalité de la clause WHERE ?
explain select count(*) from geants where nmge = 'e999';
QUERY PLAN
----------------------------------------------------------------------------------------
Finalize Aggregate (cost=213276.20..213276.21 rows=1 width=8)
-> Gather (cost=213275.98..213276.19 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=212275.98..212275.99 rows=1 width=8)
-> Parallel Seq Scan on geants (cost=0.00..212275.67 rows=127 width=0)
Filter: (nmge = 'e999'::text)
select count(*) from geants where nmge = 'e999';
count
-------
309
(1 ligne)
Temps : 516,584 ms
...
Temps : 519,195 ms
...
Temps : 537,027 ms
explain select count(*) from geants where nmge = lower('E999');
QUERY PLAN
----------------------------------------------------------------------------------------
Finalize Aggregate (cost=213273.96..213273.97 rows=1 width=8)
-> Gather (cost=213273.74..213273.95 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=212273.74..212273.75 rows=1 width=8)
-> Parallel Seq Scan on geants (cost=0.00..212273.43 rows=127 width=0)
Filter: (nmge = 'e999'::text)
select count(*) from geants where nmge = lower('E999');
count
-------
309
(1 ligne)
Temps : 534,577 ms
...
Temps : 541,270 ms
...
Temps : 534,577 ms
explain select count(*) from geants where nmge = lower_i('E999');
QUERY PLAN
-------------------------------------------------------------------
Aggregate (cost=358104.39..358104.40 rows=1 width=8)
-> Seq Scan on geants (cost=0.00..358103.62 rows=305 width=0)
Filter: (nmge = 'e999'::text)
select count(*) from geants where nmge = lower_i('E999');
count
-------
309
(1 ligne)
Durée : 1512,547 ms (00:01,513)
...
Durée : 1403,810 ms (00:01,404)
...
Durée : 1419,648 ms (00:01,420)
explain select count(*) from geants where nmge = lower_s('E999');
QUERY PLAN
--------------------------------------------------------------------
Aggregate (cost=5357996.89..5357996.90 rows=1 width=8)
-> Seq Scan on geants (cost=0.00..5357996.12 rows=305 width=0)
Filter: (nmge = lower_s('E999'::text))
select count(*) from geants where nmge = lower_s('E999');
count
-------
309
(1 ligne)
Durée : 18685,972 ms (00:18,686)
...
Durée : 18443,407 ms (00:18,443)
...
Durée : 18415,446 ms (00:18,415)
explain select count(*) from geants where nmge = lower_v('E999');
QUERY PLAN
--------------------------------------------------------------------
Aggregate (cost=5357996.89..5357996.90 rows=1 width=8)
-> Seq Scan on geants (cost=0.00..5357996.12 rows=308 width=0)
Filter: (nmge = lower_v('E999'::text))
select count(*) from geants where nmge = lower_v('E999');
count
-------
309
(1 ligne)
Durée : 20468,347 ms (00:20,468)
...
Durée : 20651,987 ms (00:20,652)
...
Durée : 20355,000 ms (00:20,355)
Dans tous les cas, un balayage complet de la table geants est effectué. Et pourtant que de différences dans les performances.
Sans appel à une fonction, nous obtenons un temps de 0s5.
La fonction LOWER originale donne des résultats équivalents (0s5 environ), elle est marquée IMMUTABLE mais aussi PARALLEL SAFE, sûre pour le parallélisme.
La fonction LOWER_I marquée IMMUTABLE donne des temps autour de 1s5 mais serait quasi aussi efficace que la fonction LOWER originale si nous lui autorisions le parallélisme.
La fonction LOWER_S marquée STABLE donne des temps autour des 19s. En théorie, il y a possibilité d'optimisation mais ici ce n'est pas vraiment le cas. Les résultats sont quasi aussi mauvais que ceux obtenus avec la fonction LOWER_V marquée VOLATILE (autour des 20s).
A présent que se passe-t-il si un index est créé sur la colonne nmge de geants ?
create index on geants(nmge);
CREATE INDEX
explain select count(*) from geants where nmge = 'e999';
QUERY PLAN
-------------------------------------------------------------------------------------------
Aggregate (cost=10.54..10.55 rows=1 width=8)
-> Index Only Scan using geants_nmge_idx on geants (cost=0.44..9.78 rows=305 width=0)
Index Cond: (nmge = 'e999'::text)
select count(*) from geants where nmge = 'e999';
count
-------
309
(1 ligne)
Temps : 0,862 ms
...
Temps : 1,146 ms
...
Temps : 0,861 ms
explain select count(*) from geants where nmge = lower('E999');
QUERY PLAN
-------------------------------------------------------------------------------------------
Aggregate (cost=10.54..10.55 rows=1 width=8)
-> Index Only Scan using geants_nmge_idx on geants (cost=0.44..9.78 rows=305 width=0)
Index Cond: (nmge = 'e999'::text)
select count(*) from geants where nmge = lower('E999');
count
-------
309
(1 ligne)
Temps : 1,003 ms
...
Temps : 0,864 ms
...
Temps : 0,924 ms
explain select count(*) from geants where nmge = lower_i('E999');
QUERY PLAN
-------------------------------------------------------------------------------------------
Aggregate (cost=10.54..10.55 rows=1 width=8)
-> Index Only Scan using geants_nmge_idx on geants (cost=0.44..9.78 rows=305 width=0)
Index Cond: (nmge = 'e999'::text)
select count(*) from geants where nmge = lower_i('E999');
count
-------
309
(1 ligne)
Temps : 1,117 ms
...
Temps : 1,016 ms
...
Temps : 0,957 ms
explain select count(*) from geants where nmge = lower_s('E999');
QUERY PLAN
--------------------------------------------------------------------------------------------
Aggregate (cost=10.79..10.80 rows=1 width=8)
-> Index Only Scan using geants_nmge_idx on geants (cost=0.69..10.03 rows=305 width=0)
Index Cond: (nmge = lower_s('E999'::text))
select count(*) from geants where nmge = lower_s('E999');
count
-------
309
(1 ligne)
Temps : 0,919 ms
...
Temps : 0,904 ms
...
Temps : 0,847 ms
explain select count(*) from geants where nmge = lower_v('E999');
QUERY PLAN
--------------------------------------------------------------------
Aggregate (cost=5358109.77..5358109.78 rows=1 width=8)
-> Seq Scan on geants (cost=0.00..5358109.00 rows=308 width=0)
Filter: (nmge = lower_v('E999'::text))
select count(*) from geants where nmge = lower_v('E999');
count
-------
309
(1 ligne)
Durée : 19949,656 ms (00:19,950)
...
Durée : 19508,942 ms (00:19,509)
...
Durée : 19482,460 ms (00:19,482)
Cette fois, utiliser une constante ou les fonctions lower (IMMUTABLE & PARALLEL SAFE), lower_i (IMMUTABLE) et lower_s (STABLE) donne des résultats similaires au niveau des performances.
Seule la fonction lower_v marquée VOLATILE ne permet aucune optimisation. L'index n'est pas utilisé.
Conclusion
Il faut être extrêmement vigilant dans la définition et l'utilisation des fonctions pour obtenir des performances optimales. Mais il ne faut bien sûr PAS marquer une fonction STABLE si elle est VOLATILE ou IMMUTABLE alors qu’elle est STABLE.
Un cas classique est celui des fonctions marquées STABLE & PARALLEL SAFE to_timestamp et to_date. Elle sont largement utilisées par les développeurs ayant travaillé avec Oracle Database mais ne donnent PAS des performances équivalentes à une constante si vous les utilisez pour filtrer. La solution est de ne pas les utiliser dans ce contexte et de privilégier une syntaxe directe ISO 8601 (e.g timestamp '2018-05-31 10:41:00.522')
Seules les fonctions marquées IMMUTABLE & PARALLEL SAFE garantissent dans tous les cas des performances aussi bonnes qu'une constante. Sinon, les performances sont plus ou moins dégradées selon les cas.
Les avancées du partitionnement avec PostgreSQL 11 en ce qui concerne l'élimination de partitions lors de l'exécution des requêtes vont nous fournir de nouveaux cas de vigilance sur le sujet.
Mise à jour : 31/05/2018