CTE w ClickHouse: makro zamiast optymalizacji

CTE w ClickHouse: dlaczego to makro, a nie optymalizacja

Deweloperzy przechodzący do ClickHouse z systemów OLTP często przenoszą nawyki używania Common Table Expressions (CTE) z WITH. W ClickHouse ta składnia istnieje, ale działa jak makro parsera: CTE jest podstawiane i wykonywane w każdym miejscu, gdzie jest przywoływane. To prowadzi do wielokrotnego uruchamiania podzapytań, zwiększenia obciążenia i nieprzewidywalnych rezultatów.

Rozważmy typowe zapytanie, które demonstruje problem:

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)

Oczekiwane policzenie wszystkich 1 000 000 rekordów nie działa: wynik ~600 000 i zmienia się przy powtórnych uruchomieniach. Przyczyna — generateRandom jest wywoływane dwukrotnie, generując różne zestawy danych. EXPLAIN pokaże dwa oddzielne odczyty ze źródła.

Faktycznie parser rozwija CTE w:

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

Alternatywy dla CTE dla przewidywalnej pracy

Główne podejście — CREATE TEMPORARY TABLE. Tabele w pamięci materializują dane raz:

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

Wynik stabilnie 1 000 000. Tabele tymczasowe żyją w sesji, nie wymagają przechowywania na dysku i są odpowiednie dla złożonych zadań ETL.

Inne opcje:

• Subzapytanie z materializacją: Użyj ARRAY JOIN lub widoków MATERIALIZED dla powtarzających się obliczeń.
• Eksperymentalna materializacja CTE: W nowych wersjach (na kwiecień 2026) dodano opcję MATERIALIZED dla WITH, ale status eksperymentalny wymaga testów na produkcji.

| Podejście | Materializacja | Wydajność | Stabilność |

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

| CTE WITH | Nie | N-krotny wzrost | Niska |

| TABELA TYMCZASOWA | Tak | O(1) | Wysoka |

| Subzapytanie | Częściowa | Średnia | Średnia |

Optymalizacja zapytań bez pułapek

Dla middle/senior deweloperów klucz do efektywności — zrozumienie planu wykonania. Zawsze uruchamiaj EXPLAIN PIPELINE przed produkcją:

• Sprawdzaj liczbę operacji Read.
• Unikaj niematerializowanych subzapytań w WHERE/JOIN.
• Dla dużych zestawów danych łącz z GROUP BY i agregatami.

Przykład zoptymalizowanego wariantu z użyciem tabeli tymczasowej i indeksowania:

• Utwórz tabelę z sortowaniem według num.
• Dodaj ORDER BY num w CTE dla przyspieszenia IN.
• Skaluj na klastrze z DISTRIBUTED.

W produkcji unikaj CTE w gorących zapytaniach: są odpowiednie tylko dla jednorazowych podstawień bez powtórzeń.

Co jest ważne

• CTE jako makro: Podstawiane w każdym wywołaniu, wykonywane wielokrotnie — sprawdzaj EXPLAIN.
• Tabele tymczasowe: Jedyny niezawodny sposób materializacji dla ponownego użycia.
• Eksperymentalne funkcje: MATERIALIZED WITH testuj ostrożnie, monitoruj obciążenie.
• Wydajność: Różnica 2+ razy w czasie i zasobach przy powtarzających się CTE.
• Best practice: Preferuj jawne tabele dla ETL, subzapytania — dla prostych przypadków.

— Editorial Team