2025 Changelog

- The inverted text index was reworked from scratch to be scalable for datasets that don't fit into RAM. [#86485](https://github.com/ClickHouse/ClickHouse/pull/86485) ([Anton Popov](https://github.com/CurtizJ)).
- Join reordering now uses statistics. The feature can be enabled by setting `allow_statistics_optimize = 1` and `query_plan_optimize_join_order_limit = 10`. [#86822](https://github.com/ClickHouse/ClickHouse/pull/86822) ([Han Fei](https://github.com/hanfei1991)).
- Support `alter table ... materialize statistics all` will materialize all the statistics of a table. [#87197](https://github.com/ClickHouse/ClickHouse/pull/87197) ([Han Fei](https://github.com/hanfei1991)).

#### Performance Improvement[​](#performance-improvement-3 "Direct link to Performance Improvement")

- Support filtering data parts using skip indexes during reading to reduce unnecessary index reads. Controlled by the new setting `use_skip_indexes_on_data_read` (disabled by default). This addresses [#75774](https://github.com/ClickHouse/ClickHouse/issues/75774). This includes some common groundwork shared with [#81021](https://github.com/ClickHouse/ClickHouse/issues/81021). [#81526](https://github.com/ClickHouse/ClickHouse/pull/81526) ([Amos Bird](https://github.com/amosbird)).
- Added JOIN order optimization that can automatically reorder JOINs for better performance (controlled by `query_plan_optimize_join_order_limit` setting). Note that the join order optimization currently has limited statistics support and primarily relies on row count estimates from storage engines - more sophisticated statistics collection and cardinality estimation will be added in future releases. **If you encounter issues with JOIN queries after upgrading**, you can temporarily disable the new implementation by setting `SET query_plan_use_new_logical_join_step = 0` and report the issue for investigation. **Note about resolution of identifiers from USING clause**: Changed resolving of the coalesced column from `OUTER JOIN ... USING` clause to be more consistent: previously, when selecting both the USING column and qualified columns (`a, t1.a, t2.a`) in a OUTER JOIN, the USING column would incorrectly be resolved to `t1.a`, showing 0/NULL for rows from the right table with no left match. Now identifiers from USING clause are always resolved to the coalesced column, while qualified identifiers resolve to the non-coalesced columns, regardless of which other identifiers are present in the query. For example: ```sql SELECT a, t1.a, t2.a FROM (SELECT 1 as a WHERE 0) t1 FULL JOIN (SELECT 2 as a) t2 USING (a) -- Before: a=0, t1.a=0, t2.a=2 (incorrect - 'a' resolved to t1.a) -- After: a=2, t1.a=0, t2.a=2 (correct - 'a' is coalesced). [#80848](https://github.com/ClickHouse/ClickHouse/pull/80848) ([Vladimir Cherkasov](https://github.com/vdimir)).
- Distributed `INSERT SELECT` for data lakes. [#86783](https://github.com/ClickHouse/ClickHouse/pull/86783) ([scanhex12](https://github.com/scanhex12)).
- Improve PREWHERE optimization for conditions like `func(primary_column) = 'xx'` and `column in (xxx)`. [#85529](https://github.com/ClickHouse/ClickHouse/pull/85529) ([李扬](https://github.com/taiyang-li)).
- Implemented rewriting of JOIN: 1. Convert `LEFT ANY JOIN` and `RIGHT ANY JOIN` to `SEMI`/`ANTI` JOIN if the filter condition is always false for matched or non-matched rows. This optimization is controlled by a new setting `query_plan_convert_any_join_to_semi_or_anti_join`. 2. Convert `FULL ALL JOIN` to `LEFT ALL` or `RIGHT ALL` JOIN if the filter condition is always false for non-matched rows from one side. [#86028](https://github.com/ClickHouse/ClickHouse/pull/86028) ([Dmitry Novik](https://github.com/novikd)).
- Improved performance of vertical merges after executing a lightweight delete. [#86169](https://github.com/ClickHouse/ClickHouse/pull/86169) ([Anton Popov](https://github.com/CurtizJ)).
- `HashJoin` performance optimised slightly in the case of `LEFT/RIGHT` join having a lot of unmatched rows. [#86312](https://github.com/ClickHouse/ClickHouse/pull/86312) ([Nikita Taranov](https://github.com/nickitat)).
- Radix sort: help the compiler use SIMD and do better prefetching. Uses dynamic dispatch to use software prefetching with Intel CPUs only. Continues the work by @taiyang-li in <https://github.com/ClickHouse/ClickHouse/pull/77029>. [#86378](https://github.com/ClickHouse/ClickHouse/pull/86378) ([Raúl Marín](https://github.com/Algunenano)).
- Improves performance of short queries with lots of parts in tables (by optimizing `MarkRanges` by using `devector` over `deque`). [#86933](https://github.com/ClickHouse/ClickHouse/pull/86933) ([Azat Khuzhin](https://github.com/azat)).
- Improved performance of applying patch parts in the join mode. [#87094](https://github.com/ClickHouse/ClickHouse/pull/87094) ([Anton Popov](https://github.com/CurtizJ)).
- Added setting `query_condition_cache_selectivity_threshold` (default value: 1.0) which excludes scan results of predicates with low selectivity from insertion into the query condition cache. This allows to reduce the memory consumption of the query condition cache at the cost of a worse cache hit rate. [#86076](https://github.com/ClickHouse/ClickHouse/pull/86076) ([zhongyuankai](https://github.com/zhongyuankai)).
- Reduce memory usage in Iceberg writes. [#86544](https://github.com/ClickHouse/ClickHouse/pull/86544) ([scanhex12](https://github.com/scanhex12)).

#### Improvement[​](#improvement-3 "Direct link to Improvement")