ClickHouse / ClickHouse/ClickHouse

Do not read column granules which column values in table ORDER BY not changed.

Open
#15,427 3 comments 1 reaction 0 assignees View on GitHub
comp-mergetree external feature performance
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

Example:
We have a table and it has column values and mark distribution similar to that.
```
Whole data: [-----------------------------------------------------------------------------------------]
CounterID: [aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb]
Date: [11111111111111111111112222222222222222222222211111111111111111111111112222222222222222222]
Marks: | | | | | | | | | | | | | |
a,1 a,1 a,1 a,1 a,2 a,2 a,2 b,1 b,1 b,1 b,2 b,2 b,2 b,2
Marks numbers: 0 1 2 3 4 5 6 7 8 9 10 11 12 13
```
```
SELECT sum(col_D) FROM table WHERE CounterID IN (a,b) AND Date = 1
```

Because of clickhouse ORDER BY key, we do know, that values of columns (CounterID and DATE ) between marks 0-3 and 7-9 doesn't changed, so we can avoid of reading that columns ranges from disk and calculating conditions (CounterID IN (a,b) AND Date = 1) for that range.

Now clickhouse will read and calculate conditions for that marks range:
col_d: 0-4,6-10 = 8
CounterID: 0-4,6-10 = 8
Date: 0-4,6-10 = 8
~ 24 marks

After that change:
col_d: 0-4,6-10 = 8
CounterID: 3-4,6-7,9-10 = 3
Date: 3-4,6-7,9-10 = 3
~ 14 marks

**Describe the solution you'd like**
Avoid reading unneeded columns and calculating conditions, that will effectively reduce the amount of readed data from disk and cpu spent on decompressing and calculating conditions.

**Additional context**
I played a bit with a wide SSB benchmark, and the difference can be close to multiple times, when you have multiple WHERE conditions which is true on somewhere wide ranges and reading additional columns is expensive.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.