v.21.12New Features

Added CONSTRAINT ASSUME feature and query optimization improvements in ClickHouse

Official CH changelog text
Added CONSTRAINT ... ASSUME ... (without checking during INSERT). Added query transformation to CNF (https://github.com/ClickHouse/ClickHouse/issues/11749) for more convenient optimization. Added simple query rewriting using constraints (only simple matching now, will be improved to support <,=,>... later). Added ability to replace heavy columns with light columns if it's possible. #18787 (Nikita Vasilev).
Introduced CONSTRAINT ... ASSUME ... syntax to define constraints that are assumed without verification during INSERT. Implemented query transformation to Conjunctive Normal Form (CNF) for enhanced query optimization and added simple constraint-based query rewriting with the ability to replace heavy columns with lighter ones when applicable.

Why it matters

This feature aims to improve query optimization by allowing the database engine to assume certain constraints without enforcing them on data insertion, reducing overhead. Transforming queries into CNF enables more effective optimizations, and rewriting queries based on constraints can simplify and speed up query processing. Replacing heavy columns with lighter ones reduces resource usage and improves performance.

How to use it

Define assumed constraints in your table schema or queries using the syntax:

CONSTRAINT <constraint_name> ASSUME <condition>


Use queries that benefit from the CNF transformation and rely on simple matching of constraints for optimization. The system automatically applies query rewriting and column replacement optimizations based on defined constraints.

Related resources

PreviousNext