ClickHouse 26.10 cuts selective LIKE query memory 25-fold with scan-time string filtering

What shipped in 26.10

ClickHouse 26.10 shipped on 6 November 2026 with a new setting, apply_string_filters_during_scan, enabled by default. According to dev.to, it pushes substring conditions on String columns down into the column reader: when a PREWHERE clause contains a LIKE, position, startsWith, endsWith or equality test against a non-empty string, every value is checked during deserialization and non-matching values are returned as empty strings rather than fully materialised. The setting's own description also says it disables itself adaptively when a condition turns out not to be selective, a claim the benchmark probed directly.

A 25-fold memory reduction

The dev.to test built a ten-million-row MergeTree table with roughly 1.3KB of text per row (103MB on disk), where one row in a hundred contains the needle word. Matching and non-matching rows were deliberately the same length, so memory differences would reflect selectivity rather than data shape. The same LIKE query ran on ClickHouse 26.9, on 26.10 with the setting forced off, and on 26.10 at its default, three runs each, single-threaded, with peak memory read from system.query_log.

Version 26.9 peaked at 37.6MB and 26.10 with the setting off at 37.7MB, which confirms the setting itself is doing the work rather than some unrelated change in the release. With the setting on, peak memory fell to 1.48MB, about 25 times lower, and runtime dropped from roughly 1.5 to 2.0 seconds to about one second, a 35 to 40 percent speedup.

Where it does not help

The documented list of qualifying predicates is narrow, and the benchmark found several lookalikes that get nothing. match(), ClickHouse's regex function, showed identical memory with the setting on and off. NOT LIKE also showed no change, which is consistent with the selectivity logic: excluding a term present in 1 percent of rows means 99 percent of rows still pass.

The adaptive-disable claim was tested with a pattern matching 100 percent of rows. Memory was still about 32 percent lower with the setting on (25.6MB versus 37.7MB), but the speed advantage vanished entirely, and the filtered runs were marginally slower in two of three comparisons. The dev.to conclusion: for non-selective filters, expect a modest memory saving and no time saving.

The equality catch

Equality with a non-empty string is on the documented list, yet a plain WHERE body = '...' query saw no difference at all. EXPLAIN showed why: ClickHouse automatically promotes LIKE conditions into PREWHERE, but equality stays a post-scan Filter, and the new optimisation only acts inside PREWHERE. Rewriting the query with an explicit PREWHERE recovered the benefit, at 1.50MB versus 17.82MB with the setting off, roughly a 12-fold reduction. The practical consequence is that an analyst typing plain equality against a wide text column gets nothing from this release's headline improvement unless they add the PREWHERE keyword themselves, while LIKE users benefit automatically.

Under concurrent load

Running the 1-percent-selective LIKE query from ten concurrent client processes, the whole batch finished in 3.18 seconds with the setting on versus 6.54 seconds with it off, and 3.03 versus 7.17 seconds on a repeat run. The advantage held up under load rather than eroding as concurrency increased.

Why it matters

Peak memory per query sets how many concurrent text scans a node can serve before it starts spilling or refusing work, so a 25-fold reduction on selective LIKE filters is a genuine capacity gain for the log- and event-analytics workloads where ClickHouse is common. But the edges matter as much as the headline: regex users and plain-equality queries see no benefit without rewriting, and non-selective patterns trade a small memory win for no speed win. Teams upgrading to 26.10 get the improvement for free on the common LIKE path, and should audit queries that rely on match() or equality against wide string columns if they want the same result.