ClickHouse / ClickHouse/ClickHouse

`dictGet` with direct dictionaries is up to 2x slower after column-native IN/set constant handling

Open
#115,803 0 comments 0 reactions 0 assignees View on GitHub
comp-dictionary performance
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.