ClickHouse / ClickHouse/ClickHouse
`dictGet` with direct dictionaries is up to 2x slower after column-native IN/set constant handling
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
The performance test `direct_dictionary` shows that `dictGet` with direct dictionaries became substantially slower on master compared to the 26.7 release base — 16 query variants regressed at once, up to 2x for simple-key lookups.
Representative queries (from `tests/performance/direct_dictionary.xml`), measured in the master CI comparison against the 26.7 release base (ARM):
| query | 26.7 base | master | change |
|---|---|---|---|
| simple key, `value_int`, 75k lookups | 0.220 s | 0.435 s | **+98%** |
| simple key, tuple of 4 attributes, 75k lookups | 0.225 s | 0.440 s | **+96%** |
| simple key, `value_decimal`, 50k lookups | 0.158 s | 0.296 s | **+88%** |
| simple key, `value_string_nullable`, 50k lookups | 0.160 s | 0.297 s | **+86%** |
| complex key, `value_string`, 50k lookups | 0.476 s | 0.631 s | **+33%** |
| complex key, `value_int`, 75k lookups | 0.692 s | 0.910 s | **+32%** |
```sql
WITH rand64() % toUInt64(75000) as key
SELECT dictGet('default.simple_key_direct_dictionary', 'value_int', key)
FROM system.numbers
LIMIT 75000
FORMAT Null;
```
The regression first appeared in the per-commit (`master_head`) comparison at merge commit ce2fb4e26e8156e469449866f7d64b0e7de768ba on 2026-08-12, flagged on both AMD and ARM (16 query variants). A direct dictionary executes a source query per `dictGet` block and builds a set/filter from the requested keys, so it is plausible that the new column-native IN/set constant handling made that per-lookup path more expensive.
Notably, on that commit the per-commit comparison showed exactly 10 slower queries per shard, one under the `SLOWER_QUERIES_FAIL_THRESHOLD = 10` gate (which is `> 10`), so the check stayed green and the regression landed silently.
Caused by: https://github.com/ClickHouse/ClickHouse/pull/113051
Report (ARM, vs release base): https://s3.amazonaws.com/clickhouse-test-reports/json.html?REF=master&sha=29cf83dac1355231265f4893603d31dfcfb577f1&name_0=MasterCI&name_1=Performance%20Comparison%20%28arm_release%2C%20release_base%2C%203%2F6%29
Dashboard: https://performance.ci.clickhouse.com/runs?scope=master&q=ce2fb4e26e81
This is the largest contributor to the accumulated regressions that currently keep the `Performance Comparison (arm_release, release_base, 3/6)` check permanently red on master (28 slower queries against a gate of 10).
cc @yakov-olkhovskiy
Contributor guide
Research direction
Start with tests/performance/direct_dictionary.xml and reproduce the listed dictGet variants against the 26.7 release base and master on the affected benchmark setup. Then inspect the change introduced by PR 113051 and the direct-dictionary lookup path. Done means identifying and correcting the regression so the benchmark results return close to the release-base timings and the performance comparison no longer accumulates these slower queries.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases, performance
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Needs clarification
- Newbie friendliness
- 35/100