Fonctions postgresql, heuristiques

Reprenons l'exemple d'hier, mais cette fois-ci avec 2 tables de 10M lignes chacune, et une requête un tout petit peu plus rigolote, puisqu'elle limite la jointure aux 10 premières lignes de la première table. Pas de surprises, le planificateur de requêtes voit que la limite permet d'aller chercher les éléments de la deuxième table via leur index, et propose une requête efficace, avec un temps d'exécution de 12 ms.

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

select *
from (select * from data1 limit 10) d1
join data2 d2 on d1.a = d2.a

Soyons un peu plus pervers, et prenons maintenant la limite depuis une autre table, empêchant ainsi le planificateur de voir que d1 peut être en grande partie ignorée. Ouch! Le plan en prend un coup, et le temps d'exécution explose à presque 4 secondes.

create table data_limit(l integer not null);
insert into data_limit values(10);

select *
from (select * from data1 limit (select * from data_limit)) d1
join data2 d2 on d1.a = d2.a

Si l'on sait que notre limite va être la plupart du temps petite, comment peut-on aider notre planificateur à choisir la bonne requête? Une possibilité est de passer par une fonction, et de lui passer le "hint" qui va bien, c'est à dire en indiquant le nombre de lignes auxquelles l'on s'attend via le mot-clé "rows" (notez que par défaut, Postgres suppose que la fonction renvoie 1000 lignes). Et tout d'un coup, le plan redevient raisonnable, et la requête rapide:

create function get_limited_data1()
returns table(a integer)
rows 10
as
$$
 select * from data1 limit (select * from data_limit);
$$ language sql;

select *
from get_limited_data1() d1
join data2 d2 on d1.a = d2.a

Il peut être utile de passer par une fonction pour ajuster les heuristiques du planificateur de requêtes.

Attention cependant! Si la fonction est inlinée, le planificateur perdra ses heuristiques, et pourra revenir à un plan moins efficace:

create function get_limited_data1()
returns table(a integer)
stable
rows 10
as
$$
 select * from data1 limit (select * from data_limit);
$$ language sql;

select *
from get_limited_data1() d1
join data2 d2 on d1.a = d2.a

Forcer la volatilité la plus haute permet d'empêcher l'inline de la fonction, et donc de forcer les heuristiques du planificateur.