PREWHERE

PREWHERE can make filtering more efficient by reducing the amount of data read. By default, ClickHouse applies this optimization, even when a query does not explicitly specify PREWHERE, by moving eligible conditions from WHERE to PREWHERE. You can specify PREWHERE explicitly to control which conditions are applied at this stage.

With PREWHERE, ClickHouse first reads only the columns needed to evaluate the condition. It then reads the other columns required by the query only for blocks that contain at least one matching row. This can reduce the amount of data read when the condition uses fewer columns than the rest of the query and filters out many blocks.

Controlling PREWHERE manually

Specify PREWHERE manually when a condition references a small number of columns and filters out many rows. This can reduce the amount of data read for the remaining columns.

A query can contain both PREWHERE and WHERE. In this case, PREWHERE is evaluated first.

Set optimize_move_to_prewhere to 0 to prevent ClickHouse from automatically moving conditions from WHERE to PREWHERE.

For queries with the FINAL modifier, ClickHouse moves conditions from WHERE to PREWHERE only when both optimize_move_to_prewhere and optimize_move_to_prewhere_if_final are enabled.

PREWHERE with JOIN

A PREWHERE condition in a query with a JOIN can directly reference columns from at most one table. ClickHouse applies the condition to that table’s rows before they reach the join.

By contrast, a WHERE condition logically filters the joined result, although the optimizer may apply it before the join when doing so does not change the result. Using the same condition in PREWHERE and WHERE can therefore produce different results, particularly with outer joins.

The following example creates two tables to demonstrate this difference:

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

In the first query, PREWHERE filters table_2 before the LEFT JOIN, so the row from table_1 with id = 1 remains unmatched:

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             │
   └────┴───────┴───────────────┘

Using the same condition in WHERE filters the joined result, removing the row with 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             │
   └────┴───────┴───────────────┘

Limitations

PREWHERE is only supported by tables from the *MergeTree family.

Example

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.