ClickHouse 26.10's string filter during scan cuts a selective LIKE query's memory 25-fold

A LIKE query against ten million rows used 37.6MB of peak memory on ClickHouse 26.9. The same table, the same query, same hardware, on 26.10 released six days ago: 1.48MB. Nothing else about the query changed.

ClickHouse 26.10 shipped on 6 November 2026 with a new setting, apply_string_filters_during_scan, on by default. It pushes substring search conditions down into the column scan itself, so rows that don't match never get fully materialised. I wanted to know what that's actually worth, where it stops working, and what a reader would hit if they relied on it without reading the fine print.

What the setting does

The description in system.settings is unusually precise for a ClickHouse setting:

When a PREWHERE condition contains a conjunct that searches for a non-empty substring in a String column (LIKE, position, startsWith, endsWith, or equality with a non-empty string), the reader checks every value against this condition during deserialization and reads non-matching values as empty strings. 

It also says the filter "is disabled adaptively at runtime if it turns out to be non-selective." That line turned out to matter a lot once I started testing.

Building something to test against

I generated a ten-million-row MergeTree table with a body column of roughly 1.3KB of repeated filler text per row, with one row in a hundred containing the word "wonderful" somewhere in the middle. I deliberately made the matching and non-matching rows the same length, so any memory difference would come from selectivity, not from one branch of the data happening to be shorter:

CREATE TABLE reviews (id UInt64, body String) ENGINE = MergeTree ORDER BY id SETTINGS min_bytes_for_wide_part = 0, min_rows_for_wide_part = 0; INSERT INTO reviews SELECT number, if(number % 100 = 0, concat('this product was absolutely wonderful and exceeded expectations. ', repeat('filler padding text to pad out the row length evenly ', 20)), repeat('ordinary filler padding text that never matches the needle word ', 20)) FROM numbers(10000000); 

That's 103MB on disk, 1% of rows matching LIKE '%wonderful%'.

The headline number, checked against the previous version

Toggling a setting within one binary can hide a difference if the code path isn't really what you think it is. So I ran the same query three ways: ClickHouse 26.9.27.3 (which doesn't have this setting at all), 26.10.3.7 with it forced off, and 26.10.3.7 at its default. Three runs each, single-threaded for determinism, peak memory read from system.query_log.memory_usage after SYSTEM FLUSH LOGS.

26.9 and 26.10-with-the-setting-off land in the same place, which is what you'd want to see: it confirms the setting, not some other change in the release, is doing the work. At 1% selectivity, the win is roughly 25 times less memory and about 35-40% faster.

The number that argues against it

The settings description says the filter gets "disabled adaptively" when it isn't selective. I tested that directly by querying for LIKE '%filler%' against the same table — a word that appears in both the matching and non-matching rows, so it matches 100% of the ten million rows:

It did not disable. Memory usage with the setting on was still lower — about 32% lower, not 25x — but the time advantage disappeared entirely; across three runs each, the "on" case was not reliably faster than "off," and in two of three comparisons it was a touch slower. "Disabled adaptively" reads like the optimiser backs off completely once selectivity drops. What I actually saw was a much smaller, partial benefit that persists even at 100% selectivity, with no time benefit at all. If you're deciding whether to rely on this for a non-selective filter, don't: there's a small memory saving and no speed saving.

What it refuses

The settings description names exactly which predicates qualify: LIKE, position, startsWith, endsWith, and equality with a non-empty string. I tried two predicates that look similar but aren't on that list.

match(), ClickHouse's regex function, got nothing:

Identical memory, both ways. If you're using match() because you reached for regex out of habit, switching to LIKE where the pattern allows it is worth doing in 26.10 specifically, because it's the only one of the two that benefits here.

NOT LIKE, excluding the same 1%-selective term, also got nothing:

That one makes sense once you think about it: excluding a rare term means 99% of rows pass the filter, so from the optimiser's point of view it's a non-selective predicate, consistent with the adaptive-disable behaviour I saw above, just more completely disabled this time.

The more surprising refusal was plain equality. The docs list "equality with a non-empty string" as covered, so I expected WHERE body = '<the full matching string>' to behave like LIKE:

No difference at all — the exact same non-result as match().

What I got wrong on the way

My first reaction to that equality result was to conclude the docs were simply wrong about equality being covered. I was about to write that up as the headline refusal finding. Before doing that I ran EXPLAIN actions=1 on both queries to see what was actually happening at the plan level:

-- the LIKE query Prewhere filter Prewhere filter column: body LIKE '%wonderful%' -- the equality query └──Filter ((WHERE + Change column names to column identifiers)) 

The LIKE condition gets automatically promoted from WHERE into PREWHERE. The equality condition does not — it stays a plain post-scan Filter, and apply_string_filters_during_scan only does anything inside PREWHERE. My test wasn't exercising the feature at all for equality; it was testing a query shape that never reaches the optimisation in the first place. The docs are more precise than I'd given them credit for: "equality with a constant string is not moved" into PREWHERE by this setting — it only applies "when it is already in PREWHERE." I'd misread "not moved for this reason" as "not accelerated," when it actually means the move itself doesn't happen, but the acceleration still does if you put it there yourself.

The workaround, and it works

Rewriting the same query with an explicit PREWHERE instead of WHERE recovers the benefit completely:

That's the real number for equality: about 12 times less memory, once the condition is actually where the setting looks for it. The practical takeaway is specific and actionable: an ordinary analyst typing WHERE col = 'value' against a wide text column gets nothing from this release's headline memory improvement, regardless of the setting, unless they write PREWHERE themselves. LIKE doesn't have this problem because ClickHouse promotes it automatically; equality doesn't, and the gap between "works for me" and "works for you" here is a single keyword most people won't think to add.

Under concurrency

I ran the original 1%-selective LIKE query from ten concurrent clickhouse-client processes, default threading, timing the whole batch:

The gap held up under load — if anything it widened slightly. Sampling the container's memory with docker stats during the burst showed a peak around 763MB with the setting on against roughly 1.3GB with it off, in line with ten queries each carrying their own ~37MB-versus-~1.5MB difference plus server overhead.

Watching it in production

There's no dedicated counter for this specific setting in system.events, but the ordinary query_log columns show it per query without any extra instrumentation:

SELECT query_duration_ms, memory_usage FROM system.query_log WHERE query_id = '<id>' AND type = 'QueryFinish'; 

If you're upgrading and want to see the effect on your own workload rather than trust a release note, that's the column to watch before and after.

Run it yourself

This needs Docker with a running daemon. Each image is a few hundred megabytes.

docker run -d --name ch26 -p 18123:8123 \ -e CLICKHOUSE_PASSWORD=test1234 clickhouse/clickhouse-server:26.10 docker exec ch26 clickhouse-client --password test1234 --multiquery --query " CREATE TABLE reviews (id UInt64, body String) ENGINE = MergeTree ORDER BY id SETTINGS min_bytes_for_wide_part = 0, min_rows_for_wide_part = 0; INSERT INTO reviews SELECT number, if(number % 100 = 0, concat('this product was absolutely wonderful and exceeded expectations. ', repeat('filler padding text to pad out the row length evenly ', 20)), repeat('ordinary filler padding text that never matches the needle word ', 20)) FROM numbers(10000000); " # compare memory with the setting on vs off docker exec ch26 clickhouse-client --password test1234 --query " SELECT count() FROM reviews WHERE body LIKE '%wonderful%' SETTINGS apply_string_filters_during_scan = 1, max_threads = 1 FORMAT Null" docker exec ch26 clickhouse-client --password test1234 --query "SYSTEM FLUSH LOGS" docker exec ch26 clickhouse-client --password test1234 --query " SELECT query_duration_ms, memory_usage FROM system.query_log WHERE query LIKE '%wonderful%' AND type = 'QueryFinish' ORDER BY event_time DESC LIMIT 1" 

Change apply_string_filters_during_scan to 0 and re-run to see the before-and-after yourself. I ran every command in this post against the same container and dataset the numbers above came from.

What to do with this

If you're already running 26.10, check which of your text filters are written as LIKE, startsWith, or endsWith against a MergeTree table — those get this for free, with no code change. If you have hot queries written as WHERE col = 'value' against a wide String column, check EXPLAIN actions=1 for a Prewhere filter node before assuming you're getting the same win; if it's a plain Filter instead, rewriting as PREWHERE col = 'value' is a one-word change with a real memory payoff on this release. And if your filter is non-selective — excluding a rare value, or matching most of the table — don't expect the default to save you much; test your own selectivity rather than assume the setting degrades gracefully to "no worse than before," because on a 100%-selective case it still used less memory but gained no speed at all.