PREWHERE

PREWHERE puede hacer que el filtrado sea más eficiente al reducir la cantidad de datos leídos. De forma predeterminada, ClickHouse aplica esta optimización incluso cuando una consulta no especifica explícitamente PREWHERE, trasladando las condiciones aptas de WHERE a PREWHERE. Puede especificar PREWHERE explícitamente para controlar qué condiciones se aplican en esta fase.

Con PREWHERE, ClickHouse primero lee solo las columnas necesarias para evaluar la condición. Después, lee las demás columnas requeridas por la consulta únicamente en los bloques que contienen al menos una fila coincidente. Esto puede reducir la cantidad de datos leídos cuando la condición usa menos columnas que el resto de la consulta y descarta muchos bloques.

Especifique PREWHERE manualmente cuando una condición haga referencia a un número reducido de columnas y descarte muchas filas. Esto puede reducir la cantidad de datos que se leen de las columnas restantes.

Una consulta puede contener tanto PREWHERE como WHERE. En este caso, PREWHERE se evalúa primero.

Establezca optimize_move_to_prewhere en 0 para impedir que ClickHouse mueva automáticamente condiciones de WHERE a PREWHERE.

En las consultas con el modificador FINAL, ClickHouse mueve condiciones de WHERE a PREWHERE solo cuando están habilitados tanto optimize_move_to_prewhere como optimize_move_to_prewhere_if_final.

Una condición PREWHERE en una consulta con un JOIN puede hacer referencia directamente a columnas de una sola tabla como máximo. ClickHouse aplica la condición a las filas de esa tabla antes de que lleguen a la unión.

En cambio, una condición WHERE filtra lógicamente el resultado de la unión, aunque el optimizador puede aplicarla antes de la unión si hacerlo no cambia el resultado. Por lo tanto, usar la misma condición en PREWHERE y WHERE puede producir resultados diferentes, especialmente con uniones externas.

El siguiente ejemplo crea dos tablas para mostrar esta diferencia:

CREATE TABLE table_1
(
    `id` UInt32,
    `value` String
)
ENGINE = MergeTree
ORDER BY id;

CREATE TABLE table_2
(
    `id` UInt32,
    `value` String
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO table_1 VALUES (1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO table_2 VALUES (1, 'x'), (2, 'y'), (3, 'z');

En la primera consulta, PREWHERE filtra table_2 antes del LEFT JOIN, por lo que la fila de table_1 con id = 1 queda sin coincidencia:

SELECT
    table_1.id,
    table_1.value,
    table_2.value
FROM table_1
LEFT JOIN table_2 ON table_1.id = table_2.id
PREWHERE table_2.id >= 2
ORDER BY table_1.id;

   ┌─id─┬─value─┬─table_2.value─┐
1. │  1 │ a     │               │
2. │  2 │ b     │ y             │
3. │  3 │ c     │ z             │
   └────┴───────┴───────────────┘

Usar la misma condición en WHERE filtra el resultado de la unión y elimina la fila con id = 1:

SELECT
    table_1.id,
    table_1.value,
    table_2.value
FROM table_1
LEFT JOIN table_2 ON table_1.id = table_2.id
WHERE table_2.id >= 2
ORDER BY table_1.id;

   ┌─id─┬─value─┬─table_2.value─┐
1. │  2 │ b     │ y             │
2. │  3 │ c     │ z             │
   └────┴───────┴───────────────┘

PREWHERE solo se admite en tablas de la familia *MergeTree.

CREATE TABLE mydata
(
    `A` Int64,
    `B` Int8,
    `C` String
)
ENGINE = MergeTree
ORDER BY A AS
SELECT
    number,
    0,
    if(number between 1000 and 2000, 'x', toString(number))
FROM numbers(10000000);

SELECT count()
FROM mydata
WHERE (B = 0) AND (C = 'x');

1 row in set. Elapsed: 0.074 sec. Processed 10.00 million rows, 168.89 MB (134.98 million rows/s., 2.28 GB/s.)

-- Enable tracing to see which predicates are moved to PREWHERE.
set send_logs_level='debug';

MergeTreeWhereOptimizer: condition "B = 0" moved to PREWHERE
-- ClickHouse automatically moves B = 0 to PREWHERE, but this condition does not filter any rows because B is always 0.

-- Move the more selective C = 'x' predicate to PREWHERE.

SELECT count()
FROM mydata
PREWHERE C = 'x'
WHERE B = 0;

1 row in set. Elapsed: 0.069 sec. Processed 10.00 million rows, 158.89 MB (144.90 million rows/s., 2.30 GB/s.)

-- The query with manually specified PREWHERE processes slightly less data: 158.89 MB instead of 168.89 MB.