Fonctions postgresql, inlining

Regardons d'un peu plus près les fonctions Postgres et leurs conséquences sur les performances. Créons deux tables d'entiers munies chacune d'un index, l'une faisant 2M de lignes, et l'autre seulement 10.

create table data1(a integer not null);
insert into data1 select generate_series(1, 10000000, 5);
create index data1_idx on data1(a);
create table data2(a integer not null);
insert into data2 select generate_series(1, 10);
create index data2_idx on data2(a);

Joignons les 2 tables, et oh, miracle, le planificateur de requête décide d'utiliser l'index sur la grande table. Temps d'exécution, 12 ms.

select *
from data1 d1 join data2 d2 on d1.a = d2.a;

Maintenant, utilisons plutôt des fonctions, qui retournent le contenu de la table.

create function get_data1() 
returns table(a integer)
as
$$
 select * from data1;
$$ language sql;

create function get_data2()
returns table(a integer)
as
$$
 select * from data2;
$$ language sql;

select *
from get_data1() d1 join get_data2() d2 on d1.a = d2.a;

Le plan est beaucoup moins sympathique: le planificateur ayant perdu toute information sur les index disponibles ne peut que faire une jointure bourrine, pour une exécution qui dure 2.3 secondes. Comment arranger cela?

Une fonction Postgres possède un certain nombre d'attributs qu'il est possible d'ajuster pour donner plus d'information au planificateur de tâches. L'un de ces attributs est la volatilité de la fonction. Par défaut, toute fonction est volatile, ce qui empêche le planificateur de faire aucune optimisation, mais une fonction ne contenant que des "select" peut être passé à "stable", ce qui permet au planificateur de l'inliner. Le résultat est sans appel:

create function get_data1() 
returns table(a integer)
stable
as
$$
 select * from data1;
$$ language sql;

create function get_data2()
returns table(a integer)
stable
as
$$
 select * from data2;
$$ language sql;

select *
from get_data1() d1 join get_data2() d2 on d1.a = d2.a;

L'on est revenu au premier plan, car le planificateur a tout inliné et a donc pu utiliser sa connaissance des index pour revenir au temps d'exécution initial.

Il est la plupart du temps extrêmement utile d'attribuer la bonne volatilité à une fonction afin de permettre au planificateur de requête de l'optimiser au mieux.