v.24.3Improvement

Improved Usability of Virtual Columns in PREWHERE and Enhanced Documentation in ClickHouse

Official CH changelog text
Now we can use virtual columns in PREWHERE. It's worthwhile for non-const virtual columns like _part_offset. #59033 (Amos Bird). Improved overall usability of virtual columns. Now it is allowed to use virtual columns in PREWHERE (it's worthwhile for non-const virtual columns like _part_offset). Now a builtin documentation is available for virtual columns as a comment of column in DESCRIBE query with enabled setting describe_include_virtual_columns. #60205 (Anton Popov).
ClickHouse now supports using virtual columns in the PREWHERE clause, improving query flexibility and performance for non-constant virtual columns like _part_offset. Additionally, built-in documentation for virtual columns is introduced as comments in DESCRIBE query output when the setting describe_include_virtual_columns is enabled.

Why it matters

This feature enhances the usability of virtual columns by allowing them to be applied in the PREWHERE clause, which can optimize query efficiency by filtering data earlier in query execution. The documentation improvements help users better understand virtual columns directly from the table schema description, leading to easier management and usage of virtual columns.

How to use it

To leverage virtual columns in the filtering stage, simply include them in the PREWHERE clause of your queries, for example:
<code>SELECT</code> ... <code>PREWHERE _part_offset &gt; 1000</code>

To see virtual column documentation, enable the setting describe_include_virtual_columns and run:
<code>DESCRIBE TABLE table_name</code>

Related resources

PreviousNext