ClickHouse / ClickHouse/ClickHouse

Optimizations based on functions preimage (was: Rewrite toDate/toStartOf* comparison to faster alternative)

Open
#15,257 9 comments 0 reactions 0 assignees View on GitHub
comp-query-optimizer external feature
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

**Use case**
When you would like to filter values which belong to specific intervals, the most intuitive variant is to use toStartOf* functions, but it is not the fastest variant.

**Describe the solution you'd like**

```
toDate

SELECT count()
FROM numbers(1000000000)
WHERE toDate(materialize(now())) = '2020-09-24'
1 rows in set. Elapsed: 3.356 sec. Processed 1.00 billion rows, 8.00 GB (298.01 million rows/s., 2.38 GB/s.)

SELECT count()
FROM numbers(1000000000)
WHERE (materialize(now()) >= '2020-09-24 00:00:00') AND (materialize(now()) < '2020-09-25 00:00:00')
1 rows in set. Elapsed: 2.078 sec. Processed 1.00 billion rows, 8.00 GB (481.16 million rows/s., 3.85 GB/s.)

SELECT count()
FROM numbers(1000000000)
WHERE toDate(materialize(now())) >= '2020-09-24'
1 rows in set. Elapsed: 3.287 sec. Processed 1.00 billion rows, 8.00 GB (304.27 million rows/s., 2.43 GB/s.)

SELECT count()
FROM numbers(1000000000)
WHERE materialize(now()) >= '2020-09-24 00:00:00'
1 rows in set. Elapsed: 1.687 sec. Processed 1.00 billion rows, 8.00 GB (592.84 million rows/s., 4.74 GB/s.)

toStartOfHour

SELECT count()
FROM numbers(1000000000)
WHERE toStartOfHour(materialize(now())) >= '2020-09-24 00:00:00'
1 rows in set. Elapsed: 2.131 sec. Processed 1.00 billion rows, 8.00 GB (469.30 million rows/s., 3.75 GB/s.)

SELECT count() FROM numbers(1000000000) WHERE toStartOfHour(materialize(now())) = '2020-09-24 00:00:00';
1 rows in set. Elapsed: 1.534 sec. Processed 1.00 billion rows, 8.00 GB (651.81 million rows/s., 5.21 GB/s.)

SELECT count() FROM numbers(1000000000) WHERE materialize(now()) >= '2020-09-24 00:00:00' AND materialize(now()) < '2020-09-24 01:00:00' ;
1 rows in set. Elapsed: 1.411 sec. Processed 1.00 billion rows, 8.00 GB (708.86 million rows/s., 5.67 GB/s.)

SELECT count() FROM numbers(1000000000) WHERE toStartOfHour(materialize(now())) >= '2020-09-24 00:00:00';
1 rows in set. Elapsed: 2.236 sec. Processed 1.00 billion rows, 8.00 GB (447.25 million rows/s., 3.58 GB/s.)

SELECT count() FROM numbers(1000000000) WHERE materialize(now()) >= '2020-09-24 00:00:00';
1 rows in set. Elapsed: 1.696 sec. Processed 1.00 billion rows, 8.00 GB (589.76 million rows/s., 4.72 GB/s.)

toStartOfYear

SELECT count() FROM numbers(1000000000) WHERE toStartOfYear(materialize(now())) = '2020-01-01';
1 rows in set. Elapsed: 4.185 sec. Processed 1.00 billion rows, 8.00 GB (238.94 million rows/s., 1.91 GB/s.)

SELECT count() FROM numbers(1000000000) WHERE materialize(now()) >= '2020-01-01 00:00:00' AND materialize(now()) < '2021-01-01 00:00:00';
1 rows in set. Elapsed: 2.015 sec. Processed 1.00 billion rows, 8.00 GB (496.19 million rows/s., 3.97 GB/s.)
```

**Describe alternatives you've considered**
Write those kinds of conditions by hand, but people don't like to write a lot of conditions and it is much easier to make a mistake.

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.