Modifications importantes apportées à CTE dans PostgreSQL 12

WITH w AS NOT MATERIALIZED ( SELECT * FROM very_very_big_table ) SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref WHERE w2.key = 123; 

Aujourd'hui, une validation est tombée dans le référentiel PostgreSQL, ce qui vous permet de contrôler le comportement du traitement des sous-requêtes CTE, à savoir: vous pouvez désormais indiquer explicitement si la sous-requête sera matérialisée séparément ou si elle sera exécutée dans le cadre d'une grande requête.

Cela ira dans PostgreSQL 12 et c'est un gros problème. Voyons pourquoi

Les programmeurs adorent CTE car il peut améliorer considérablement la lisibilité du code. Eh bien, en effet, certaines requêtes analytiques peuvent fonctionner avec des dizaines de tables et divers regroupements et filtres. Pour écrire tout cela dans une seule grande requête - vous garantissez d'obtenir quelque chose d'illisible. Par conséquent, en utilisant l'opérateur WITH , nous décrivons séquentiellement, dans de petites sous-requêtes (auxquelles un nom est lisible par l'homme), la logique du travail, puis nous produisons le résultat. Très confortable.

Plus précisément, ce serait très pratique si ce n’était pour rien: le PostgreSQL actuel exécute ces sous-requêtes séparément les unes des autres, les matérialise (écrit le résultat dans une table temporaire). Cela peut entraîner un ralentissement important par rapport à un gros monstre illisible. Surtout si les sous-requêtes CTE renvoient des millions de lignes.

Cependant, il y a des situations où une telle exécution séparée fonctionne pour le bien: il y a une telle astuce d'optimisation quand il vaut mieux exécuter une partie d'une requête complexe séparément, mais le postgres ne le comprend pas tout seul. Ensuite, nous supprimons cette partie dans la sous-requête CTE.

En général, les situations sont différentes, c'est pourquoi Postgres 12 a fait un commit en ajoutant des mots clés MATERIALIZED et NOT MATERIALIZED , qui indiquent respectivement s'il faut matérialiser la requête ou en ligne.

De plus, le comportement par défaut a changé. Maintenant, la sous-requête CTE sera en ligne par défaut si son résultat est utilisé une fois. Sinon, il se matérialisera comme avant.