ClickHouse의 CTE: 최적화 대신 매크로

ClickHouse의 CTE: 왜 매크로일 뿐 최적화가 아닌가

OLTP 시스템에서 ClickHouse로 전환하는 개발자들은 WITH를 사용한 공통 테이블 표현식(CTE) 습관을 그대로 가져오곤 합니다. ClickHouse는 이 구문을 지원하지만, 이는 파서 매크로로 작동합니다: CTE는 참조될 때마다 인라인 전개되어 실행됩니다. 이로 인해 서브쿼리가 반복 실행되고, 부하가 증가하며, 예측 불가능한 결과가 나옵니다.

이 문제를 잘 보여주는 전형적인 쿼리를 보죠:

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)

100만 행 전체를 카운트할 거라 기대하시겠죠? 하지만 결과는 약 60만 행 정도 나오고, 매 실행마다 달라집니다. 원인? generateRandom이 두 번 실행되어 다른 데이터셋을 생성합니다. EXPLAIN을 보면 소스에서 두 번 읽는 걸 확인할 수 있습니다.

실제로는 파서가 CTE를 이렇게 전개합니다:

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

안정적인 성능을 위한 CTE 대안

최고의 해결책은 CREATE TEMPORARY TABLE입니다. 이 인메모리 테이블은 데이터를 한 번만 물리화합니다:

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

이제 결과는 안정적으로 100만 행입니다. 임시 테이블은 세션 동안 유지되며, 디스크 저장을 건너뛰고 복잡한 ETL 워크플로에서 빛을 발합니다.

다른 옵션:

• 물리화된 서브쿼리: 반복 계산에 ARRAY JOIN이나 MATERIALIZED 뷰 사용.
• 실험적 CTE 물리화: 최신 버전(2026년 4월 기준)에서 WITH에 MATERIALIZED 지원, 하지만 실험 단계라 프로덕션에서 철저히 테스트 필요.

| 접근법 | 물리화 | 성능 | 안정성 |

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

| CTE WITH | 없음 | N배 증가 | 낮음 |

| TEMP TABLE | 있음 | O(1) | 높음 |

| 서브쿼리 | 부분 | 중간 | 중간 |

함정 없는 쿼리 최적화

중고급 개발자에게 효율의 핵심은 실행 계획 마스터입니다. 프로덕션 전에 반드시 EXPLAIN PIPELINE 실행:

• Read 작업 수 확인.
• WHERE/JOIN에서 비물리화 서브쿼리 피하기.
• 대용량 데이터셋은 GROUP BY와 집계와 함께 사용.

임시 테이블과 인덱싱을 활용한 최적화 버전:

• num으로 정렬된 테이블 생성.
• CTE에 ORDER BY num 추가해 IN 속도 향상.
• 클러스터에서 DISTRIBUTED로 확장.

프로덕션에서 핫 쿼리에 CTE 피하세요: 반복 없는 일회성 치환에만 적합합니다.

주요 요점

• CTE는 매크로: 참조마다 인라인, 여러 번 실행 — 항상 EXPLAIN 확인.
• 임시 테이블: 재사용 물리화의 안정적 방법.
• 실험 기능: MATERIALIZED WITH 신중 테스트 및 부하 모니터링.
• 성능: 반복 CTE로 시간·자원 2배 이상 차이.
• 모범 사례: ETL은 명시적 테이블, 간단 케이스는 서브쿼리.

— Editorial Team