PostgreSQL: Documentation: 13:7.8. AVEC DES Requêtes (Expressions de Table Courantes)

La valeur de base de SELECT dans WITH consiste à décomposer les requêtes compliquées en parties plus simples. Un exemple est:

WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales))SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;

qui affiche les totaux des ventes par produit uniquement dans les principales régions de vente. La clause WITH définit deux instructions auxiliaires nommées regional_sales et top_regions, où la sortie de regional_sales est utilisée dans top_regions et la sortie de top_regions est utilisée dans la requête principale SELECT. Cet exemple aurait pu être écrit sans WITH, mais nous aurions eu besoin de deux niveaux de sous-SELECT imbriqués. C’est un peu plus facile à suivre de cette façon.

Le modificateur RECURSIVE optionnel change WITH d’une simple commodité syntaxique en une fonctionnalité qui accomplit des choses qui ne sont pas autrement possibles en SQL standard. En utilisant RECURSIVE, une requête WITH peut faire référence à sa propre sortie. Un exemple très simple est cette requête pour additionner les entiers de 1 à 100:

WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100)SELECT sum(n) FROM t;

La forme générale d’une requête récursive WITH est toujours un terme non récursif, puis UNION (ou UNION ALL), puis un terme récursif, où seul le terme récursif peut contenir une référence à la propre sortie de la requête. Une telle requête est exécutée comme suit:

Dans l’exemple ci-dessus, la table de travail ne comporte qu’une seule ligne à chaque étape, et elle prend les valeurs de 1 à 100 par étapes successives. À la 100e étape, il n’y a pas de sortie en raison de la clause WHERE, et la requête se termine donc.

Les requêtes récursives sont généralement utilisées pour traiter des données hiérarchiques ou arborescentes. Un exemple utile est cette requête pour trouver toutes les sous-parties directes et indirectes d’un produit, étant donné uniquement un tableau qui montre les inclusions immédiates:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part)SELECT sub_part, SUM(quantity) as total_quantityFROM included_partsGROUP BY sub_part

Lorsque vous travaillez avec des requêtes récursives, il est important de s’assurer que la partie récursive de la requête ne retournera finalement aucun tuples, sinon la requête tournera en boucle indéfiniment. Parfois, l’utilisation de UNION au lieu de UNION ALL peut accomplir cela en supprimant les lignes qui dupliquent les lignes de sortie précédentes. Cependant, souvent, un cycle n’implique pas de lignes de sortie complètement dupliquées: il peut être nécessaire de vérifier seulement un ou quelques champs pour voir si le même point a été atteint auparavant. La méthode standard pour gérer de telles situations consiste à calculer un tableau des valeurs déjà visitées. Par exemple, considérons la requête suivante qui recherche une table graph à l’aide d’un champ link:

WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 1 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link)SELECT * FROM search_graph;

Cette requête tournera en boucle si les relations link contiennent des cycles. Parce que nous avons besoin d’une sortie “profondeur”, le simple fait de changer UNION ALL en UNION n’éliminerait pas la boucle. Au lieu de cela, nous devons reconnaître si nous avons atteint à nouveau la même ligne tout en suivant un chemin particulier de liens. Nous ajoutons deux colonnes path et cycle à la requête sujette aux boucles:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;

En plus d’empêcher les cycles, la valeur du tableau est souvent utile en tant que représentant le “chemin” emprunté pour atteindre une ligne particulière.

Dans le cas général où plusieurs champs doivent être vérifiés pour reconnaître un cycle, utilisez un tableau de lignes. Par exemple, si nous devions comparer les champs f1 et f2:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || ROW(g.f1, g.f2), ROW(g.f1, g.f2) = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;

Une astuce utile pour tester les requêtes lorsque vous n’êtes pas certain qu’elles pourraient être en boucle consiste à placer un LIMIT dans la requête parent. Par exemple, cette requête tournerait en boucle pour toujours sans le LIMIT:

WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t)SELECT n FROM t LIMIT 100;

Cela fonctionne car l’implémentation de PostgreSQL n’évalue que le nombre de lignes d’une requête WITH récupérées par la requête parent. L’utilisation de cette astuce en production n’est pas recommandée, car d’autres systèmes peuvent fonctionner différemment. De plus, cela ne fonctionnera généralement pas si vous faites trier les résultats de la requête récursive par la requête externe ou si vous les joignez à une autre table, car dans de tels cas, la requête externe essaiera généralement d’extraire de toute façon toute la sortie de la requête WITH.

Une propriété utile des requêtes WITH est qu’elles ne sont normalement évaluées qu’une seule fois par exécution de la requête parent, même si elles sont référencées plus d’une fois par la requête parent ou les requêtes frères WITH. Ainsi, des calculs coûteux nécessaires à plusieurs endroits peuvent être placés dans une requête WITH pour éviter le travail redondant. Une autre application possible est d’empêcher les évaluations multiples indésirables des fonctions avec des effets secondaires. Cependant, l’autre côté de la médaille est que l’optimiseur n’est pas en mesure de pousser les restrictions de la requête parent vers une requête WITH à références multiples, car cela pourrait affecter toutes les utilisations de la sortie de la requête WITH alors qu’elle ne devrait en affecter qu’une seule. La requête WITH référencée en plusieurs fois sera évaluée comme écrite, sans suppression des lignes que la requête parent pourrait supprimer par la suite. (Mais, comme mentionné ci-dessus, l’évaluation peut s’arrêter tôt si la ou les références à la requête ne nécessitent qu’un nombre limité de lignes.)

Cependant, si une requête WITH est non récursive et sans effets secondaires (c’est-à-dire qu’il s’agit d’une requête SELECT ne contenant aucune fonction volatile), elle peut être pliée dans la requête parent, permettant une optimisation conjointe des deux niveaux de requête. Par défaut, cela se produit si la requête parent fait référence à la requête WITH une seule fois, mais pas si elle fait référence à la requête WITH plusieurs fois. Vous pouvez remplacer cette décision en spécifiant MATERIALIZED pour forcer le calcul séparé de la requête WITH, ou en spécifiant NOT MATERIALIZED pour la forcer à être fusionnée dans la requête parent. Ce dernier choix risque de dupliquer le calcul de la requête WITH, mais il peut toujours générer une économie nette si chaque utilisation de la requête WITH ne nécessite qu’une petite partie de la sortie complète de la requête WITH.

Un exemple simple de ces règles est

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w WHERE key = 123;

Cette requête WITH sera pliée, produisant le même plan d’exécution que

SELECT * FROM big_table WHERE key = 123;

En particulier, s’il y a un index sur key, il sera probablement utilisé pour récupérer uniquement les lignes ayant key = 123. D’autre part, dans

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

la requête WITH sera matérialisée, produisant une copie temporaire de big_table qui sera ensuite jointe à elle—même – sans bénéficier d’aucun index. Cette requête sera exécutée beaucoup plus efficacement si elle est écrite sous la forme

WITH w AS NOT MATERIALIZED ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

afin que les restrictions de la requête parent puissent être appliquées directement aux analyses de big_table.

Un exemple où NOT MATERIALIZED pourrait être indésirable est

WITH w AS ( SELECT key, very_expensive_function(val) as f FROM some_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

Ici, la matérialisation de la requête WITH garantit que very_expensive_function n’est évalué qu’une seule fois par ligne de table, pas deux fois.

Les exemples ci-dessus montrent uniquement que WITH est utilisé avec SELECT, mais il peut être attaché de la même manière à INSERT, UPDATE ou DELETE. Dans chaque cas, il fournit effectivement des tables temporaires auxquelles on peut faire référence dans la commande principale.