Erreurs typiques avec les CTE et comment les éviter

Bon, il est temps de parler du côté obscur des CTE — les erreurs classiques. Même la requête la plus stylée peut planter si tu utilises mal ces outils puissants. Mais t’inquiète, on t’a préparé un vrai guide pour diagnostiquer et éviter tout ça !

1. Erreur : matérialisation du CTE et ses conséquences

Un truc clé avec PostgreSQL quand tu bosses avec les CTE — c’est leur matérialisation par défaut. Ça veut dire que le résultat du CTE est traité et stocké temporairement en mémoire (ou sur disque si t’as trop de données). Si t’as plein de requêtes ou un gros volume, ça peut vraiment ralentir l’exécution.

Exemple :

WITH heavy_data AS (
    SELECT * FROM large_table
)
SELECT * FROM heavy_data WHERE column_a > 100;

À première vue, on dirait que le CTE filtre juste les données. Mais en vrai, heavy_data est d’abord entièrement chargé et matérialisé, puis seulement le filtre s’applique. Ça peut prendre un temps fou.

Comment éviter ça ?

Depuis PostgreSQL 12, tu peux utiliser les CTE comme inline expression (comme un sous-requête), ce qui règle le souci de matérialisation. Il suffit que le CTE soit utilisé qu’une seule fois et que t’aies pas besoin de garder les résultats intermédiaires.

Exemple d’approche optimisée :

WITH inline_data AS MATERIALIZED (
    SELECT * FROM large_table
)
SELECT * FROM inline_data WHERE column_a > 100;

Astuce : si tu veux forcer la matérialisation, mets MATERIALIZED. Sinon — utilise NOT MATERIALIZED.

2. Erreur : CTE récursifs qui bouclent à l’infini

Les CTE récursifs, c’est puissant, mais si tu limites pas la profondeur des itérations, tu risques de créer une boucle infinie. Non seulement ça va tout ralentir, mais ça va aussi bouffer toutes les ressources.

Exemple :

WITH RECURSIVE endless_loop AS (
    SELECT 1 AS value

    UNION ALL

    SELECT value + 1
    FROM endless_loop
)
SELECT * FROM endless_loop;

Ce truc va générer une infinité de lignes, parce qu’il n’y a pas de condition pour stopper la récursion.

Comment éviter ça ?

Ajoute une vraie condition d’arrêt avec WHERE. Par exemple :

WITH RECURSIVE limited_loop AS (
    SELECT 1 AS value

    UNION ALL

    SELECT value + 1
    FROM limited_loop
    WHERE value < 10
)
SELECT * FROM limited_loop;

Astuce : si tu utilises des CTE récursifs pour de grosses hiérarchies, limite la profondeur avec l’option max_recursion_depth de PostgreSQL.

3. Erreur : mauvais usage de UNION et UNION ALL

Quand tu combines la requête de base et la requête récursive dans un CTE, mal choisir entre UNION et UNION ALL peut donner des résultats chelous. Par exemple, UNION vire les doublons, ce qui coûte en perf.

Exemple :

WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION  -- Ici, mieux vaut utiliser UNION ALL

    SELECT e.employee_id, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

Dans cet exemple, UNION peut supprimer des lignes importantes de la hiérarchie si elles se répètent. Et en plus, ça ralentit la requête !

Comment corriger ?

Utilise UNION ALL si t’as pas besoin de virer les doublons :

UNION ALL

4. Erreur : trop de CTE dans une seule requête

En voulant structurer à fond, certains balancent des dizaines de CTE. Non seulement ça rend le code incompréhensible, mais en plus ça surcharge le planner de PostgreSQL.

Exemple :

WITH cte1 AS (...),
     cte2 AS (...),
     cte3 AS (...),
     ...
     cte20 AS (...)
SELECT ...
FROM cte20;

Ça ressemble à un cauchemar pour n’importe quel dev.

Comment corriger ?

— Coupe ta requête en plusieurs plus simples. Plutôt qu’un méga-requête avec plein de CTE — fais plusieurs requêtes indépendantes.

— Autre option : pour les résultats intermédiaires à réutiliser, stocke-les dans des tables temporaires.

5. Erreur : CTE complexes sans indexation

Si ton CTE manipule beaucoup de données mais que t’as oublié de mettre des index sur les tables, tes requêtes vont ramer. Les index, c’est comme du dopage pour ta base.

Exemple :

WITH filtered_data AS (
    SELECT * FROM large_table WHERE unindexed_column = 'valeur'
)
SELECT * FROM filtered_data;

Comment corriger ?

Avant d’utiliser un CTE, assure-toi que tes tables sont optimisées :

CREATE INDEX idx_large_table ON large_table(unindexed_column);

6. Erreur : essayer d’utiliser un CTE pour des appels multiples aux données

Un CTE est créé, exécuté, puis “figé”. Si tu veux utiliser son résultat à plusieurs endroits, les données ne seront pas recalculées — et parfois ça pose problème.

Exemple :

WITH data AS (
    SELECT x, y FROM some_table
)
SELECT x FROM data
WHERE y > 10;
-- Si tu veux recalculer data encore, ça ne se fera pas.

Comment corriger ?

Si t’as besoin de dynamique ou de recalcul, le CTE n’est peut-être pas le bon choix. Utilise plutôt des sous-requêtes.

7. Erreur : pas de commentaires

Les CTE, c’est génial, mais qui veut d’une requête SQL complexe que personne, même toi, ne pourra relire dans deux semaines ?

Exemple :

WITH data_filtered AS (
    SELECT *
    FROM large_table
    WHERE some_column > 100
)
SELECT * FROM data_filtered;

Dans un mois, plus personne ne saura pourquoi on filtrait ces données !

Alors commente tes requêtes, surtout si tu utilises des CTE complexes ou récursifs :

WITH data_filtered AS (
    -- Filtre les données où la colonne some_column > 100
    SELECT *
    FROM large_table
    WHERE some_column > 100
)
SELECT * FROM data_filtered;

8. Erreur : abuser des CTE au lieu des tables temporaires

Parfois, les tables temporaires sont bien plus adaptées. Par exemple, si tu dois réutiliser le résultat dans plusieurs requêtes ou si tu bosses avec un gros volume de données.

Exemple :

WITH temp_data AS (
    SELECT * FROM large_table
)
SELECT * FROM temp_data WHERE column_a > 100;
SELECT * FROM temp_data WHERE column_b < 50;

Cette requête avec CTE va s’exécuter deux fois, même si les données ne changent pas !

Comment corriger ?

Crée une table temporaire si tu utilises les données plusieurs fois :

CREATE TEMP TABLE temp_table AS
SELECT * FROM large_table;

SELECT * FROM temp_table WHERE column_a > 100;
SELECT * FROM temp_table WHERE column_b < 50;

Dernier conseil

Comme pour toute fonction puissante, avec les CTE il faut piger que c’est pas toujours le meilleur outil pour tout. Réfléchis à pourquoi et comment tu les utilises. Le mode “plus y’a de CTE, mieux c’est” peut vraiment flinguer les perfs et la lisibilité du code. Et bien sûr, pense à faire des tests de perf et à optimiser tes requêtes.