ClickHouse / ClickHouse/ClickHouse

GROUPING SETS + grouping + if(grouping(key) = 1, uniqExact(column),0) optimization

Open
#37,757 5 comments 0 reactions 0 assignees View on GitHub
comp-aggregation performance
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

**Describe the situation**
Sometimes, you need to do multiple aggregations by different combinations of keys with different aggregate functions used.
And currently there is 3 ways how it can be done:
1. Multiple subqueries. (but ClickHouse will read the same table multiple times)
2. WINDOW functions. (but they are working in single thread)
3. GROUPING SETS + grouping function + if (but looks like it sign slower than method 1)

**How to reproduce**
```
┌─version()───┐
│ 22.6.1.1006 │
└─────────────┘

SELECT
number % 1000000 AS some_id,
number % 3000 AS another_id,
number % 236 AS last_id,
sum(number),
if(grouping(another_id) = 1, uniqExact(number), 0),
if(grouping(last_id) = 1, quantileExact(0.5)(number), 0)
FROM numbers(10000000)
GROUP BY
GROUPING SETS (
(some_id),
(another_id),
(last_id))
FORMAT `Null`

Query id: d827d2f1-91ac-4a96-a89e-7696a3894efe

Ok.

0 rows in set. Elapsed: 3.614 sec. Processed 10.02 million rows, 80.18 MB (2.77 million rows/s., 22.18 MB/s.)

Peak memory usage (for query): 1.92 GiB.

SELECT
number % 1000000 AS some_id,
0 AS another_id,
0 AS last_id,
sum(number),
0,
0
FROM numbers(10000000)
GROUP BY some_id
UNION ALL
SELECT
0 AS some_id,
number % 3000 AS another_id,
0 AS last_id,
sum(number),
uniqExact(number),
0
FROM numbers(10000000)
GROUP BY another_id
UNION ALL
SELECT
0 AS some_id,
0 AS another_id,
number % 236 AS last_id,
sum(number),
0,
quantileExact(0.5)(number)
FROM numbers(10000000)
GROUP BY last_id
FORMAT `Null`

Query id: 3a3da29f-97e6-430f-84cb-89aecd537886

Ok.

0 rows in set. Elapsed: 1.439 sec. Processed 30.07 million rows, 240.53 MB (20.89 million rows/s., 167.15 MB/s.)

Peak memory usage (for query): 414.09 MiB.
```

**Expected performance**
Similar performance

**Additional context**
May be short_circuit can help here?

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.