v.23.10New Feature

Enhancement: Indexing with Space-Filling Curves in ClickHouse

Official CH changelog text
If a table has a space-filling curve in its key, e.g., ORDER BY mortonEncode(x, y), the conditions on its arguments, e.g., x >= 10 AND x <= 20 AND y >= 20 AND y <= 30 can be used for indexing. A setting analyze_index_with_space_filling_curves is added to enable or disable this analysis. This closes #41195. Continuation of #4538. Continuation of #6286. Continuation of #28130. Continuation of #41753. #55642 (Alexey Milovidov).
Adds support for index analysis on space-filling curves in table keys, allowing query conditions on the arguments of functions like mortonEncode(x, y) to be used for efficient indexing.

Why it matters

This feature improves query performance for tables that use space-filling curves in their ORDER BY keys by enabling analysis of filter conditions on the curve's input arguments (e.g., ranges on x and y). It helps the query planner leverage these conditions to better prune data during query execution.

How to use it

To enable analysis of indexes with space-filling curves, set the configuration parameter analyze_index_with_space_filling_curves to 1 (or true) in your server configuration or session settings. Then queries with conditions on arguments of the space-filling curve function in the key will be considered for index usage.

Related resources

PreviousNext