try to optimize index hash join query by reduce cop task count #54840
Description
Enhancement
Related issue: #54160
1. Minimal reproduce step
drop table if exists t0,t1;
create table t1 (id int key auto_increment, b int, index idx1 (b));
insert into t1 () values (), (), (), (), (), (), (), ();
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1 limit 34464;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1 limit 200000;
update t1 set b = rand() * 100000;
create table t0 (id int key, b int, index idx1 (b));
insert into t0 select * from t1 order by id limit 100000;
update t0 set b = rand() * 100000;
split table t1 index idx1 between (0) and (100000) regions 10;
analyze table t0,t1;
set @@tidb_store_batch_size=0;
explain analyze select /*+ INL_HASH_JOIN(t0,t1) */ t0.* from t0 join t1 where t0.id<=50000 and t0.b=t1.b;> explain analyze select /*+ INL_HASH_JOIN(t0,t1) */ t0.* from t0 join t1 where t0.id<=50000 and t0.b=t1.b;
+-------------------------+-----------+---------+-----------+-------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------+-----------+---------+-----------+-------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+----------+------+
| IndexHashJoin_14 | 502491.21 | 501776 | root | | time:176.8ms, loops:492, RU:764.099551, inner:{total:501.3ms, concurrency:5, task:10, construct:24.2ms, fetch:395.4ms, build:6.44ms, join:81.7ms} | inner join, inner:IndexReader_11, outer key:test2.t0.b, inner key:test2.t1.b, equal cond:eq(test2.t0.b, test2.t1.b) | 10.5 MB | N/A |
| ├─TableReader_33(Build) | 49927.53 | 50000 | root | | time:9.16ms, loops:57, cop_task: {num: 9, max: 1.86ms, min: 565.5µs, avg: 987µs, p95: 1.86ms, max_proc_keys: 2016, p95_proc_keys: 2016, tot_proc: 2.09ms, tot_wait: 2.11ms, copr_cache_hit_ratio: 0.56, build_task_duration: 9.04µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:9, total_time:8.83ms}} | data:Selection_32 | 453.9 KB | N/A |
| │ └─Selection_32 | 49927.53 | 50000 | cop[tikv] | | tikv_task:{proc max:22ms, min:0s, avg: 5.89ms, p80:12ms, p95:22ms, iters:84, tasks:9}, scan_detail: {total_process_keys: 3712, total_process_keys_size: 146085, total_keys: 7428, get_snapshot_time: 101µs, rocksdb: {key_skipped_count: 7424, block: {cache_hit_count: 19}}}, time_detail: {total_process_time: 2.09ms, total_wait_time: 2.11ms, total_kv_read_wall_time: 2ms, tikv_wall_time: 5.75ms} | not(isnull(test2.t0.b)) | N/A | N/A |
| │ └─TableRangeScan_31 | 49927.53 | 50000 | cop[tikv] | table:t0 | tikv_task:{proc max:22ms, min:0s, avg: 5.78ms, p80:12ms, p95:22ms, iters:84, tasks:9} | range:[-inf,50000], keep order:false | N/A | N/A |
| └─IndexReader_11(Probe) | 502491.21 | 474600 | root | | time:365.2ms, loops:498, cop_task: {num: 413, max: 35.3ms, min: 284.6µs, avg: 7.41ms, p95: 26.4ms, max_proc_keys: 5088, p95_proc_keys: 4055, tot_proc: 971.1ms, tot_wait: 520.5ms, copr_cache_hit_ratio: 0.35, build_task_duration: 2.94ms, max_distsql_concurrency: 11}, rpc_info:{Cop:{num_rpc:413, total_time:3.06s}} | index:Selection_10 | 2.32 KB | N/A |
| └─Selection_10 | 502491.21 | 474600 | cop[tikv] | | tikv_task:{proc max:33ms, min:0s, avg: 7.65ms, p80:15ms, p95:25ms, iters:1951, tasks:413}, scan_detail: {total_process_keys: 337691, total_process_keys_size: 15533786, total_keys: 371540, get_snapshot_time: 5.6ms, rocksdb: {delete_skipped_count: 137736, key_skipped_count: 475427, block: {cache_hit_count: 204392}}}, time_detail: {total_process_time: 971.1ms, total_suspend_time: 1.15s, total_wait_time: 520.5ms, total_kv_read_wall_time: 2.12s, tikv_wall_time: 2.78s} | not(isnull(test2.t1.b)) | N/A | N/A |
| └─IndexRangeScan_9 | 502491.21 | 474600 | cop[tikv] | table:t1, index:idx1(b) | tikv_task:{proc max:33ms, min:0s, avg: 7.63ms, p80:15ms, p95:25ms, iters:1951, tasks:413} | range: decided by [eq(test2.t1.b, test2.t0.b)], keep order:false | N/A | N/A |
+-------------------------+-----------+---------+-----------+-------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+----------+------+Pay attention to the execution information of IndexReader_11 executor, it send too many cop task, since the total index region of table t1's index idx1 is 10.
└─IndexReader_11 ... time:365.2ms, loops:498, cop_task: {num: 413, ...And I try to increase tidb_max_chunk_size, but this has no affected, this is unexpected, it should work and reduce the number of cop task.
> set @@tidb_max_chunk_size=25000;
> explain analyze select /*+ INL_HASH_JOIN(t0,t1) */ t0.* from t0 join t1 where t0.id<=50000 and t0.b=t1.b;
+-------------------------+-----------+---------+-----------+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------+-----------+---------+-----------+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+----------+------+
| IndexHashJoin_14 | 502491.21 | 501776 | root | | time:202.3ms, loops:23, RU:941.067610, inner:{total:675.2ms, concurrency:5, task:10, construct:21.4ms, fetch:574.3ms, build:4.79ms, join:79.5ms} | inner join, inner:IndexReader_11, outer key:test2.t0.b, inner key:test2.t1.b, equal cond:eq(test2.t0.b, test2.t1.b) | 11.5 MB | N/A |
| ├─TableReader_33(Build) | 49927.53 | 50000 | root | | time:12.7ms, loops:16, cop_task: {num: 9, max: 3.36ms, min: 374µs, avg: 1.53ms, p95: 3.36ms, max_proc_keys: 2016, p95_proc_keys: 2016, tot_proc: 2.52ms, tot_wait: 7.04ms, copr_cache_hit_ratio: 0.56, build_task_duration: 6.88µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:9, total_time:13.7ms}} | data:Selection_32 | 453.9 KB | N/A |
| │ └─Selection_32 | 49927.53 | 50000 | cop[tikv] | | tikv_task:{proc max:22ms, min:0s, avg: 6.11ms, p80:12ms, p95:22ms, iters:84, tasks:9}, scan_detail: {total_process_keys: 3712, total_process_keys_size: 146085, total_keys: 7428, get_snapshot_time: 160.8µs, rocksdb: {key_skipped_count: 7424, block: {cache_hit_count: 19}}}, time_detail: {total_process_time: 2.52ms, total_suspend_time: 1.12ms, total_wait_time: 7.04ms, total_kv_read_wall_time: 3ms, tikv_wall_time: 11.7ms} | not(isnull(test2.t0.b)) | N/A | N/A |
| │ └─TableRangeScan_31 | 49927.53 | 50000 | cop[tikv] | table:t0 | tikv_task:{proc max:22ms, min:0s, avg: 5.89ms, p80:12ms, p95:22ms, iters:84, tasks:9} | range:[-inf,50000], keep order:false | N/A | N/A |
| └─IndexReader_11(Probe) | 502491.21 | 472170 | root | | time:552.2ms, loops:35, cop_task: {num: 423, max: 39.5ms, min: 354.7µs, avg: 12.2ms, p95: 34.6ms, max_proc_keys: 5088, p95_proc_keys: 3317, tot_proc: 1.25s, tot_wait: 1.04s, copr_cache_hit_ratio: 0.11, build_task_duration: 1.85ms, max_distsql_concurrency: 11}, rpc_info:{Cop:{num_rpc:423, total_time:5.17s}} | index:Selection_10 | 2.81 KB | N/A |
| └─Selection_10 | 502491.21 | 472170 | cop[tikv] | | tikv_task:{proc max:36ms, min:0s, avg: 9.56ms, p80:20ms, p95:32ms, iters:1963, tasks:423}, scan_detail: {total_process_keys: 449086, total_process_keys_size: 20657956, total_keys: 494130, get_snapshot_time: 12ms, rocksdb: {delete_skipped_count: 176484, key_skipped_count: 625570, block: {cache_hit_count: 271946}}}, time_detail: {total_process_time: 1.25s, total_suspend_time: 2.59s, total_wait_time: 1.04s, total_kv_read_wall_time: 3.84s, tikv_wall_time: 4.99s} | not(isnull(test2.t1.b)) | N/A | N/A |
| └─IndexRangeScan_9 | 502491.21 | 472170 | cop[tikv] | table:t1, index:idx1(b) | tikv_task:{proc max:36ms, min:0s, avg: 9.53ms, p80:20ms, p95:32ms, iters:1963, tasks:423} | range: decided by [eq(test2.t1.b, test2.t0.b)], keep order:false | N/A | N/A |
+-------------------------+-----------+---------+-----------+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+----------+------+