Où l'on reparle des heuristiques

J'ai eu l'occasion récemment de me remettre aux heuristiques des fonctions Postgresql, que j'avais mentionnées dans un post précédent

Voyons le problème: l'on a un certain nombre de valeurs que l'on veut rechercher dans une grande table. On insère donc ces valeurs dans une table temporaire, et l'on fait une jointure. En fonction du nombre de valeurs dans la table temporaire, le planificateur de tâches va soit utiliser l'index, soit ordonner la table temporaire et faire un merge join. Sauf que depuis l'application, l'on prépare le plan à la première exécution, et l'on a donc aucune certitude sur le plan choisi. Pire, souvent les premières requêtes chargent un grand nombre de valeurs, et optimisent donc le plan pour ce cas, alors qu'ensuite le système se stabilise sur de toutes petites recherches, qui prennent donc beaucoup plus de temps.

create table data(i integer not null);
insert into data select generate_series(1, 1000000);
create index data_idx on data(i);

create temp table search(i integer not null);
insert into search select generate_series(1, 1000);
analyze search;
select d.i
from data d
join search s on d.i = s.i

truncate search;
insert into search select generate_series(1, 1);
analyze search;
select d.i
from data d
join search s on d.i = s.i

L'on pourrait empêcher la préparation du plan, ce qui oblige l'application à différencier entre les requêtes que l'on peut préparer et les autres. Mais il y a aussi une solution: doucement orienter le planificateur de requêtes vers le plan le plus efficace en moyenne, à l'aide d'une fonction et de hints bien choisis.

create or replace function get_search() 
returns table(i integer) 
rows 1 
volatile as
$$
 select * from search;
$$ language sql;

truncate search;
insert into search select generate_series(1, 100000);
analyze search;
select d.i
from data d
join get_search() s on d.i = s.i

L'on aura beau mettre autant de lignes que l'on veut dans la table search, le plan passera toujours par l'index de la grande table: