cockroachdb / cockroachdb/cockroach

opt: add rule to eliminate trivial GroupBy

Open
#114,206 20 comments 0 reactions 1 assignee Claimed by @rickyhuangjh View on GitHub
C-enhancement E-quick-win O-support P-3 T-sql-queries
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

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.