ClickHouse / ClickHouse/ClickHouse

min/max/uniq aggregate functions can be calculated from secondary indices of type `minmax` and `set`

Open
#92,758 2 comments 0 reactions 0 assignees View on GitHub
comp-skip-index performance
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

### Company or project name

ClickHouse

### Describe the situation

When there is an aggregation across the whole table involving `min` or `max` aggregate functions, and there is a `minmax` index of a column, we can avoid data reading by calculating the result only on the index:

```sql
CREATE TABLE table
(
c UInt64,
INDEX ix(c) TYPE minmax
);

SELECT min(c), max(c) FROM table;
```

We can do it as well if we read from entire parts:

```sql
CREATE TABLE table
(
c UInt64,
INDEX ix(c) TYPE minmax,
p String,
PARTITION BY p
);

SELECT min(c), max(c) FROM table WHERE p = 'Hello';
```

Or when aggregating by partition key:

```sql
CREATE TABLE table
(
c UInt64,
INDEX ix(c) TYPE minmax,
p String,
PARTITION BY p
);

SELECT p, min(c), max(c) FROM table GROUP BY p;
```

It can be applied to the `set` index as well. However, we can use it only if the set is present in every granule (it didn't overflow). In this case, we can also calculate `uniq`, `uniqExact`, and other `uniq` variants.

The implementation can use "implicit projections".

### Which ClickHouse versions are affected?

.

### How to reproduce

.

### Expected performance

_No response_

### Additional context

_No response_

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.