ARRAY JOIN / array string-filter executor regressions: Nested +25% in 25.9, all shapes +30–75% in 26.1, Nested ×2 in 26.6

[MaXal]

Company or project name

Company or project name: JetBrains — IJ Perf, the IntelliJ performance dashboard (https://github.com/JetBrains/ij-perf-report-aggregator)

Describe the situation

While evaluating an upgrade from 24.3 LTS we found that every dashboard chart query regressed
20–55% on 26.3/26.6. These queries unnest a Nested column of per-report measurements
(ARRAY JOIN measures) and filter on the measurement name. A per-month bisect on a
self-contained synthetic repro (below) shows three separate steps:

1. v25.9 — ARRAY JOIN over a Nested column got ~25% slower (single-array shapes unaffected).
2. v26.1 — filtering unnested string array elements got 30–75% slower across every shape we
   tested: plain Array(String), Array(LowCardinality(String)), Nested, and even
   arrayCount(x -> x = c, arr) with no ARRAY JOIN at all.
3. v26.6 — ARRAY JOIN over Nested with LowCardinality takes another large step
   (~1.9× vs 25.8 overall).

Each step is present in the earliest published release of its series (v25.9.2.1, v26.1.1.912),
so these are not backports into later patches. (v26.1.1.912 was in fact worse — 1238 ms on q4
below vs 1039 ms on v26.1.12.23 — later 26.1 patches recovered part of it.)

The loss is executor CPU, not I/O or planning: read_rows, selected marks, and read bytes are
identical across versions, and single-threaded ratios match multi-threaded ones.

Which ClickHouse versions are affected?

report is our production schema: measures is
Nested(name LowCardinality(String), value Int32, type LowCardinality(String)) on a MergeTree
with a handful of LowCardinality dimension columns; same generator, same 52M elements.
Apple M4 Max/ 64 GB; median of 5 runs inside one clickhouse local process;
each version reads data it generated itself (content is deterministic and identical). Latest
published patch of each series:

#	query shape	25.8.28.1	25.9.7.56	25.10.7.6	25.12.11.4	26.1.12.23	26.3.17.56	26.6.2.81
q1	ARRAY JOIN arr AS x WHERE x = c, Array(String)	79	81	68	66	114	118	154
q2	same, Array(LowCardinality(String))	82	87	86	82	146	147	144
q3	ARRAY JOIN measures WHERE measures.name = c (Nested)	206	261	257	250	260	261	387
q4	q3 with max_threads = 1	759	930	907	916	1039	1065	1402
q5	sum(arrayCount(x -> x = c, measures.name)) — no ARRAY JOIN	254	253	253	247	311	327	324
q6	full dashboard query (time filter, machine LIKE, ORDER BY, 9 output columns)	239	247	241	233	308	314	362

(ms; bold = where a step lands. 25.11 not tested: 25.12 is clean for the 26.1 step and the
25.9 step had already landed.)

How to reproduce

How to reproduce

Generate deterministic data (identical content on every version) and run one
query per version — official prebuilt binaries, macos-aarch64:

-- clickhouse local --path <fresh dir per version>
CREATE TABLE t (arr Array(String)) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO t

SELECT arrayMap(i -> concat('some.metric.name.of.realistic.len.', toString(cityHash64(number, i) % 15000)),
                range(if(cityHash64(number, 5) % 10 = 0, 726, 16)))
FROM numbers(600000);
OPTIMIZE TABLE t FINAL;
-- 52,026,760 array elements total; ~15k distinct ~35-char strings; skewed lengths (90% × 16, 10% × 726).
-- The filter matches 3,420 elements (dashboards routinely filter on rare or absent names):
SELECT count() FROM t ARRAY JOIN arr AS x WHERE x = 'some.metric.name.of.realistic.len.7777';

This query (q1 below) runs in 66 ms on v25.12.11.4 and 114 ms on v26.1.12.23 (1.7×) on
the same machine.

queries.sql
repro.sql
run.sh

Reading the steps:

• 25.9 hits only the Nested shapes (q3: 206→261, q4: 759→930); q1/q2/q5/q6 are flat.
• 26.1 hits every string-filter shape: q1 +73%, q2 +78%, q5 +26%, q6 +32% vs 25.12, and
  stacks another +13% onto Nested q4. q5 shows it is not specific to ARRAY JOIN — the same
  work as an arrayCount lambda regresses identically.
• 26.6 adds a Nested(+LowCardinality)-specific step: q3 261→387, q4 1065→1402.
• Aside: 25.10 improved q1 (79→68 ms), which the 26.1 step then more than undoes.

Expected performance

Query latency on 26.x comparable to 25.8/25.12 for ARRAY JOIN / array-filter workloads.
The 26.1 step currently blocks our 26.3 LTS upgrade (dashboard p50 is dominated by exactly
this query class).

Related issues and pull requests

The profile resembles #91430, but this repro contains no multiIf and the analyzer is on in both versions.

Additional context

• SETTINGS enable_analyzer = 0 on 26.3 recovers the Nested shape only partially
  (261 → 233 ms; 25.8 is 206 ms). compatibility = '24.3' had no effect in our full
  evaluation. Both versions run the new analyzer by default, so this is not a
  settings-default change.
• In our production-shaped evaluation (45 real dashboard queries, restored production data,
  24.3 baseline): all ARRAY JOIN measures queries were 1.2–1.6× slower on 26.3/26.6 with
  identical result rows, while peak memory of heavy aggregations dropped ~4× (164 → 43 MB on
  26.6).

[benj-chen]

Hi, I'd like to investigate this :) .

[benj-chen]

To make a baseline, I ran your script on my computer (official binaries, linux amd64, 16gb of ram, 16 cores).

query	25.8.28.1	25.9.7.56	25.10.7.6	25.12.11.4	26.1.12.23	26.3.17.56	26.6.2.81
q1	905	881	592	592	596	630	609
q2	406	404	406	401	401	403	336
q3	620	782	763	760	601	591	591
q4	1370	1675	1652	1623	1357	1348	1657
q5	555	568	563	568	561	562	558
q6	598	581	579	570	575	587	555

I also ran everything with max_threads = 1 just in case thread count might have been an issue:

query (max_threads=1)	25.8.28.1	25.9.7.56	25.10.7.6	25.12.11.4	26.1.12.23	26.3.17.56	26.6.2.81
q1	1049	1103	890	893	901	899	866
q2	1373	1377	1356	1356	1363	1393	1374
q3	1428	1737	1730	1704	1404	1413	1761
q4 (same as q3)	1429	1732	1712	1708	1402	1410	1759
q5	1287	1283	1258	1267	1252	1266	1249
q6	1474	1444	1437	1424	1423	1444	1656

Notably, I don't see the 26.1 step that you mention at all. In fact, on some queries, at that version the execution time goes down rather than up. I can't think of much that would explain this other than a difference in OS. Not really sure what's happening there.

I see the other two steps reproduced in the test, so that's my new scope and I'll look at those.

[benj-chen]

@MaXal according to #99587:

Version info
Merged into: 26.3.1.824
Backported to: 26.2.5.44, 26.1.5.43, 25.8.22.9

so 25.8.28.1 has the same improved code (a new analyzer pass) as 26.1.12.23.

This explains what happens on my system. 25.8.28.1 has backported ARRAY JOIN logic that is also in 26.1.12.23, so the execution time for q3/q4 is similar for the two but is elevated for versions I tested in between. This is shown when I tested previous 25.8 releases:

query	25.8.3.66	25.8.4.13	25.8.7.3	25.8.20.4	25.8.21.7	25.8.22.28	25.8.23.13	25.8.26.11	25.8.28.1
q1	1052 ms	996 ms	987 ms	1053 ms	1008 ms	1014 ms	988 ms	1016 ms	1008 ms
q2	489 ms	491 ms	509 ms	460 ms	508 ms	477 ms	499 ms	486 ms	484 ms
q3	946 ms	918 ms	910 ms	883 ms	909 ms	724 ms	716 ms	689 ms	697 ms
q4	1843 ms	1918 ms	1837 ms	1845 ms	1821 ms	1492 ms	1487 ms	1489 ms	1499 ms
q5	598 ms	588 ms	632 ms	572 ms	590 ms	656 ms	615 ms	626 ms	612 ms
q6	682 ms	681 ms	666 ms	687 ms	656 ms	685 ms	673 ms	674 ms	676 ms

Basically, the 25.9 step isn't a regression. The 26.1 step down probably exists on the Mac release in the same way that it does for Linux, so I think that there may be some very large problem introduced in the Mac release of 26.1 that more than offsets the performance gain seen on Linux.

I'll continue to investigate the 26.6 step.

[MaXal]

@benj-chen Thank you for looking into this! I re-run one more time to make sure that this was not some glitch on my side.

The results are pretty-much the same.

Results (ms, median of 5)

query	25.8.28.1	25.9.2.1	25.9.7.56	25.10.7.6	25.12.11.4	26.1.1.912	26.1.12.23	26.3.17.56	26.6.2.81
q1 ARRAY JOIN, Array(String)	77	73	76	64	64	105	108	110	124
q2 same, Array(LowCardinality(String))	82	79	81	78	77	140	143	146	134
q3 ARRAY JOIN measures (Nested)	201	250	252	249	243	307	248	248	374
q4 q3, max_threads = 1	739	884	879	872	862	1192	957	957	1330
q5 arrayCount, no ARRAY JOIN	248	248	249	247	244	307	300	299	304
q6 full dashboard query	237	242	234	235	227	295	296	292	348

[MaXal]

@benj-chen Looks like I found the reason of macOS degradation!

The 26.1 regression is jemalloc dirty_decay_ms being set to 0 in the macOS build config — #93360. Embedded conf string per binary (via strings):

• ≤ 25.12: oversize_threshold:0,muzzy_decay_ms:0,dirty_decay_ms:5000,lg_extent_max_active_fit:8
• ≥ 26.1: same but dirty_decay_ms:0

Proof by runtime override (restores the old value; env var is JE_MALLOC_CONF on 26.1–26.3, plain MALLOC_CONF on 26.6 after the jemalloc fork re-integration):

query (max_threads=1)	25.12	26.1 default	26.1 + dirty_decay_ms:5000
pure read NOT ignore(arr)	305	398	304
q1	502	692	500
q2 (LC)	821	1006	825
q5	666	800	665

It was already reported in #108429

I rerun on 26.7.1.135.

Rerun (ms, median of 5):

query	25.8.28.1	25.12.11.4	26.6.2.81	26.7.1.1315
q1	74	67	120	87
q2	80	76	139	91
q3	196	243	375	236
q4	729	856	1331	863
q5	247	244	295	245
q6	236	228	341	237

So the jemalloc fix works as far as it goes: q5/q6 fully recover, and the 26.6 Nested step disappears (q3 375→236, q4 1331→863). What remains in 26.7 is not allocator-related:

• the 25.9 Nested step: q3 236 vs 196, q4 863 vs 729 (~+20% vs 25.8);
• a q1/q2 flat-array ARRAY JOIN residual (~+20–30% vs 25.12; 603 vs 513 ms single-threaded), introduced in the 26.5/26.6 timeframe and insensitive to every jemalloc knob tested (prof:false, prof_active:false, percpu_arena:disabled, decay).

[MaXal]

More investigations about 25.9 step = ARRAY JOIN subcolumn-pruning loss (fixed in 26.1.5+).

The pruning gap is a known analyzer issue, and the "25.9 step" is a bisection artifact of patch-release timing, not a 25.9 change:

• Analyzer: ARRAY JOIN with Nested reads all subcolumns #74878 (novikd, Jan 2025): "Analyzer: ARRAY JOIN with Nested reads all subcolumns" — exactly the q3 shape. The optimization has been missing since the new analyzer became the default; the old PruneArrayJoinColumnsPass existed pre-analyzer. (Matches the issue's observation that enable_analyzer=0 on 26.3 partially recovers Nested.)
• Performance regression with analyzer: ARRAY JOIN with implicitly nested columns #91855 (fm4v, Dec 2025): "Performance regression with analyzer: ARRAY JOIN with implicitly nested columns" — customer case, 24.5 s with analyzer vs 0.29 s without, "version doesn't matter".
• Fix: PR Prune unused columns from ARRAY JOIN #99587 (novikd, merged 2026-03-18): "Prune unused columns from ARRAY JOIN" — re-adds PruneArrayJoinColumnsPass at query-tree level. Merged into 26.3.1.824; backported to 26.2.5.44, 26.1.5.43, 25.8.22.9. The 25.12 backport (Backport #99587 to 25.12: Prune unused columns from ARRAY JOIN #99965) was closed unmerged, and 25.9/25.10 are EOL — those series never got it.

So in the benchmark: 25.8.28.1 prunes only because 25.8 LTS ≥ 25.8.22.9 carries the backport; 25.9.x/25.10.x/25.12.x never prune; 26.1.1.912 predates the fix (this is most of why the issue saw 26.1.1.912 worse, "partially recovering" in later 26.1 patches = the 26.1.5.43 backport); 26.1.12.23/26.3.17.56/26.6+ prune.

Verified empirically: EXPLAIN QUERY TREE shows JOIN EXPRESSIONS pruned to 1 node on 25.8.28.1 and 26.1.12.23, all 3 nodes on 25.9.2.1; CompressedReadBufferBytes 153 MB vs 413 MB accordingly.

[MaXal]

I just couldn't stop myself and tried to investigate the 26.6 step.

Bisect with allocator normalized (MALLOC_CONF=dirty_decay_ms:5000, q4 single-thread): 26.3 = 699, 26.4.5.143 = 697, 26.5.6.64 = 701, 26.6.2.81 = 1045 ms — the step is exactly in 26.6.

Believed cause: PR #98974 "Fix nested() function stripping LowCardinality from types" (ships in 26.6; closes #95582).
A 2-line correctness fix: nested() (used internally by ARRAY JOIN over Nested) now preserves LowCardinality in its return type.

• 26.5 query tree: __array_join_exp_1 :: Tuple(name String) — LC stripped once up front, block splitting is plain memcpy.
• 26.6 query tree: Tuple(name LowCardinality(String)) — LC preserved inside ColumnTuple.

Equal-work profile (26.5 vs 26.6, both alloc-normalized): the entire delta is LC dictionary rebuilding, hot path
ArrayJoinResultIterator::next → ColumnArray::insertRangeFrom → ColumnTuple::insertRangeFrom → ColumnLowCardinality::insertRangeFrom → ColumnUnique::uniqueInsertRangeFrom (mapUniqueIndex 0→793 samples, ReverseIndex::insert 124→909).

Every output-block split re-inserts the ~15k-entry dictionary; the LC-in-Tuple path has no shared-dictionary fast path. So a correctness fix (right types, and likely lower memory) traded away CPU: Nested+LC ARRAY JOIN +50%. 26.7 partially mitigates (879 ms) but stays ~+20% vs 26.5's 727 ms.

[benj-chen]

More investigations about 25.9 step = ARRAY JOIN subcolumn-pruning loss (fixed in 26.1.5+).

The pruning gap is a known analyzer issue, and the "25.9 step" is a bisection artifact of patch-release timing, not a 25.9 change:

Thanks for the detail! I'm happy you agree on the culprit.

I ran run.sh on versions around 26.6:

query	26.3.17.56	26.4.5.143	26.5.6.64	26.6.1.1193	26.6.2.81	26.7.1.1315
q1	626	626	614	601	602	604
q2	410	390	359	332	341	345
q3	588	587	610	578	576	412
q4	1376	1376	1352	1676	1676	1308
q5	563	563	571	558	566	561
q6	594	586	572	550	548	457

I found that in 26.7, q4 improved past 26.5, which seems to be another linux/macos difference. Do you think #110370 or #109825 have anything to do with it?