rethink the cost of streamagg/hashagg
Open
@fzhedu is already working on this.
Since Jun 22, 2021.
- Dominant language
- C++
- Stars
- 1k
- Forks
- 423
- Avg merge
- 1d 15h
- Merged PRs (30d)
- 24
Description
mysql> desc select sum(r_regionkey) from test.region;
+----------------------------+---------+--------------+---------------+---------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------+---------+--------------+---------------+---------------------------------------------------------------+
| StreamAgg_8 | 1.00 | root | | funcs:sum(Column#7)->Column#4 |
| └─Projection_20 | 5.00 | root | | cast(test.region.r_regionkey, decimal(41,0) BINARY)->Column#7 |
| └─TableReader_19 | 5.00 | root | | data:TableFullScan_18 |
| └─TableFullScan_18 | 5.00 | cop[tiflash] | table:region | keep order:false, stats:pseudo |
+----------------------------+---------+--------------+---------------+---------------------------------------------------------------+
4 rows in set (0.01 sec)
should be
mysql> desc select sum(r_regionkey) from test.region;
+--------------------------------+---------+-------------------+---------------+---------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+---------+-------------------+---------------+---------------------------------------------------------------+
| HashAgg_15 | 1.00 | root | | funcs:sum(Column#6)->Column#4 |
| └─TableReader_17 | 1.00 | root | | data:ExchangeSender_16 |
| └─ExchangeSender_16 | 1.00 | batchCop[tiflash] | | ExchangeType: PassThrough |
| └─HashAgg_7 | 1.00 | batchCop[tiflash] | | funcs:sum(Column#7)->Column#6 |
| └─Projection_20 | 5.00 | batchCop[tiflash] | | cast(test.region.r_regionkey, decimal(41,0) BINARY)->Column#7 |
| └─TableFullScan_14 | 5.00 | batchCop[tiflash] | table:region | keep order:false, stats:pseudo |
+--------------------------------+---------+-------------------+---------------+---------------------------------------------------------------+
6 rows in set (0.00 sec)
a user cases:
mysql> desc SELECT SUM(reply_cnt) AS "sumany(reply_cnt)", SUM(like_cnt) AS "sumany(like_cnt)" FROM ( SELECT a.md5_doc_id, MAX(IFNULL(reply_cnt,1)) AS reply_cnt, MAX(IFNULL(like_cnt,1)) AS like_cnt FROM `document_ndc_all` a JOIN dim_fid_sentid b ON a.md5_doc_id = b.md5_doc_id WHERE pub_date_time BETWEEN '2021-02-25' AND '2021-05-25' AND b.folder_id IN ('108487','100508','100510') GROUP BY a.md5_doc_id) t;
+------------------------------------------------------+----------+-------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------------------------+----------+-------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_16 | 1.00 | root | | funcs:sum(Column#57)->Column#44, funcs:sum(Column#58)->Column#45 |
| └─Projection_72 | 1.00 | root | | cast(Column#42, decimal(32,0) BINARY)->Column#57, cast(Column#43, decimal(32,0) BINARY)->Column#58 |
| └─TableReader_49 | 1.00 | root | | data:ExchangeSender_48 |
| └─ExchangeSender_48 | 1.00 | batchCop[tiflash] | | ExchangeType: PassThrough |
| └─Projection_47 | 1.00 | batchCop[tiflash] | | Column#42, Column#43 |
| └─HashAgg_19 | 1.00 | batchCop[tiflash] | | group by:Column#56, funcs:max(Column#54)->Column#42, funcs:max(Column#55)->Column#43 |
| └─Projection_71 | 0.03 | batchCop[tiflash] | | ifnull(poc.document_ndc_all.reply_cnt, 1)->Column#54, ifnull(poc.document_ndc_all.like_cnt, 1)->Column#55, poc.document_ndc_all.md5_doc_id |
| └─ExchangeReceiver_46 | 0.03 | batchCop[tiflash] | | |
| └─ExchangeSender_45 | 0.03 | batchCop[tiflash] | | ExchangeType: HashPartition, Hash Cols: poc.document_ndc_all.md5_doc_id |
| └─Projection_42 | 0.03 | batchCop[tiflash] | | poc.document_ndc_all.md5_doc_id, poc.document_ndc_all.reply_cnt, poc.document_ndc_all.like_cnt |
| └─HashJoin_44 | 0.03 | batchCop[tiflash] | | inner join, equal:[eq(poc.dim_fid_sentid.md5_doc_id, poc.document_ndc_all.md5_doc_id)] |
| ├─ExchangeReceiver_29(Build) | 0.02 | batchCop[tiflash] | | |
| │ └─ExchangeSender_28 | 0.02 | batchCop[tiflash] | | ExchangeType: Broadcast |
| │ └─Selection_27 | 0.02 | batchCop[tiflash] | | in(poc.dim_fid_sentid.folder_id, 108487, 100508, 100510), not(isnull(poc.dim_fid_sentid.md5_doc_id)) |
| │ └─TableFullScan_26 | 8.00 | batchCop[tiflash] | table:b | keep order:false, stats:pseudo |
| └─Selection_31(Probe) | 250.00 | batchCop[tiflash] | | ge(poc.document_ndc_all.pub_date_time, 2021-02-25 00:00:00.000000), le(poc.document_ndc_all.pub_date_time, 2021-05-25 00:00:00.000000) |
| └─TableFullScan_30 | 10000.00 | batchCop[tiflash] | table:a | keep order:false, stats:pseudo |
+------------------------------------------------------+----------+-------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------+
17 rows in set (0.00 sec)
should be:
after the MPPTiDB mode cost to 0, it change to:
mysql> desc SELECT SUM(reply_cnt) AS "sumany(reply_cnt)", SUM(like_cnt) AS "sumany(like_cnt)" FROM ( SELECT a.md5_doc_id, MAX(IFNULL(reply_cnt,1)) AS reply_cnt, MAX(IFNULL(like_cnt,1)) AS like_cnt FROM `document_ndc_all` a JOIN dim_fid_sentid b ON a.md5_doc_id = b.md5_doc_id WHERE pub_date_time BETWEEN '2021-02-25' AND '2021-05-25' AND b.folder_id IN ('108487','100508','100510') GROUP BY a.md5_doc_id) t;
+--------------------------------------------------------+----------+-------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------------------------+----------+-------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_68 | 1.00 | root | | funcs:sum(Column#52)->Column#44, funcs:sum(Column#53)->Column#45 |
| └─TableReader_70 | 1.00 | root | | data:ExchangeSender_69 |
| └─ExchangeSender_69 | 1.00 | batchCop[tiflash] | | ExchangeType: PassThrough |
| └─HashAgg_15 | 1.00 | batchCop[tiflash] | | funcs:sum(Column#57)->Column#52, funcs:sum(Column#58)->Column#53 |
| └─Projection_72 | 1.00 | batchCop[tiflash] | | cast(Column#42, decimal(32,0) BINARY)->Column#57, cast(Column#43, decimal(32,0) BINARY)->Column#58 |
| └─Projection_63 | 1.00 | batchCop[tiflash] | | Column#42, Column#43 |
| └─HashAgg_61 | 1.00 | batchCop[tiflash] | | group by:Column#56, funcs:max(Column#54)->Column#42, funcs:max(Column#55)->Column#43 |
| └─Projection_71 | 0.03 | batchCop[tiflash] | | ifnull(poc.document_ndc_all.reply_cnt, 1)->Column#54, ifnull(poc.document_ndc_all.like_cnt, 1)->Column#55, poc.document_ndc_all.md5_doc_id |
| └─ExchangeReceiver_46 | 0.03 | batchCop[tiflash] | | |
| └─ExchangeSender_45 | 0.03 | batchCop[tiflash] | | ExchangeType: HashPartition, Hash Cols: poc.document_ndc_all.md5_doc_id |
| └─Projection_42 | 0.03 | batchCop[tiflash] | | poc.document_ndc_all.md5_doc_id, poc.document_ndc_all.reply_cnt, poc.document_ndc_all.like_cnt |
| └─HashJoin_44 | 0.03 | batchCop[tiflash] | | inner join, equal:[eq(poc.dim_fid_sentid.md5_doc_id, poc.document_ndc_all.md5_doc_id)] |
| ├─ExchangeReceiver_29(Build) | 0.02 | batchCop[tiflash] | | |
| │ └─ExchangeSender_28 | 0.02 | batchCop[tiflash] | | ExchangeType: Broadcast |
| │ └─Selection_27 | 0.02 | batchCop[tiflash] | | in(poc.dim_fid_sentid.folder_id, 108487, 100508, 100510), not(isnull(poc.dim_fid_sentid.md5_doc_id)) |
| │ └─TableFullScan_26 | 8.00 | batchCop[tiflash] | table:b | keep order:false, stats:pseudo |
| └─Selection_31(Probe) | 250.00 | batchCop[tiflash] | | ge(poc.document_ndc_all.pub_date_time, 2021-02-25 00:00:00.000000), le(poc.document_ndc_all.pub_date_time, 2021-05-25 00:00:00.000000) |
| └─TableFullScan_30 | 10000.00 | batchCop[tiflash] | table:a | keep order:false, stats:pseudo |
+--------------------------------------------------------+----------+-------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------+
18 rows in set (0.00 sec)
create database poc;
mysql> show create table dim_fid_sentid;
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dim_fid_sentid | CREATE TABLE `dim_fid_sentid` (
`md5_doc_id` varchar(50) DEFAULT NULL,
`folder_id` int(11) DEFAULT NULL,
`sentiment_type_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table document_ndc_all;
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| document_ndc_all | CREATE TABLE `document_ndc_all` (
`customer_id` varchar(50) DEFAULT NULL,
`md5_doc_id` varchar(50) NOT NULL,
`doc_type_id` int(11) DEFAULT NULL,
`media_id` int(11) DEFAULT NULL,
`media_type_id` int(11) DEFAULT NULL,
`region_type_id` int(11) DEFAULT NULL,
`headline` text DEFAULT NULL,
`doc_url` text DEFAULT NULL,
`account_name` text DEFAULT NULL,
`pub_time` datetime DEFAULT NULL,
`pub_date_time` datetime DEFAULT NULL,
`pub_day_date` datetime DEFAULT NULL,
`pub_week_date` datetime DEFAULT NULL,
`pub_month_date` datetime DEFAULT NULL,
`pub_quarter_date` datetime DEFAULT NULL,
`pub_year_date` datetime DEFAULT NULL,
`pub_hour_date` datetime DEFAULT NULL,
`reply_cnt` int(11) DEFAULT NULL COMMENT '评论数:文本,短视频',
`forward_cnt` int(11) DEFAULT NULL COMMENT '转发数:文本',
`like_cnt` int(11) DEFAULT NULL COMMENT '点赞数:文本,短视频',
`view_cnt` int(11) DEFAULT NULL COMMENT '阅读数:WBI',
`gift_cnt` int(11) DEFAULT NULL COMMENT '礼物数:直播',
`user_cnt` int(11) DEFAULT NULL COMMENT '在线观看峰值:直播',
`duration` int(11) DEFAULT NULL COMMENT '播放时长:直播、短视频',
`folder_ids_str` text DEFAULT NULL,
`sentiment_type_ids_str` varchar(500) DEFAULT NULL,
`event_time` datetime DEFAULT CURRENT_TIMESTAMP,
`action` int(11) DEFAULT NULL COMMENT '0:delete, 1:insert, 2:update',
`account_id` varchar(100) DEFAULT NULL,
`link_type_id` int(11) DEFAULT NULL,
`link_status_id` int(11) DEFAULT NULL,
`sina_verified_type_id` int(11) DEFAULT NULL,
`section` text DEFAULT NULL,
`cluster_id` varchar(100) DEFAULT NULL,
`sina_verified_group_id` int(11) DEFAULT NULL,
`cts` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`md5_doc_id`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
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.
Assessment
This issue has not been assessed yet.