cockroachdb / cockroachdb/cockroach
opt: add rule to eliminate trivial GroupBy
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
We already have normalization rules `EliminateDistinct` and `EliminateUpsertDistinct`, which eliminate a `DistinctOn` variant that is grouping on a key, since in that case it is a no-op. This isn't always possible for `GroupBy`, since it allows many more aggregate functions than `DistinctOn`. However, certain aggregate functions will pass through a single value unchanged (and when grouping on a key, each group consists of a single row). Here are a few examples of aggregate functions with this behavior:
```
root@localhost:26257/system/defaultdb> create table t (k int primary key, v bool);
CREATE TABLE
Time: 23ms total (execution 23ms / network 0ms)
root@localhost:26257/system/defaultdb> insert into t values (1, true), (2, false), (3, null);
INSERT 0 3
root@localhost:26257/system/defaultdb> select k, v, bool_and(v), bool_or(v), max(v), min(v) from t group by k;
k | v | bool_and | bool_or | max | min
----+------+----------+---------+------+-------
1 | t | t | t | t | t
2 | f | f | f | f | f
3 | NULL | NULL | NULL | NULL | NULL
(3 rows)
root@localhost:26257/system/defaultdb> explain (opt) select k, v, bool_and(v), bool_or(v), max(v), min(v) from t group by k;
info
------------------------
group-by (streaming)
├── scan t
└── aggregations
├── bool-and
│ └── v
├── bool-or
│ └── v
├── max
│ └── v
├── min
│ └── v
└── const-agg
└── v
(13 rows)
```
We should add a rule that matches `GroupBy` operators that group on a key, and which detects when the aggregate functions pass through a single-row group. We can likely make a similar optimization for `ScalarGroupBy` when the input has exactly one row, with some subtlety around handling NULL values.
Jira issue: CRDB-33393
Contributor guide
Assessment
This issue has not been assessed yet.