Unlock this content
Enter your email to unlock this content for free
PREWHERE
TL;DR
PREWHERE is like WHERE but filters data before reading all columns, making it more efficient for early filtering. Use PREWHERE for conditions that filter out many rows, especially on columns not needed in the SELECT clause. ClickHouse automatically moves simple WHERE conditions to PREWHERE when beneficial, but you can be explicit for better control.
PREWHERE is a powerful optimization technique in ClickHouse that filters data before reading all columns. Understanding when and how to use PREWHERE can significantly improve query performance.
What is PREWHERE?
PREWHERE is similar to WHERE but with a key difference: PREWHERE filters data before reading all columns from disk.
Execution order:
- PREWHERE: Filters data using only columns needed for filtering
- Read columns: Reads remaining columns only for rows that passed PREWHERE
- WHERE: Additional filtering after all columns are read (if needed)