CTE dans ClickHouse : macro au lieu d'optimisation

CTE dans ClickHouse : un simple macro, pas une optimisation

Les développeurs passant à ClickHouse depuis des systèmes OLTP conservent souvent l'habitude d'utiliser des Common Table Expressions (CTE) avec WITH. ClickHouse supporte cette syntaxe, mais elle fonctionne comme une macro du parseur : la CTE est développée en ligne et exécutée à chaque référence. Cela entraîne des exécutions répétées de sous-requêtes, une charge accrue et des résultats imprévisibles.

Voici une requête typique qui illustre le problème :

WITH cte_numbers AS
(
    SELECT
        num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
SELECT
    count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers)

On s'attendrait à compter les 1 000 000 lignes, mais le résultat tourne autour de 600 000 et varie à chaque exécution. La cause ? generateRandom s'exécute deux fois, générant des ensembles de données différents. EXPLAIN révèle deux lectures distinctes de la source.

En réalité, le parseur développe la CTE en :

SELECT
    count()
FROM
    (
        SELECT
            num
        FROM
            generateRandom('num UInt64', NULL)
        LIMIT 1000000
    )
WHERE
    num IN (
        SELECT
            num
        FROM
            generateRandom('num UInt64', NULL)
        LIMIT 1000000
    )

Alternatives aux CTE pour des performances fiables

La solution de prédilection est CREATE TEMPORARY TABLE. Ces tables en mémoire matérialisent les données une seule fois :

CREATE TEMPORARY TABLE cte_numbers AS
(
    SELECT
        num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
;

SELECT
    count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers)
;

Le résultat est désormais stable à 1 000 000. Les tables temporaires persistent pour la session, évitent l'écriture sur disque et excellent dans les workflows ETL complexes.

Autres options :

• Sous-requêtes avec matérialisation : Utilisez ARRAY JOIN ou vues MATERIALIZED pour les calculs répétés.
• Matérialisation expérimentale des CTE : Les versions récentes (avril 2026) proposent MATERIALIZED pour WITH, mais son statut expérimental impose des tests approfondis en production.

| Approche | Matérialisation | Performance | Stabilité |

|-------------------|-----------------|-----------------|-----------|

| CTE WITH | Non | Multiplication | Faible |

| TABLE TEMPORAIRE | Oui | O(1) | Élevée |

| Sous-requête | Partielle | Moyenne | Moyenne |

Optimisation des requêtes sans pièges

Pour les développeurs intermédiaires et seniors, la clé de l'efficacité réside dans la maîtrise du plan d'exécution. Exécutez toujours EXPLAIN PIPELINE avant la production :

• Vérifiez le nombre d'opérations Read.
• Évitez les sous-requêtes non matérialisées dans WHERE/JOIN.
• Pour les gros volumes, combinez avec GROUP BY et agrégats.

Voici une version optimisée avec table temporaire et indexation :

• Créez la table triée par num.
• Ajoutez ORDER BY num dans la CTE pour accélérer IN.
• Scalez sur un cluster avec DISTRIBUTED.

En production, fuyez les CTE dans les requêtes chaudes : elles conviennent seulement aux substitutions uniques sans répétition.

Points clés

• CTE comme macro : Développée à chaque référence, s'exécute plusieurs fois — vérifiez toujours EXPLAIN.
• Tables temporaires : La méthode fiable pour matérialiser et réutiliser.
• Fonctionnalités expérimentales : Testez MATERIALIZED WITH avec soin et surveillez la charge.
• Performance : Gain de 2x+ en temps et ressources avec les CTE répétées.
• Meilleure pratique : Tables explicites pour l'ETL, sous-requêtes pour les cas simples.

— Editorial Team