ClickHouse DB::Exception: PREWHERE not supported

The "DB::Exception: PREWHERE not supported" error in ClickHouse is raised when a query includes a PREWHERE clause against a table engine that does not support this optimization. The error code is ILLEGAL_PREWHERE. The PREWHERE clause is a ClickHouse-specific optimization that filters rows before reading all columns, and it is only available for MergeTree family tables.

Impact

The query fails without returning results. This is a query-level error and does not affect other operations or the server's health. It indicates that the query needs to be adjusted, either by replacing PREWHERE with WHERE or by using a table engine that supports PREWHERE.

Common Causes

1. Using PREWHERE on a non-MergeTree table engine such as Memory, Log, TinyLog, or Join.
2. Using PREWHERE on a Distributed table directly rather than letting ClickHouse push the filter down to the underlying MergeTree tables.
3. Migrating queries from a MergeTree table to a different engine without updating the query syntax.
4. Using PREWHERE on a view or subquery where the underlying source does not support it.
5. Applying PREWHERE to a table function like numbers(), url(), or file().

Troubleshooting and Resolution Steps

1. Check the table engine:

   SELECT engine FROM system.tables WHERE database = 'your_db' AND name = 'your_table';
   

2. Replace PREWHERE with WHERE. In most cases, the standard WHERE clause provides the same logical filtering:

   -- Instead of:
   -- SELECT * FROM your_table PREWHERE condition;
   
   -- Use:
   SELECT * FROM your_table WHERE condition;
   

3. If you need the performance benefits of PREWHERE, ensure you are querying a MergeTree family table. ClickHouse also automatically converts some WHERE conditions to PREWHERE on MergeTree tables when the optimizer determines it would be beneficial.

4. For Distributed tables backed by MergeTree, using WHERE is typically sufficient because ClickHouse will push down the filter and may apply PREWHERE optimization on each shard's local table automatically.

5. If you are building queries dynamically in application code, check the table engine before adding PREWHERE to the generated SQL.

Best Practices

• Use WHERE by default and let ClickHouse's query optimizer decide when to apply PREWHERE automatically on MergeTree tables.
• Only use explicit PREWHERE when you have benchmarked it and confirmed a performance improvement for your specific query pattern.
• When writing generic query builders or ORMs, avoid hardcoding PREWHERE unless you can verify the target table supports it.
• Prefer MergeTree family engines for analytical tables where PREWHERE optimization matters.

Frequently Asked Questions

Q: What is the difference between PREWHERE and WHERE?
A: PREWHERE filters rows by reading only the columns needed for the filter condition, then reads the remaining columns only for rows that pass. WHERE reads all requested columns first, then filters. PREWHERE can reduce I/O significantly when the filter is selective and involves fewer columns than the full query.

Q: Does ClickHouse automatically use PREWHERE?
A: Yes. On MergeTree tables, ClickHouse's optimizer can automatically move suitable WHERE conditions to PREWHERE. This behavior is controlled by the optimize_move_to_prewhere setting (enabled by default).

Q: Can I use PREWHERE on a Distributed table?
A: Not directly. Use WHERE on the Distributed table, and ClickHouse will push the filter to the underlying local tables where PREWHERE optimization can be applied automatically.

Q: Which table engines support PREWHERE?
A: All MergeTree family engines support PREWHERE: MergeTree, ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree, CollapsingMergeTree, and VersionedCollapsingMergeTree. Non-MergeTree engines do not support it.