CTE PostgreSQL : WITH et matérialisation

Une expression de table commune PostgreSQL, ou CTE, donne un nom au résultat d’une requête à l’intérieur d’une seule instruction SQL. Définissez-la avec WITH, puis utilisez ce nom dans la requête principale ou une CTE suivante. Elle peut rendre les étapes plus lisibles, mais ne crée pas automatiquement une table stockée et ne garantit pas de meilleures performances.

Nous avons exécuté 31 contrôles sur un serveur PostgreSQL 18.6 isolé avec les exemples ci-dessous. Ils couvrent les requêtes enchaînées, la portée des noms, cinq plans d’exécution, les lignes exactes et une récursion bornée. Le planificateur utilisait ses réglages par défaut. Ces observations locales ne mesurent pas la latence d’un environnement de production.

Construire une requête en étapes nommées

Utilisez une base jetable où le nom de schéma cte_lab est libre. Cette préparation crée cinq commandes. Le client A a deux commandes payées totalisant 120, B en a deux totalisant 50, et C a une commande en attente de 900 :

CREATE SCHEMA cte_lab;
CREATE TABLE cte_lab.orders (
    id integer PRIMARY KEY,
    customer text NOT NULL,
    status text NOT NULL,
    amount integer NOT NULL
);
INSERT INTO cte_lab.orders VALUES
(1, 'A', 'paid', 70), (2, 'A', 'paid', 50),
(3, 'B', 'paid', 40), (4, 'B', 'paid', 10),
(5, 'C', 'pending', 900);

Séparez maintenant la sélection des commandes payées du calcul des totaux par client :

WITH paid AS (
    SELECT customer, amount
    FROM cte_lab.orders
    WHERE status = 'paid'
), totals AS (
    SELECT customer, sum(amount) AS total, count(*) AS orders
    FROM paid
    GROUP BY customer
)
SELECT customer, total, orders
FROM totals
WHERE total >= 100
ORDER BY customer;

Résultat observé, dans le format non aligné de psql :

A|120|2

paid contient quatre lignes pour un total de 170. totals les regroupe par client. La requête principale conserve uniquement le client dont le total atteint 100. Le montant en attente de 900 n’entre jamais dans l’agrégat.

Notre requête directe de contrôle, avec GROUP BY et HAVING mais sans CTE, a renvoyé la même ligne. Ici, WITH sert à rendre les étapes visibles. La documentation des requêtes WITH décrit la syntaxe et ses règles d’optimisation.

La virgule relie les définitions d’une même clause WITH. Chaque définition comprend un nom, une liste facultative de noms de colonnes et une requête entre parenthèses. Terminez l’instruction après la requête principale, pas entre les définitions.

Un nom de CTE ne dure qu’une instruction

Après la fin de l’instruction précédente, notre SELECT * FROM paid; séparé a échoué avec le code SQLSTATE 42P01, indiquant une relation introuvable. Nous avons aussi vérifié qu’aucune table stockée cte_lab.paid n’existait. Nommer le résultat dans WITH n’en avait pas créé.

Si plusieurs instructions ultérieures ont besoin d’une relation alimentée séparément, envisagez une table temporaire avec des règles explicites de création et de durée de vie. Ce choix relève de CREATE TABLE. Une CTE n’est pas un cache persistant, et le mot-clé MATERIALIZED ci-dessous ne conserve pas son résultat après l’instruction.

Choisissez des noms précis comme paid et totals lorsqu’ils désignent une étape utile. Transformer chaque expression en une CTE supplémentaire peut rendre une requête courte moins lisible.

Comparer le comportement par défaut et la matérialisation

Pour examiner les plans, créez une autre table de 20 000 lignes avec un index de clé primaire. Chaque libellé a la même longueur. Nous collectons les statistiques avant les requêtes :

CREATE TABLE cte_lab.events (
    id integer PRIMARY KEY,
    label text NOT NULL
);
INSERT INTO cte_lab.events
SELECT n, repeat('x', 40)
FROM generate_series(1, 20000) AS g(n);
ANALYZE cte_lab.events;

Commencez par une CTE utilisée une seule fois :

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
WITH selected AS (
    SELECT id, label FROM cte_lab.events
)
SELECT id, label FROM selected WHERE id = 1000;

Lors de notre exécution, PostgreSQL a intégré la CTE à la requête principale. Le plan utilisait l’index events_pkey sans nœud CTE Scan. Le filtre id = 1000 pouvait s’appliquer à l’accès à la table sous-jacente.

Demandez ensuite une évaluation séparée :

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
WITH selected AS MATERIALIZED (
    SELECT id, label FROM cte_lab.events
)
SELECT id, label FROM selected WHERE id = 1000;

Ce plan produisait les 20 000 lignes par un parcours séquentiel. Le CTE Scan extérieur conservait une ligne et en éliminait 19 999 par son filtre. Demandez enfin l’intégration à la requête principale :

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
WITH selected AS NOT MATERIALIZED (
    SELECT id, label FROM cte_lab.events
)
SELECT id, label FROM selected WHERE id = 1000;

Le plan NOT MATERIALIZED utilisait l’index de clé primaire, comme le comportement par défaut avec une seule référence. Les trois formes renvoyaient le même identifiant, 1000, et le même libellé de 40 caractères. La différence concernait l’organisation du travail, pas la ligne sélectionnée.

Forme testée	Accès observé	Lignes renvoyées
Une référence, défaut	Index de clé primaire ; sans CTE Scan	1
Une référence, MATERIALIZED	Production de 20 000 lignes ; filtrage de 19 999	1
Une référence, NOT MATERIALIZED	Index de clé primaire ; sans CTE Scan	1

La règle documentée concerne une CTE SELECT non récursive, sans effet de bord ni fonction volatile : une référence unique permet son intégration, tandis que plusieurs références favorisent normalement une évaluation séparée. Nos exemples respectent ces conditions. Ne généralisez pas leur plan aux instructions récursives ou modifiant les données.

EXPLAIN ANALYZE exécute l’instruction. Ici, les requêtes mesurées lisent seulement notre table synthétique ; les commandes de préparation et de nettoyage modifient la base de test. Le guide EXPLAIN de PostgreSQL décrit ces mesures. Notre guide EXPLAIN ANALYZE, en anglais détaille la lecture des plans.

La réutilisation d’une CTE change la décision

Cette autojointure volontairement simple utilise deux fois la même CTE. Son résultat reste facile à vérifier, tout en exposant la décision de réutilisation :

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
WITH selected AS (
    SELECT id, label FROM cte_lab.events
)
SELECT a.id, b.label
FROM selected AS a
JOIN selected AS b ON b.id = a.id
WHERE a.id = 1000;

Notre plan par défaut comportait un seul producteur pour selected et deux nœuds CTE Scan. Nous avons ensuite remplacé uniquement AS ( par AS NOT MATERIALIZED (. Le nouveau plan ne contenait aucun CTE Scan et utilisait deux accès par index. Les deux requêtes renvoyaient la même ligne.

Ce résultat appuie l’intégration pour cette recherche sélective précise. Il ne montre pas que la matérialisation gaspille toujours du travail. Si une étape commune effectue un calcul coûteux nécessaire à plusieurs références, l’évaluer une fois peut éviter de le répéter. Nous n’avons testé aucune fonction coûteuse ni attribué de facteur d’accélération à ces approches.

Examinez la requête, les volumes, les index et le plan avant d’ajouter une directive. Vérifiez les résultats en parallèle. Un plan apparemment moins coûteux n’aide pas si la réécriture change le résultat attendu. Pour choisir les requêtes à examiner dans une charge réelle, notre guide pg_stat_statements, en anglais explique comment les repérer.

Utiliser une récursion bornée pour une petite hiérarchie

Une CTE récursive peut utiliser les lignes de l’itération précédente à l’étape suivante. Cet exemple autonome part du nœud 1 et suit les relations parent-enfant. Il conserve le chemin parcouru et une limite de profondeur :

WITH RECURSIVE nodes(id, parent_id, name) AS (
    VALUES (1, NULL::integer, 'root'),
           (2, 1, 'alpha'), (3, 1, 'beta'), (4, 2, 'leaf')
), walk(id, name, depth, path) AS (
    SELECT id, name, 0, ARRAY[id]
    FROM nodes WHERE id = 1
    UNION ALL
    SELECT n.id, n.name, w.depth + 1, w.path || n.id
    FROM walk AS w
    JOIN nodes AS n ON n.parent_id = w.id
    WHERE w.depth < 3 AND NOT n.id = ANY(w.path)
)
SELECT id, name, depth, path FROM walk ORDER BY path;

Résultat observé :

1|root|0|{1}
2|alpha|1|{1,2}
4|leaf|2|{1,2,4}
3|beta|1|{1,3}

L’ancre crée la racine à profondeur zéro. Chaque étape trouve les enfants, incrémente la profondeur et ajoute leur identifiant au chemin. Le contrôle du chemin rejette un nœud déjà visité sur cet itinéraire. La condition de profondeur autorise au plus trois arêtes depuis la racine.

Nous avons testé un cycle en remplaçant le parent NULL de la racine par 4, ce qui crée le parcours 1 → 2 → 4 → 1. Le contrôle du chemin a bloqué cette nouvelle visite et renvoyé les mêmes quatre lignes. Passer la limite de profondeur de 3 à 1 a conservé seulement la racine et ses enfants directs. Une ancre absente n’a renvoyé aucune ligne.

La borne de profondeur est une limite, pas la preuve qu’une hiérarchie plus grande a été parcourue entièrement. Choisissez-la et documentez-la selon le besoin. De même, le contrôle porte sur chaque chemin, pas sur une déduplication globale de tous les graphes. Cet exemple utilise des identifiants uniques et un seul parent par nœud.

ORDER BY path fixe l’ordre d’affichage. Ne déduisez pas un ordre d’exécution ou de visite d’une sortie récursive sans tri final explicite. La référence SELECT décrit l’ordre des résultats et la syntaxe récursive. Les CTE modifiant les données et les parcours non bornés restent hors du périmètre de ce guide.

Nettoyer et choisir la forme utile la plus simple

Après vérification, supprimez uniquement les objets créés pour ce test :

DROP TABLE cte_lab.events;
DROP TABLE cte_lab.orders;
DROP SCHEMA cte_lab;

Notre contrôle de nettoyage a confirmé la disparition du schéma. Le serveur isolé a été arrêté et son fichier temporaire de mot de passe d’initialisation supprimé après les tests.

Utilisez une CTE lorsqu’une étape nommée clarifie la requête ou qu’une récursion est nécessaire. Partez du plan par défaut. Envisagez MATERIALIZED ou NOT MATERIALIZED avec une raison visible dans le plan et vérifiée contre les lignes attendues. Nos tests établissent ces résultats locaux ; ils ne mesurent ni des données de production, ni des charges concurrentes, ni un hébergement cloud.