CTE

Ce que c'est

Une CTE, abréviation de common table expression, est une requête
nommée dans une clause WITH au début d'une instruction. Le reste de
l'instruction peut alors lire ses lignes comme si elles venaient
d'une table. Une instruction peut définir plusieurs CTE, chacune
pouvant lire celles nommées avant elle, et la requête finale, au bas
de l'instruction, les rassemble. Le manuel PostgreSQL les décrit
comme des « tables temporaires qui n'existent que pour une
requête ». L'image est juste, avec une réserve : rien n'est stocké
sous ce nom, et l'instruction suivante ne peut pas y accéder. Chaque
CTE peut être un SELECT ou, dans PostgreSQL, un INSERT, UPDATE,
DELETE ou MERGE ; l'instruction à laquelle la clause WITH est
attachée peut aussi être de l'un de ces types.

Une question comme « quels compteurs ont enregistré une consommation
supérieure à trois fois la normale sur une journée des 30 derniers
jours » se divise naturellement en étapes :

```sql
WITH daily AS (
  SELECT meter_id, read_at::date AS day, sum(kwh) AS kwh
  FROM readings
  WHERE read_at >= current_date - 30
  GROUP BY meter_id, read_at::date
),
typical AS (
  SELECT meter_id, avg(kwh) AS avg_kwh
  FROM daily
  GROUP BY meter_id
)
SELECT d.meter_id, d.day, d.kwh, t.avg_kwh
FROM daily AS d
JOIN typical AS t USING (meter_id)
WHERE d.kwh > 3 * t.avg_kwh
ORDER BY d.day, d.meter_id;
```

La première étape totalise les relevés quotidiens de chaque
compteur, la deuxième fait la moyenne de ces totaux par compteur, et
la dernière compare chaque journée à la moyenne de son propre
compteur. On peut obtenir la même réponse avec des sous-requêtes
imbriquées, mais le calcul des totaux quotidiens apparaîtrait alors
deux fois dans le texte, l'une des occurrences étant imbriquée dans
l'autre, et la lecture devrait se faire de l'intérieur vers
l'extérieur. Avec WITH, la logique se lit de haut en bas, et chaque
étape peut être exécutée seule pendant sa vérification.

L'ajout de RECURSIVE permet à une CTE de lire sa propre sortie, ce
que le SQL ordinaire n'a pas d'autre moyen d'exprimer. Une CTE
récursive contient une requête initiale, puis UNION ou UNION ALL,
puis une requête qui fait référence à la CTE. PostgreSQL exécute la
requête initiale, conserve ses lignes dans une table de travail,
puis exécute à nouveau la seconde requête sur les lignes les plus
récentes jusqu'à ce qu'un tour n'en ajoute aucune. C'est ainsi
qu'une instruction parcourt un arbre de profondeur inconnue, comme
les catégories sous une rubrique :

```sql
WITH RECURSIVE subtree AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM categories
  WHERE id = 42
  UNION ALL
  SELECT c.id, c.parent_id, c.name, s.depth + 1
  FROM categories AS c
  JOIN subtree AS s ON c.parent_id = s.id
)
SELECT id, name, depth FROM subtree ORDER BY depth, name;
```

Pourquoi c'est important

La première raison est la lisibilité. Une requête analytique de cent
lignes imbriquée sur quatre niveaux est difficile à relire et encore
plus à modifier. La même logique sous forme de chaîne d'étapes
nommées donne à la personne qui la relit une phrase par étape, à son
auteur un endroit pour un commentaire, et à chacun le moyen
d'exécuter une étape seule pour vérifier ses lignes avant de faire
confiance à la suivante.

La deuxième raison est la façon dont la base évalue les étapes, et
les détails ont changé avec le temps. PostgreSQL évalue normalement
une requête WITH une fois par exécution de l'instruction, même si
celle-ci y fait référence plusieurs fois. Un calcul coûteux
nécessaire à deux endroits ne s'exécute donc qu'une fois. Le coût de
cette garantie est que le planificateur ne peut pas faire descendre
un filtre de la requête externe dans une CTE référencée plusieurs
fois, car ce filtre devrait s'appliquer à un usage et pas aux
autres. La CTE est calculée entièrement, et le filtrage n'écarte les
lignes qu'après coup.

Avant PostgreSQL 12, cela valait pour toute CTE : chacune était
évaluée avant le reste de la requête, sans jamais y être fusionnée.
La version 12 a changé le comportement appliqué sans indication
contraire. Une CTE non récursive, sans effet de bord et référencée
une seule fois est désormais intégrée à la requête externe. Le
planificateur optimise les deux niveaux ensemble et peut faire
descendre le filtre externe jusqu'à un index de la table de base.
MATERIALIZED impose l'ancien comportement, et NOT MATERIALIZED
impose l'intégration même pour une CTE utilisée plusieurs fois, au
risque d'exécuter son travail deux fois. SQLite accepte les deux
mêmes mots-clés comme indications non contraignantes et dit avoir
repris l'idée à PostgreSQL. Les autres moteurs décident eux-mêmes ;
c'est dans le plan qu'il faut vérifier.

La troisième raison est qu'une CTE peut modifier des données. Un
DELETE avec RETURNING dans une clause WITH peut passer sa sortie à
un INSERT dans l'instruction principale. Cela déplace des lignes
d'une table vers une autre dans une instruction unique qui
s'effectue entièrement ou pas du tout. Les CTE qui modifient des
données s'exécutent exactement une fois et toujours jusqu'au bout,
que leur sortie soit lue ou non. Elles servent ainsi à faire
plusieurs écritures ensemble, plutôt qu'à décrire un résultat.

À quoi cela ressemble en pratique

Le travail décrit dans la multiplication par 10 des performances
PostgreSQL concernait une base de comptes utilisateurs,
d'organisations clientes, de lieux et de relevés quotidiens
d'électricité qui ralentissait avec la croissance des données. La
correction avait trois volets : des index ciblés et GiST, un
partitionnement temporel de la table des relevés et la réécriture de
requêtes. Les réécritures utilisaient des CTE et des vues
matérialisées pour les données souvent consultées afin d'éviter les
parcours complets de table. EXPLAIN ANALYZE indiquait les jointures
et sous-requêtes à restructurer. La division par dix des temps de
réponse et la baisse de 40 % des ressources consommées venaient des
trois volets ensemble, pas des seules CTE. L'association mérite
attention, car les deux outils font des choses différentes : une CTE
nomme un résultat intermédiaire le temps d'une instruction ; une vue
matérialisée stocke un résultat que plusieurs instructions peuvent
lire jusqu'à son actualisation.

L'autre apport des CTE, la lisibilité, a soutenu les 500 rapports
d'analyse de données électriques et SIG. Chaque rapport était écrit
en SQL pur pour PostgreSQL et BigQuery, sur des données de compteurs
intelligents avec coordonnées, identifiants de compteurs et
consommations horodatées. Les scripts associaient les CTE à des
fonctions de fenêtrage, des sous-requêtes et des jointures
géospatiales. Chaque rapport était livré avec du SQL annoté pour
permettre aux collègues de reproduire et d'auditer l'analyse, ainsi
que des notes sur des problèmes courants de qualité, comme les
coordonnées GPS manquantes et les valeurs de compteurs corrompues.
Un SQL lisible étape par étape se partage bien : les rapports sont
devenus une référence commune aux différents services et ont
contribué à améliorer de 10–15 % l'efficacité de la planification
opérationnelle. Les étapes nommées rendent ce SQL annotable : une
note au-dessus de chacune peut expliquer ce qu'elle retire ou
suppose.

Où les problèmes surviennent

Le filtre arrive une fois tout le travail terminé. Une CTE
référencée deux fois ou marquée MATERIALIZED est calculée
entièrement avant que la requête externe la filtre, et la copie
produite n'a pas d'index. Une requête qui ne devrait toucher que
quelques lignes lit toute une table. Lisez le plan : si la CTE
apparaît comme une étape distincte, avec beaucoup plus de lignes que
la requête n'en renvoie, essayez NOT MATERIALIZED ou reformulez la
requête pour ne référencer la CTE qu'une fois. Gardez la forme
matérialisée quand la CTE appelle une fonction coûteuse, afin que
celle-ci s'exécute une fois par ligne plutôt qu'une fois par
référence.

Une requête récursive ne s'arrête pas. Des données cycliques, comme
deux catégories dont chacune désigne l'autre comme parent, font
tourner une CTE avec UNION ALL aussi longtemps que le serveur le
permet. UNION n'aide que si des lignes entières se répètent, ce qui
est rare une fois une colonne de profondeur ajoutée. Suivez le
chemin des clés visitées dans un tableau et arrêtez-vous quand une
clé réapparaît, ou utilisez la clause CYCLE ajoutée par PostgreSQL
14. Un LIMIT sur la requête externe est une astuce de test ; le
manuel déconseille de s'y fier en production. SQL Server limite la
récursion à 100 niveaux sans autre réglage et annule l'instruction
au-delà ; le même défaut s'arrête donc au lieu de continuer.

Le reste de l'instruction ne voit pas les changements. Une CTE qui
modifie des données et la requête principale s'exécutent
concurremment sur le même instantané ; une requête principale lisant
directement la table cible voit donc les lignes d'avant le
changement. Leur seul canal est RETURNING. Deux parties d'une même
instruction modifiant la même ligne font pire : une seule
modification prend effet, et il est difficile de prévoir laquelle.
Lisez les lignes changées dans la sortie RETURNING de la CTE et
veillez à ce qu'une seule partie de l'instruction modifie chaque
ligne.

Le plan change avec une mise à niveau. Du code écrit pour PostgreSQL
11 ou antérieur reposait parfois sur l'évaluation préalable d'une
CTE comme barrière délibérée contre un plan indésirable. Après une
mise à niveau vers 12 ou ultérieur, la même CTE est intégrée à la
requête externe et le planificateur choisit autre chose,
généralement plus rapide et parfois beaucoup plus lent. Comparez les
sorties EXPLAIN des instructions lentes avant et après la mise à
niveau, puis ajoutez MATERIALIZED là où la barrière était voulue.

Pour aller plus loin

  * 7.8. Requêtes WITH (expressions de table communes) — le chapitre
du manuel sur les CTE : récursion, détection des cycles,
matérialisation et instructions de modification des données.

  * E.23. Version 12 — la version qui a commencé à intégrer les CTE
utilisées une seule fois à la requête externe.

  * E.25. Version 14 — la version qui a ajouté les clauses SEARCH et
CYCLE de la norme SQL.

  * La clause WITH — la version de SQLite, ses indications de
matérialisation et leur provenance.

  * WITH common_table_expression (Transact-SQL) — les règles de SQL
Server pour les CTE, dont la limite de récursion MAXRECURSION.

hCette page sur le Web	URL:https://engineer.company/fr/glossary/cte/	engineer.company	70
