CTE in ClickHouse: Makro statt Optimierung

CTE in ClickHouse: Warum es ein Makro ist und keine Optimierung

Entwickler, die von OLTP-Systemen zu ClickHouse wechseln, greifen oft auf ihre Gewohnheit zurück, Common Table Expressions (CTE) mit WITH zu verwenden. ClickHouse unterstützt diese Syntax, aber sie wirkt lediglich als Parser-Makro: Die CTE wird eingefügt und bei jeder Referenz erneut ausgeführt. Das führt zu wiederholten Unterabfragen, höherer Last und unvorhersehbaren Ergebnissen.

Betrachten Sie diese typische Abfrage, die das Problem verdeutlicht:

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)

Man würde erwarten, dass sie alle 1.000.000 Zeilen zählt, aber das Ergebnis liegt bei etwa 600.000 und variiert bei jedem Lauf. Der Grund? generateRandom läuft zweimal und erzeugt unterschiedliche Datensätze. EXPLAIN zeigt zwei separate Lesevorgänge aus der Quelle.

Tatsächlich erweitert der Parser die CTE zu:

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

Alternativen zu CTE für zuverlässige Performance

Die beste Lösung ist CREATE TEMPORARY TABLE. Diese In-Memory-Tabellen materialisieren die Daten einmalig:

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)
;

Nun ist das Ergebnis stabil bei 1.000.000. Temporäre Tabellen existieren nur für die Session, umgehen Festplattenspeicher und eignen sich hervorragend für komplexe ETL-Prozesse.

Weitere Optionen:

• Unterabfragen mit Materialisierung: Nutzen Sie ARRAY JOIN oder MATERIALIZED-Views für wiederholte Berechnungen.
• Experimentelle CTE-Materialisierung: Neuere Versionen (Stand April 2026) bieten MATERIALIZED für WITH, aber aufgrund des experimentellen Status ist gründliches Testen in der Produktion essenziell.

| Ansatz | Materialisierung | Performance | Stabilität |

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

| CTE WITH | Nein | N-fache Steigerung | Niedrig |

| TEMP TABLE | Ja | O(1) | Hoch |

| Unterabfrage | Teilweise | Mittel | Mittel |

Abfrageoptimierung ohne Fallstricke

Für erfahrene Entwickler ist der Schlüssel zur Effizienz das Beherrschen des Ausführungsplans. Führen Sie immer EXPLAIN PIPELINE vor dem Produktiveinsatz aus:

• Überprüfen Sie die Anzahl der Read-Operationen.
• Vermeiden Sie unmaterialisierte Unterabfragen in WHERE/JOIN.
• Bei großen Datensätzen kombinieren Sie mit GROUP BY und Aggregaten.

Hier eine optimierte Version mit temporärer Tabelle und Indexierung:

• Erstellen Sie die Tabelle sortiert nach num.
• Fügen Sie ORDER BY num in der CTE hinzu, um IN zu beschleunigen.
• Skalieren Sie in einem Cluster mit DISTRIBUTED.

In der Produktion: Vermeiden Sie CTEs in heißen Abfragen – sie eignen sich nur für einmalige Ersetzungen ohne Wiederholungen.

Wichtige Erkenntnisse

• CTE als Makro: Bei jeder Referenz eingefügt, läuft mehrmals – prüfen Sie immer EXPLAIN.
• Temporäre Tabellen: Der zuverlässige Weg zur Materialisierung für die Wiederverwendung.
• Experimentelle Features: Testen Sie MATERIALIZED WITH sorgfältig und überwachen Sie die Last.
• Performance: 2x+ Unterschied in Zeit und Ressourcen bei wiederholten CTEs.
• Best Practice: Explizite Tabellen für ETL, Unterabfragen für einfache Fälle.

— Editorial Team