Как работает оптимизация PREWHERE?

PREWHERE снижает I/O, избегая чтения ненужных данных из столбцов.

Предложение PREWHERE — это оптимизация выполнения запросов в ClickHouse. Она уменьшает I/O и ускоряет запросы, избегая лишнего чтения данных и отфильтровывая нерелевантные данные до чтения с диска столбцов, не используемых для фильтрации.

В этом руководстве объясняется, как работает PREWHERE, как измерить его влияние и как настроить его для достижения максимальной производительности.

​

Обработка запроса без оптимизации PREWHERE

Для начала рассмотрим, как обрабатывается запрос к таблице uk_price_paid_simple без использования PREWHERE:

① Запрос содержит фильтр по столбцу town, который входит в первичный ключ таблицы, а значит — и в первичный индекс.

② Чтобы ускорить выполнение запроса, ClickHouse загружает первичный индекс таблицы в память.

③ Затем ClickHouse просматривает записи индекса, чтобы определить, какие гранулы столбца town могут содержать строки, соответствующие условию.

④ Эти потенциально подходящие гранулы загружаются в память вместе с позиционно выровненными гранулами из других столбцов, необходимых для запроса.

⑤ После этого оставшиеся фильтры применяются во время выполнения запроса.

Как видно, без PREWHERE все потенциально нужные столбцы загружаются до фильтрации, даже если условию на самом деле соответствует лишь несколько строк.

​

Как PREWHERE повышает эффективность запроса

Следующие анимации показывают, как обрабатывается приведённый выше запрос, когда предложение PREWHERE применяется ко всем условиям запроса.

Первые три шага обработки такие же, как и раньше:

① Запрос включает фильтр по столбцу town, который входит в первичный ключ таблицы, а значит — и в первичный индекс.

② Как и при выполнении без предложения PREWHERE, для ускорения запроса ClickHouse загружает первичный индекс в память,

③ а затем просматривает записи индекса, чтобы определить, какие гранулы столбца town могут содержать строки, соответствующие условию.

Теперь, благодаря предложению PREWHERE, следующий шаг выглядит иначе: вместо того чтобы сразу читать все нужные столбцы, ClickHouse фильтрует данные по одному столбцу за раз, загружая только то, что действительно необходимо. Это значительно сокращает объём I/O, особенно для широких таблиц.

На каждом шаге загружаются только те гранулы, которые содержат хотя бы одну строку, прошедшую — то есть удовлетворившую — предыдущему фильтру. В результате число гранул, которые нужно загружать и проверять для каждого фильтра, монотонно уменьшается:

Шаг 1: Фильтрация по town
ClickHouse начинает обработку PREWHERE с ① чтения выбранных гранул столбца town и проверки, какие из них действительно содержат строки, соответствующие London.

В нашем примере все выбранные гранулы подходят, поэтому ② для обработки выбираются соответствующие им позиционно выровненные гранулы следующего столбца фильтра — date:

Шаг 2: Фильтрация по date
Далее ClickHouse ① читает выбранные гранулы столбца date, чтобы проверить условие date > '2024-12-31'.

В этом случае две из трёх гранул содержат подходящие строки, поэтому ② для дальнейшей обработки выбираются только их позиционно выровненные гранулы из следующего столбца фильтра — price:

Шаг 3: Фильтрация по price
Наконец, ClickHouse ① читает две выбранные гранулы столбца price, чтобы проверить последнее условие price > 10_000.

Только одна из двух гранул содержит подходящие строки, поэтому ② для дальнейшей обработки нужно загрузить только её позиционно выровненную гранулу из столбца SELECT — street:

На последнем шаге загружается только минимальный набор гранул столбцов — тех, что содержат подходящие строки. Это снижает использование памяти, уменьшает дисковый I/O и ускоряет выполнение запроса.

PREWHERE уменьшает объём читаемых данных, а не количество обрабатываемых строк

Обратите внимание: ClickHouse обрабатывает одинаковое количество строк как в версии запроса с PREWHERE, так и без него. Однако при использовании оптимизации PREWHERE не все значения столбцов нужно загружать для каждой обрабатываемой строки.

​

Оптимизация PREWHERE применяется автоматически

Предложение PREWHERE можно добавить вручную, как показано в примере выше. Однако писать PREWHERE вручную не требуется. Когда включена настройка optimize_move_to_prewhere (по умолчанию true), ClickHouse автоматически переносит условия фильтрации из WHERE в PREWHERE, отдавая приоритет тем, которые сильнее всего сокращают объем чтения.

Идея состоит в том, что столбцы меньшего размера сканируются быстрее, и к моменту обработки более крупных столбцов большинство гранул уже отфильтровано. Поскольку во всех столбцах одинаковое количество строк, размер столбца в первую очередь определяется его типом данных: например, столбец UInt8 обычно значительно меньше, чем столбец String.

Начиная с версии 23.2, ClickHouse по умолчанию использует эту стратегию, сортируя столбцы фильтра PREWHERE для многоэтапной обработки по возрастанию несжатого размера.

Начиная с версии 23.11, необязательная статистика столбцов может дополнительно повысить эффективность, выбирая порядок обработки фильтров на основе фактической селективности данных, а не только размера столбцов.

​

Как измерить влияние PREWHERE

Чтобы убедиться, что PREWHERE действительно улучшает выполнение запросов, можно сравнить их производительность при включенном и отключенном параметре optimize_move_to_prewhere.

Начнем с выполнения запроса при отключенном параметре optimize_move_to_prewhere:

SELECT
    street
FROM
   uk.uk_price_paid_simple
WHERE
   town = 'LONDON' AND date > '2024-12-31' AND price < 10_000
SETTINGS optimize_move_to_prewhere = false;

   ┌─street──────┐
1. │ MOYSER ROAD │
2. │ AVENUE ROAD │
3. │ AVENUE ROAD │
   └─────────────┘

3 rows in set. Elapsed: 0.056 sec. Processed 2.31 million rows, 23.36 MB (41.09 million rows/s., 415.43 MB/s.)
Peak memory usage: 132.10 MiB.

ClickHouse прочитал 23.36 MB данных из столбцов при обработке 2.31 миллиона строк во время выполнения запроса.

Далее выполним запрос с включённым параметром optimize_move_to_prewhere. (Обратите внимание: этот параметр необязателен, так как он включён по умолчанию):

SELECT
    street
FROM
   uk.uk_price_paid_simple
WHERE
   town = 'LONDON' AND date > '2024-12-31' AND price < 10_000
SETTINGS optimize_move_to_prewhere = true;

   ┌─street──────┐
1. │ MOYSER ROAD │
2. │ AVENUE ROAD │
3. │ AVENUE ROAD │
   └─────────────┘

3 rows in set. Elapsed: 0.017 sec. Processed 2.31 million rows, 6.74 MB (135.29 million rows/s., 394.44 MB/s.)
Peak memory usage: 132.11 MiB.

Было обработано то же количество строк (2,31 миллиона), но благодаря PREWHERE ClickHouse прочитал более чем в три раза меньше данных из столбцов — всего 6,74 МБ вместо 23,36 МБ, — что сократило общее время выполнения в 3 раза.

Чтобы лучше понять, как ClickHouse применяет PREWHERE «за кулисами», используйте EXPLAIN и трассировочные логи.

Рассмотрим логический план запроса с помощью конструкции EXPLAIN:

EXPLAIN PLAN actions = 1
SELECT
    street
FROM
   uk.uk_price_paid_simple
WHERE
   town = 'LONDON' and date > '2024-12-31' and price < 10_000;

...
Prewhere info                                                                                                                                                                                                                                          
  Prewhere filter column: 
    and(greater(__table1.date, '2024-12-31'_String), 
    less(__table1.price, 10000_UInt16), 
    equals(__table1.town, 'LONDON'_String)) 
...

Здесь мы опускаем большую часть вывода плана, так как он довольно объёмный. По сути, он показывает, что все три предиката по столбцам были автоматически перенесены в PREWHERE.

Если вы воспроизведёте это самостоятельно, то также увидите в плане запроса, что порядок этих предикатов определяется размером типов данных столбцов. Поскольку мы не включили статистику по столбцам, ClickHouse использует размер как резервный критерий для определения порядка обработки в PREWHERE.

Если вы хотите заглянуть ещё глубже во внутренние механизмы, можно проследить каждый отдельный шаг обработки PREWHERE, указав ClickHouse возвращать все записи журнала уровня test во время выполнения запроса:

SELECT
    street
FROM
   uk.uk_price_paid_simple
WHERE
   town = 'LONDON' AND date > '2024-12-31' AND price < 10_000
SETTINGS send_logs_level = 'test';

...
<Trace> ... Condition greater(date, '2024-12-31'_String) moved to PREWHERE
<Trace> ... Condition less(price, 10000_UInt16) moved to PREWHERE
<Trace> ... Condition equals(town, 'LONDON'_String) moved to PREWHERE
...
<Test> ... Executing prewhere actions on block: greater(__table1.date, '2024-12-31'_String)
<Test> ... Executing prewhere actions on block: less(__table1.price, 10000_UInt16)
...

​

Ключевые выводы

• PREWHERE позволяет не читать данные столбцов, которые впоследствии будут отфильтрованы, экономя I/O и память.
• Это происходит автоматически, если включен optimize_move_to_prewhere (по умолчанию).
• Порядок фильтрации важен: небольшие и селективные столбцы должны идти первыми.
• Используйте EXPLAIN и журналы, чтобы проверить, применяется ли PREWHERE, и понять его влияние.
• PREWHERE дает наибольший эффект на широких таблицах и при сканировании больших объемов данных с селективными фильтрами.