CTE en ClickHouse: macro en lugar de optimización

CTE en ClickHouse: Por qué es un macro y no una optimización

Los desarrolladores que migran a ClickHouse desde sistemas OLTP suelen traer consigo el hábito de usar expresiones de tabla común (CTE) con WITH. ClickHouse soporta esta sintaxis, pero actúa como un macro del analizador: la CTE se expande e inlinea, ejecutándose cada vez que se referencia. Esto provoca ejecuciones repetidas de subconsultas, mayor carga y resultados impredecibles.

Considera esta consulta típica que ilustra el problema:

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)

Esperarías que contara las 1.000.000 filas, pero el resultado ronda las 600.000 y varía en cada ejecución. ¿El culpable? generateRandom se ejecuta dos veces, generando conjuntos de datos distintos. EXPLAIN revela dos lecturas separadas de la fuente.

En realidad, el analizador expande 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
    )

Alternativas a CTE para un rendimiento fiable

La solución principal es CREATE TEMPORARY TABLE. Estas tablas en memoria materializan los datos una sola vez:

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

Ahora el resultado es un estable 1.000.000. Las tablas temporales duran durante la sesión, evitan el almacenamiento en disco y brillan en flujos ETL complejos.

Otras opciones:

• Subconsultas con materialización: Usa ARRAY JOIN o vistas MATERIALIZED para cálculos repetidos.
• Materialización experimental de CTE: Versiones recientes (a partir de abril 2026) ofrecen MATERIALIZED para WITH, pero su estado experimental exige pruebas exhaustivas en producción.

| Enfoque | Materialización | Rendimiento | Estabilidad |

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

| CTE WITH | No | Multiplicación n veces | Baja |

| TABLA TEMP | Sí | O(1) | Alta |

| Subconsulta | Parcial | Medio | Medio |

Optimización de consultas sin trampas

Para desarrolladores intermedios y senior, la clave de la eficiencia es dominar el plan de ejecución. Siempre ejecuta EXPLAIN PIPELINE antes de producción:

• Verifica el número de operaciones Read.
• Evita subconsultas no materializadas en WHERE/JOIN.
• Para datasets grandes, combínalo con GROUP BY y agregados.

Aquí una versión optimizada con tabla temporal e indexación:

• Crea la tabla ordenada por num.
• Añade ORDER BY num en la CTE para acelerar IN.
• Escala en clúster con DISTRIBUTED.

En producción, evita CTE en consultas calientes: solo sirven para sustituciones únicas sin repeticiones.

Lecciones clave

• CTE como macro: Se inlinea en cada referencia, se ejecuta múltiples veces — siempre verifica con EXPLAIN.
• Tablas temporales: La forma fiable de materializar para reutilizar.
• Funciones experimentales: Prueba MATERIALIZED WITH con cuidado y monitorea la carga.
• Rendimiento: Diferencia de 2x+ en tiempo y recursos con CTE repetidas.
• Mejor práctica: Usa tablas explícitas para ETL, subconsultas para casos simples.

— Editorial Team