Postgresql 8.4 et les requêtes récursives

La fonctionnalité de Postgres 8.4 que je préfère, c'est les requêtes récursives. Voilà vraiment quelque chose qui va permettre de résoudre bon nombre de problèmes de manière beaucoup plus élégante, et notamment tout ce qui concerne les arbres, les graphes, et les hiérarchies de toutes sortes.

Voici par exemple un arbre où chaque élément pointe vers son parent.

create table h(entry int not null, parent int null);

insert into h values(1, null);
insert into h values(2, 1);
insert into h values(3, 1);
insert into h values(4, 1);
insert into h values(5, 1);
insert into h values(6, 2);
insert into h values(7, 2);
insert into h values(8, 6);
insert into h values(9, 8);
insert into h values(10, 4);

Maintenant, répondons à la question: Quels sont tous les éléments enfants de l'élément 2? Avec le SQL récursif, c'est trivial:

with recursive deep(n) as(
 select entry from h where entry = 2
union
 select entry from h join deep on h.parent = deep.n
)
select * from deep;

Le premier élément de l'union est l'amorce de la récursion: l'on démarre donc avec l'élément 2. Le second élément est la récursion en elle-même: l'on retourne l'ensemble des éléments qui sont parents de l'élément n.

La documentation contient quelques exemples beaucoup plus compliqués, où l'on explore des graphes cycliques, avec un SQL qui devient franchement ésotérique. Mais pour des cas simples, c'est un très bon moyen de se débarrasser des couches de pl/pgsql ou autres, et de revenir en pur SQL, ce qui pourrait entre autres améliorer les performances en permettant au planificateur de travailler directement sur toutes les tables.

A propos de planificateur, le nouveau pgadmin3 (malheureusement pas encore dans Squeeze, mais cela ne saurait tarder) montre de bien jolies choses sur ces requêtes récursives. Voici donc l'explain de ma requête.



En faisant quelques tests supplémentaires, je me suis rendu compte que Postgres utilisait gentiment les index qu'on lui donnait sur les colonnes "entry" et "parent", ce qui permettait de gagner environ 25% de temps sur de très grosses requêtes (arbre de 100 000 éléments). Elle est pas belle, la vie?