ClickHouse / ClickHouse/ClickHouse
Optimizations based on functions preimage (was: Rewrite toDate/toStartOf* comparison to faster alternative)
- 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
Assessment
This issue has not been assessed yet.