improve `avg` for high cardinality aggregation
Open
Nobody has claimed this yet.
type/enhancement
- Dominant language
- C++
- Stars
- 1k
- Forks
- 423
- Avg merge
- 1d 15h
- Merged PRs (30d)
- 24
Description
Enhancement
Currently, final avg is rewritten to sum_col/(case when count_col = 0 then 1 else count_col), this is complex and will hurt the performance for high cardinality aggregation. It's better to add an internal function like avg_divide, and for avg_divide, always return null if the divisor = 0 is enough.
mysql> explain analyze SELECT l_orderkey, avg(l_quantity) as xx FROM lineitem GROUP BY l_orderkey having xx > 1000;
+--------------------------------------+--------------+-----------+--------------+----------------+----------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------+--------------+-----------+--------------+----------------+----------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------+
| TableReader_60 | 120704204.80 | 0 | root | | time:7.43s, loops:1, cop_task: {num: 4, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_59 | N/A | N/A |
| └─ExchangeSender_59 | 120704204.80 | 0 | mpp[tiflash] | | tiflash_task:{proc max:7.43s, min:6.14s, avg: 6.78s, p80:7.43s, p95:7.43s, iters:0, tasks:4, threads:80} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_7 | 120704204.80 | 0 | mpp[tiflash] | | tiflash_task:{proc max:7.43s, min:6.14s, avg: 6.78s, p80:7.43s, p95:7.43s, iters:0, tasks:4, threads:80} | tpch_100_multi_key.lineitem.l_orderkey, Column#18 | N/A | N/A |
| └─Selection_58 | 120704204.80 | 0 | mpp[tiflash] | | tiflash_task:{proc max:7.43s, min:6.14s, avg: 6.78s, p80:7.43s, p95:7.43s, iters:0, tasks:4, threads:80} | gt(Column#18, 1000) | N/A | N/A |
| └─Projection_51 | 150880256.00 | 150000000 | mpp[tiflash] | | tiflash_task:{proc max:7.36s, min:6.08s, avg: 6.72s, p80:7.36s, p95:7.36s, iters:1024, tasks:4, threads:80} | div(Column#18, cast(case(eq(Column#35, 0), 1, Column#35), decimal(20,0) BINARY))->Column#18, tpch_100_multi_key.lineitem.l_orderkey | N/A | N/A |
| └─HashAgg_49 | 150880256.00 | 150000000 | mpp[tiflash] | | tiflash_task:{proc max:6.09s, min:5.17s, avg: 5.66s, p80:6.09s, p95:6.09s, iters:1024, tasks:4, threads:80} | group by:tpch_100_multi_key.lineitem.l_orderkey, funcs:count(tpch_100_multi_key.lineitem.l_quantity)->Column#35, funcs:sum(tpch_100_multi_key.lineitem.l_quantity)->Column#18, funcs:firstrow(tpch_100_multi_key.lineitem.l_orderkey)->tpch_100_multi_key.lineitem.l_orderkey | N/A | N/A |
| └─ExchangeReceiver_26 | 600037902.00 | 600037902 | mpp[tiflash] | | tiflash_task:{proc max:4.28s, min:1.88s, avg: 3.6s, p80:4.28s, p95:4.28s, iters:40269, tasks:4, threads:80} | | N/A | N/A |
| └─ExchangeSender_25 | 600037902.00 | 600037902 | mpp[tiflash] | | tiflash_task:{proc max:4.85s, min:0s, avg: 1.21s, p80:4.85s, p95:4.85s, iters:10176, tasks:4, threads:61} | ExchangeType: HashPartition, Hash Cols: [name: tpch_100_multi_key.lineitem.l_orderkey, collate: binary] | N/A | N/A |
| └─TableFullScan_24 | 600037902.00 | 600037902 | mpp[tiflash] | table:lineitem | tiflash_task:{proc max:207.9ms, min:0s, avg: 52ms, p80:207.9ms, p95:207.9ms, iters:10176, tasks:4, threads:61} | keep order:false | N/A | N/A |
+--------------------------------------+--------------+-----------+--------------+----------------+----------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------+
9 rows in set (7.44 sec)
mysql> explain analyze SELECT l_orderkey, sum(l_quantity)/count(l_quantity) as xx FROM lineitem GROUP BY l_orderkey having xx > 1000;
+--------------------------------------+--------------+-----------+--------------+----------------+---------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------+--------------+-----------+--------------+----------------+---------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------+
| TableReader_60 | 120704204.80 | 0 | root | | time:5.65s, loops:1, cop_task: {num: 4, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_59 | N/A | N/A |
| └─ExchangeSender_59 | 120704204.80 | 0 | mpp[tiflash] | | tiflash_task:{proc max:5.65s, min:5.11s, avg: 5.39s, p80:5.65s, p95:5.65s, iters:0, tasks:4, threads:80} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_7 | 120704204.80 | 0 | mpp[tiflash] | | tiflash_task:{proc max:5.65s, min:5.11s, avg: 5.39s, p80:5.65s, p95:5.65s, iters:0, tasks:4, threads:80} | tpch_100_multi_key.lineitem.l_orderkey, div(Column#18, cast(Column#19, decimal(20,0) BINARY))->Column#20 | N/A | N/A |
| └─Selection_58 | 120704204.80 | 0 | mpp[tiflash] | | tiflash_task:{proc max:5.65s, min:5.11s, avg: 5.39s, p80:5.65s, p95:5.65s, iters:0, tasks:4, threads:80} | gt(div(Column#18, cast(Column#19, decimal(20,0) BINARY)), 1000) | N/A | N/A |
| └─Projection_51 | 150880256.00 | 150000000 | mpp[tiflash] | | tiflash_task:{proc max:5.27s, min:4.8s, avg: 5.04s, p80:5.27s, p95:5.27s, iters:1024, tasks:4, threads:80} | Column#18, Column#19, tpch_100_multi_key.lineitem.l_orderkey | N/A | N/A |
| └─HashAgg_49 | 150880256.00 | 150000000 | mpp[tiflash] | | tiflash_task:{proc max:5.27s, min:4.8s, avg: 5.04s, p80:5.27s, p95:5.27s, iters:1024, tasks:4, threads:80} | group by:tpch_100_multi_key.lineitem.l_orderkey, funcs:sum(tpch_100_multi_key.lineitem.l_quantity)->Column#18, funcs:count(tpch_100_multi_key.lineitem.l_quantity)->Column#19, funcs:firstrow(tpch_100_multi_key.lineitem.l_orderkey)->tpch_100_multi_key.lineitem.l_orderkey | N/A | N/A |
| └─ExchangeReceiver_26 | 600037902.00 | 600037902 | mpp[tiflash] | | tiflash_task:{proc max:3.14s, min:2.46s, avg: 2.79s, p80:3.14s, p95:3.14s, iters:40251, tasks:4, threads:80} | | N/A | N/A |
| └─ExchangeSender_25 | 600037902.00 | 600037902 | mpp[tiflash] | | tiflash_task:{proc max:3.87s, min:0s, avg: 967.6ms, p80:3.87s, p95:3.87s, iters:10176, tasks:4, threads:61} | ExchangeType: HashPartition, Hash Cols: [name: tpch_100_multi_key.lineitem.l_orderkey, collate: binary] | N/A | N/A |
| └─TableFullScan_24 | 600037902.00 | 600037902 | mpp[tiflash] | table:lineitem | tiflash_task:{proc max:76.5ms, min:0s, avg: 19.1ms, p80:76.5ms, p95:76.5ms, iters:10176, tasks:4, threads:61} | keep order:false | N/A | N/A |
+--------------------------------------+--------------+-----------+--------------+----------------+---------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------+
9 rows in set (5.67 sec)
As the example shows, Projection_51 in the above query includes div(Column#18, cast(case(eq(Column#35, 0), 1, Column#35), decimal(20,0) BINARY)), takes 1.3s, and Selection_58 in the bottom query includes div(Column#18, cast(Column#19, decimal(20,0) BINARY)) only takes 0.4s
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with the two EXPLAIN ANALYZE queries in the issue and trace the AVG rewrite and aggregation-function registration in the C++ engine. Compare the generated plans for AVG and SUM/COUNT, including a zero divisor. Done means AVG uses the internal avg_divide behavior and returns NULL for a zero divisor without the current CASE expression.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- cpp, sql
- Domain
- databases, performance
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 38/100