CTE v ClickHouse: makro místo optimalizace

CTE v ClickHouse: Proč je to makro a ne optimalizace

Vývojáři přecházející do ClickHouse z OLTP systémů často přenášejí zvyky používání Common Table Expressions (CTE) s WITH. V ClickHouse je tato syntaxe přítomna, ale funguje jako makro parseru: CTE je dosazeno a provedeno na každém místě zmínky. To vede k mnohonásobnému provádění poddotazů, růstu zátěže a nepředvídatelným výsledkům.

Uvažujme typický dotaz, který demonstruje problém:

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)

Očekávaný počet všech 1 000 000 záznamů nefunguje: výsledek je přibližně 600 000 a mění se při opakovaných spuštěních. Důvodem je, že generateRandom je voláno dvakrát, generující různé sady dat. EXPLAIN ukáže dvě samostatné čtení ze zdroje.

Ve skutečnosti parser rozvine CTE na:

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

Alternativy k CTE pro předvídatelnou práci

Hlavní přístup je CREATE TEMPORARY TABLE. Tabulky v paměti materializují data jednou:

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

Výsledek je stabilně 1 000 000. Dočasné tabulky žijí v sezení, nevyžadují diskové ukládání a jsou vhodné pro složité ETL úlohy.

Další možnosti:

• Subquery s materializací: Použijte ARRAY JOIN nebo MATERIALIZED views pro opakující se výpočty.
• Experimental CTE materializace: V nejnovějších verzích (na duben 2026) byla přidána volba MATERIALIZED pro WITH, ale status experimental vyžaduje testy na production.

| Přístup | Materializace | Výkonnost | Stabilita |

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

| CTE WITH | Ne | N-násobný růst | Nízká |

| TEMP TABLE | Ano | O(1) | Vysoká |

| Subquery | Částečná | Střední | Střední |

Optimalizace dotazů bez pastí

Pro middle/senior vývojáře je klíčem k efektivitě porozumění execution planu. Vždy spusťte EXPLAIN PIPELINE před productionem:

• Zkontrolujte počet Read operací.
• Vyhněte se nematerializovaným poddotazům v WHERE/JOIN.
• Pro velké datasety kombinujte s GROUP BY a agregáty.

Příklad optimalizovaného variantu s použitím dočasné tabulky a indexace:

• Vytvořit tabulku s řazením podle num.
• Přidat ORDER BY num do CTE pro urychlení IN.
• Škálovat na clusteru s DISTRIBUTED.

V productionu se vyhněte CTE v horkých dotazech: jsou vhodné pouze pro jednorázové dosazení bez opakování.

Co je důležité

• CTE jako makro: Je dosazeno v každém volání, provedeno mnohonásobně — zkontrolujte EXPLAIN.
• Dočasné tabulky: Jediný spolehlivý způsob materializace pro opětovné použití.
• Experimental funkce: MATERIALIZED WITH testujte opatrně, monitorujte zátěž.
• Výkonnost: Rozdíl 2+ krát v čase a zdrojích při opakovaných CTE.
• Best practice: Preferujte explicitní tabulky pro ETL, subquery — pro jednoduché případy.

— Editorial Team