There are some redundant computation for agg query containing both `sum(col)` and `avg(col)`
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
```
mysql> show create table window_test; [6/220]
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------+
| Table | Create Table
|
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------+
| window_test | CREATE TABLE `window_test` (
`year` int(11) DEFAULT NULL,
`country` varchar(20) DEFAULT NULL,
`product` varchar(20) DEFAULT NULL,
`profit` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table window_test set tiflash replica 1;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select sum(profit), avg(profit) from window_test;
+------------------------------+---------+-------------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info
|
+------------------------------+---------+-------------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_25 | 1.00 | root | | funcs:sum(Column#14)->Column#6, funcs:avg(Column#15, Column#16)->Column#7 |
| └─TableReader_26 | 1.00 | root | | data:StreamAgg_9 |
| └─StreamAgg_9 | 1.00 | batchCop[tiflash] | | funcs:sum(Column#17)->Column#14, funcs:count(Column#18)->Column#15, funcs:sum(Column#19)->Column#16 |
| └─Projection_31 | 3.00 | batchCop[tiflash] | | cast(test.window_test.profit, decimal(10,0) BINARY)->Column#17, test.window_test.profit, cast(test.window_test.profit, decimal(14,4) BINARY)->Column#19 |
| └─TableFullScan_24 | 3.00 | batchCop[tiflash] | table:window_test | keep order:false, stats:pseudo |
+------------------------------+---------+-------------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql> explain select sum(profit), sum(profit)/count(profit) from window_test;
+--------------------------------+---------+-------------------+-------------------+-----------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+---------+-------------------+-------------------+-----------------------------------------------------------------------------------------+
| Projection_4 | 1.00 | root | | Column#6, div(Column#6, cast(Column#7, decimal(20,0) BINARY))->Column#8 |
| └─StreamAgg_25 | 1.00 | root | | funcs:sum(Column#14)->Column#6, funcs:count(Column#15)->Column#7 |
| └─TableReader_26 | 1.00 | root | | data:StreamAgg_9 |
| └─StreamAgg_9 | 1.00 | batchCop[tiflash] | | funcs:sum(Column#16)->Column#14, funcs:count(Column#17)->Column#15 |
| └─Projection_31 | 3.00 | batchCop[tiflash] | | cast(test.window_test.profit, decimal(10,0) BINARY)->Column#16, test.window_test.profit |
| └─TableFullScan_24 | 3.00 | batchCop[tiflash] | table:window_test | keep order:false, stats:pseudo |
+--------------------------------+---------+-------------------+-------------------+-----------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
```
As we see the example above,
`select sum(profit), sum(profit)/count(profit) from window_test; `
is equivalent to
`select sum(profit), avg(profit) from window_test;`
However, for the first sql, TiFlash will do some extra computation like `cast(test.window_test.profit, decimal(14,4) BINARY)->Column#19` and `funcs:sum(Column#19)->Column#16 `, better to avoid there meaningless redundant computation
Contributor guide
Assessment
This issue has not been assessed yet.