PREWHERE is an optimization applied automatically even if you write WHERE in the query. A separate keyword lets you manually tune this optimizer behavior if you know what you are doing.
SELECT column1, column2, column3 FROM table_name WHERE column1 BETWEEN 1 AND 1000
When you run this query, ClickHouse will read all three columns and filter out the rows you don’t want based on column 1 only.
You can optimize this by changing the WHERE to a PREWHERE.
SELECT column1, column2, column3 FROM table_name PREWHERE column1 BETWEEN 1 AND 1000
ClickHouse reads only column 1 and filters out any rows that do not match the requirement when executing this query. Once the filter has been applied, ClickHouse searches the remaining columns for rows that match the filter criteria. By filtering on a smaller set of columns, the data received from the disk can be drastically minimized.
By default, optimise_move_to_prewhere is enabled. However, it only enables the optimization pass that decides whether to move the conditions to PREWHERE based on some heuristics.
Note: Moving everything to PREWHERE is not always advantageous; for example, if there are two conditions, one of which is a very selective condition on a small column and the other on a large column, it is advantageous to move only the first condition to PREWHERE.