SQL Unable to Use the STREAM_AGG() Hint Feature

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: sql 无法使用hint 的STREAM_AGG() 特性

| username: TiDBer_yyy

[TiDB Usage Environment] Production Environment
[TiDB Version] 5.0.4
Table Index Structure:

Index used
Data volume: 200 million rows
Execution Plan:

MySQL [rd_yrisk_control_engine]> EXPLAIN ANALYZE  /*+ STREAM_AGG(),AGG_TO_COP() */ select CH_DEALER_ID,CH_USER_ID_CARD_ENCRYPT,max(I_AMOUNT) group_month from risk_payee_month where  I_GROUP_MONTH>= '201909' AND I_GROUP_MONTH < '201910'  group by CH_DEALER_ID,CH_USER_ID_CARD_ENCRYPT ;
+------------------------------------+------------+---------+-----------+-----------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id                                 | estRows    | actRows | task      | access object                                                               | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                       | memory  | disk |
+------------------------------------+------------+---------+-----------+-----------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_5                       | 1554397.13 | 5886179 | root      |                                                                             | time:13.7s, loops:5754, Concurrency:8                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | rd_yrisk_control_engine.risk_payee_month.ch_dealer_id, rd_yrisk_control_engine.risk_payee_month.ch_user_id_card_encrypt, Column#52                                                                                                                                                                                                                                                                                                                  | 1.45 MB | N/A  |
| └─HashAgg_22                       | 1554397.13 | 5886179 | root      |                                                                             | time:13.7s, loops:5754, partial_worker:{wall_time:8.8730768s, concurrency:8, task_num:5749, tot_wait:54.618898289s, tot_exec:13.841985702s, tot_time:1m10.802779125s, max:8.873047233s, p95:8.873047233s}, final_worker:{wall_time:13.731023546s, concurrency:8, task_num:64, tot_wait:1m10.65196393s, tot_exec:37.365916086s, tot_time:1m48.017917807s, max:13.730988384s, p95:13.730988384s}                                                                                                                                                 | group by:rd_yrisk_control_engine.risk_payee_month.ch_dealer_id, rd_yrisk_control_engine.risk_payee_month.ch_user_id_card_encrypt, funcs:max(Column#57)->Column#52, funcs:firstrow(rd_yrisk_control_engine.risk_payee_month.ch_dealer_id)->rd_yrisk_control_engine.risk_payee_month.ch_dealer_id, funcs:firstrow(rd_yrisk_control_engine.risk_payee_month.ch_user_id_card_encrypt)->rd_yrisk_control_engine.risk_payee_month.ch_user_id_card_encrypt | 3.32 GB | N/A  |
|   └─IndexLookUp_23                 | 1554397.13 | 5886179 | root      |                                                                             | time:7.37s, loops:5750, index_task: {total_time: 8.05s, fetch_handle: 964.5ms, build: 355.5µs, wait: 7.09s}, table_task: {total_time: 1m7s, num: 149, concurrency: 8}                                                                                                                                                                                                                                                                                                                                                                          |                                                                                                                                                                                                                                                                                                                                                                                                                                                     | 92.6 MB | N/A  |
|     ├─IndexRangeScan_20(Build)     | 7233512.74 | 5886179 | cop[tikv] | table:risk_payee_month, index:idx_group_dealer(I_GROUP_MONTH, CH_DEALER_ID) | time:258.5ms, loops:5755, cop_task: {num: 7, max: 597.5ms, min: 60.1ms, avg: 384.2ms, p95: 597.5ms, max_proc_keys: 1233023, p95_proc_keys: 1233023, tot_proc: 2.55s, rpc_num: 7, rpc_time: 2.69s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:547ms, min:56ms, p80:403ms, p95:547ms, iters:5780, tasks:7}, scan_detail: {total_process_keys: 5886179, total_keys: 5886186, rocksdb: {delete_skipped_count: 0, key_skipped_count: 5886179, block: {cache_hit_count: 4226, read_count: 7, read_byte: 303.6 KB}}}                            | range:[201909,201909], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                             | N/A     | N/A  |
|     └─HashAgg_7(Probe)             | 1554397.13 | 5886179 | cop[tikv] |                                                                             | time:1m1.7s, loops:1764, cop_task: {num: 3042, max: 952.1ms, min: 437.7µs, avg: 47.6ms, p95: 275.2ms, max_proc_keys: 40960, p95_proc_keys: 11951, tot_proc: 2m6.6s, tot_wait: 7.78s, rpc_num: 3042, rpc_time: 2m24.6s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:871ms, min:0s, p80:29ms, p95:257ms, iters:7745, tasks:3042}, scan_detail: {total_process_keys: 5886179, total_keys: 6568056, rocksdb: {delete_skipped_count: 0, key_skipped_count: 2973557, block: {cache_hit_count: 70241163, read_count: 229, read_byte: 1.59 MB}}}  | group by:rd_yrisk_control_engine.risk_payee_month.ch_dealer_id, rd_yrisk_control_engine.risk_payee_month.ch_user_id_card_encrypt, funcs:max(rd_yrisk_control_engine.risk_payee_month.i_amount)->Column#57                                                                                                                                                                                                                                           | N/A     | N/A  |
|       └─TableRowIDScan_21          | 7233512.74 | 5886179 | cop[tikv] | table:risk_payee_month                                                      | tikv_task:{proc max:822ms, min:0s, p80:28ms, p95:246ms, iters:7745, tasks:3042}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}}                                                                                                                                                                                                                                                                                 | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                    | N/A     | N/A  |
+------------------------------------+------------+---------+-----------+-----------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
6 rows in set (13.73 sec)

MySQL [rd_yrisk_control_engine]> EXPLAIN ANALYZE  /*+ STREAM_AGG() */ select CH_DEALER_ID,CH_USER_ID_CARD_ENCRYPT,max(I_AMOUNT) group_month from risk_payee_month where  I_GROUP_MONTH>= '201909' AND I_GROUP_MONTH < '201910'  group by CH_DEALER_ID,CH_USER_ID_CARD_ENCRYPT ;
+------------------------------------+------------+---------+-----------+-----------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id                                 | estRows    | actRows | task      | access object                                                               | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                       | memory  | disk |
+------------------------------------+------------+---------+-----------+-----------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_5                       | 1554397.13 | 5886179 | root      |                                                                             | time:12.8s, loops:5754, Concurrency:8                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | db.table1.ch_dealer_id, db.table1.ch_user_id_card_encrypt, Column#52                                                                                                                                                                                                                                                                                                                  | 1.46 MB | N/A  |
| └─HashAgg_22                       | 1554397.13 | 5886179 | root      |                                                                             | time:12.8s, loops:5754, partial_worker:{wall_time:8.273685576s, concurrency:8, task_num:5749, tot_wait:52.11571047s, tot_exec:11.462284482s, tot_time:1m5.98873633s, max:8.273609274s, p95:8.273609274s}, final_worker:{wall_time:12.847093339s, concurrency:8, task_num:64, tot_wait:1m5.812118454s, tot_exec:36.527406153s, tot_time:1m42.339561045s, max:12.847032589s, p95:12.847032589s}                                                                                                                                               | group by:db.table1.ch_dealer_id, db.table1.ch_user_id_card_encrypt, funcs:max(Column#57)->Column#52, funcs:firstrow(db.table1.ch_dealer_id)->db.table1.ch_dealer_id, funcs:firstrow(db.table1.ch_user_id_card_encrypt)->db.table1.ch_user_id_card_encrypt | 3.32 GB | N/A  |
|   └─IndexLookUp_23                 | 1554397.13 | 5886179 | root      |                                                                             | time:7.01s, loops:5750, index_task: {total_time: 7.49s, fetch_handle: 374.5ms, build: 371.1µs, wait: 7.11s}, table_task: {total_time: 1m2.4s, num: 149, concurrency: 8}                                                                                                                                                                                                                                                                                                                                                                     |                                                                                                                                                                                                                                                                                                                                                                                                                                                     | 88.6 MB | N/A  |
|     ├─IndexRangeScan_20(Build)     | 7233512.74 | 5886179 | cop[tikv] | table:risk_payee_month, index:idx_group_dealer(I_GROUP_MONTH, CH_DEALER_ID) | time:13.8ms, loops:5754, cop_task: {num: 7, max: 520.6ms, min: 708.1µs, avg: 305.7ms, p95: 520.6ms, max_proc_keys: 1233023, p95_proc_keys: 1233023, tot_proc: 2.04s, tot_wait: 6ms, rpc_num: 7, rpc_time: 2.14s, copr_cache_hit_ratio: 0.14}, tikv_task:{proc max:469ms, min:54ms, p80:412ms, p95:469ms, iters:5780, tasks:7}, scan_detail: {total_process_keys: 4979640, total_keys: 4979646, rocksdb: {delete_skipped_count: 0, key_skipped_count: 4979640, block: {cache_hit_count: 3555, read_count: 2, read_byte: 49.0 KB}}}           | range:[201909,201909], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                             | N/A     | N/A  |
|     └─HashAgg_7(Probe)             | 1554397.13 | 5886179 | cop[tikv] |                                                                             | time:59.1s, loops:1745, cop_task: {num: 3081, max: 911.5ms, min: 422.1µs, avg: 43.5ms, p95: 250.3ms, max_proc_keys: 40960, p95_proc_keys: 11764, tot_proc: 2m1s, tot_wait: 2.19s, rpc_num: 3081, rpc_time: 2m13.9s, copr_cache_hit_ratio: 0.01}, tikv_task:{proc max:872ms, min:0s, p80:28ms, p95:237ms, iters:7780, tasks:3081}, scan_detail: {total_process_keys: 5880081, total_keys: 6562066, rocksdb: {delete_skipped_count: 0, key_skipped_count: 2973785, block: {cache_hit_count: 70093428, read_count: 124, read_byte: 2.94 MB}}}  | group by:db.table1.ch_dealer_id, db.table1.CH_DEALER_ID , funcs:max(db.table1.i_amount)->Column#57                                                                                                                                                                                                                                           | N/A     | N/A  |
|       └─TableRowIDScan_21          | 7233512.74 | 5886179 | cop[tikv] | table:risk_payee_month                                                      | tikv_task:{proc max:824ms, min:0s, p80:27ms, p95:225ms, iters:7780, tasks:3081}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}}                                                                                                                                                                                                                                                                              | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                    | N/A     | N/A  |
+------------------------------------+------------+---------+-----------+-----------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
6 rows in set (12.85 sec)
| username: db_user | Original post link

How about writing it like this:
EXPLAIN ANALYZE select /*+ STREAM_AGG() */ CH_DEALER_ID, CH_USER_ID_CARD_ENCRYPT, max(I_AMOUNT) group_month from risk_payee_month where I_GROUP_MONTH >= ‘201909’ AND I_GROUP_MONTH < ‘201910’ group by CH_DEALER_ID, CH_USER_ID_CARD_ENCRYPT;

| username: TiDBer_yyy | Original post link

My mistake, the hint was placed incorrectly.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.