Postgresql est un système de base de données relationnel libre, gratuit et extrêmement performant. Dans bien des cas, il se compare favorablement avec les plus onéreuses des solutions propriétaires comme Oracle par exemple.
La norme SQL:1999 définit l’utilisation des CTE (recursive common table expressions) et rend ce langage turing-complêt. Postgresql supporte les CTE depuis sa version 8.4.
Grâce aux CTE, il est possible de réaliser en SQL déclaratif bien des choses impossibles auparavant : parcourir des arbres par exemple, mais aussi réaliser un quicksort ou résoudre un sudoku :-)
Une autre utilisation particulière, mais très intéressante, est la capacité d’accélérer spéctaculairement les select distinct sur une colonne indexée...
Sur un cas concret d’une table d’articles contenant 68 799 653 enregistrements répartis à peu près également entre différentes familles, connaître la liste distincte des familles s’obtient facilement avec la requête suivante :
select distinct famille from articles;
13 familles utilisées sont retournées.
Mais la requête a pris 44s 153ms, la base étant stockée sur un disque SSD relativement rapide. C’est long...
En effet, si on regarde le plan d’exécution, on constate qu’une lecture séquentielle des 68 millions d’enregistrements a été nécessaire :
Alors, comment obtenir ce résultat plus vite ? L’idée est d’utiliser l’index présent sur la colonne famille pour en retirer les différentes familles existantes, plutôt que de regarder chaque ligne de la table. Pour cela, on va utiliser une CTE recursive :
WITH RECURSIVE familles_triees AS (
(SELECT famille FROM articles ORDER BY famille LIMIT 1) -- parentheses required
UNION ALL
SELECT (SELECT famille FROM articles WHERE famille > familles_triees.famille ORDER BY famille LIMIT 1)
FROM familles_triees
WHERE familles_triees.famille IS NOT NULL
)
SELECT famille FROM familles_triees WHERE famille IS NOT NULL;Le même résultat est retourné (mes 13 familles d’articles), mais cette fois, il aura fallu 130ms ! et pour cause, le plan d’exécution suivi ne lit plus les données de notre volumineuse table, mais se contente d’en parcourir l’index btree, qui donne facilement accès à cette information :
D’autres variations autour de cette technique dite de "loose indexscan" peuvent être trouvées dans le wiki de postgresql.