planner: Unexpected estCost for the build side of an Index Join with different probe sides.
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
1. Unexpected estCost for the build side of an Index Join with different probe sides(TableReader_44(Build) vs TableReader_47(Build)).
2. The cost of index reader should less than table reader. Since there are not double-read and tablerowsize should be always bigger than indexrowsize
### 1. Minimal reproduce step (Required)
```
mysql> create table t1(id int,id1 int,id2 int,id3 int,id4 int);
mysql> create table t2(id int,id1 int,id2 int,id3 int,id4 int,primary key(id) clustered,KEY `idx_id` (`id`,`id1`));
mysql> INSERT INTO t1 (id, id1, id2, id3, id4)
SELECT
n.id,
1,
FLOOR(1 + RAND() * 1000),
FLOOR(1 + RAND() * 10000),
FLOOR(1 + RAND() * 100000)
FROM (
SELECT a.N + b.N * 10 + c.N * 100 + d.N * 1000 + 1 AS id
FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
(SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
(SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c,
(SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d
HAVING id <= 10000
) n;
mysql> insert into t2 select * from t1;
```
### 2. What did you expect to see? (Required)
```
mysql> explain analyze format=verbose select t1.* from t1,t2 use index(idx_id1) where t1.id=t2.id and t1.id4<500 and t2.id1=1;
+------------------------------+----------+------------+---------+-----------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | estCost | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------+----------+------------+---------+-----------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------+---------+------+
| IndexJoin_11 | 50.80 | 452406.61 | 49 | root | | time:1.93ms, open:22.2µs, close:1.54µs, loops:2, RU:15.74, inner:{total:738µs, concurrency:5, task:1, construct:16.8µs, fetch:711µs, build:10µs}, probe:8.5µs | inner join, inner:IndexReader_10, outer key:tttttt.t1.id, inner key:tttttt.t2.id, equal cond:eq(tttttt.t1.id, tttttt.t2.id) | 58.2 KB | N/A |
| ├─TableReader_44(Build) | 50.80 | 424950.12 | 49 | root | | time:1.13ms, open:21.8µs, close:958ns, loops:3, cop_task: {num: 10, max: 1.05ms, min: 704.3µs, avg: 831.4µs, p95: 1.05ms, max_proc_keys: 1001, p95_proc_keys: 1001, tot_proc: 4.38ms, tot_wait: 1ms, copr_cache_hit_ratio: 0.00, build_task_duration: 11µs, max_distsql_concurrency: 10}, rpc_info:{Cop:{num_rpc:10, total_time:8.21ms}} | data:Selection_43 | 1.10 KB | N/A |
| │ └─Selection_43 | 50.80 | 6358159.48 | 49 | cop[tikv] | | tikv_task:{proc max:1ms, min:0s, avg: 800µs, p80:1ms, p95:1ms, iters:60, tasks:10}, scan_detail: {total_process_keys: 10000, total_process_keys_size: 581986, total_keys: 10010, get_snapshot_time: 408.5µs, rocksdb: {key_skipped_count: 10000, block: {cache_hit_count: 76}}}, time_detail: {total_process_time: 4.38ms, total_wait_time: 1ms, total_kv_read_wall_time: 7ms, tikv_wall_time: 6.36ms} | lt(tttttt.t1.id4, 500), not(isnull(tttttt.t1.id)) | N/A | N/A |
| │ └─TableFullScan_42 | 10000.00 | 5360159.48 | 10000 | cop[tikv] | table:t1 | tikv_task:{proc max:1ms, min:0s, avg: 700µs, p80:1ms, p95:1ms, iters:60, tasks:10} | keep order:false | N/A | N/A |
| └─IndexReader_10(Probe) | 50.80 | 23.60 | 49 | root | | time:655.4µs, open:0s, close:4.63µs, loops:2, cop_task: {num: 1, max: 637.9µs, proc_keys: 49, tot_proc: 412.1µs, tot_wait: 47µs, copr_cache_hit_ratio: 0.00, build_task_duration: 7.63µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:635.2µs}} | index:IndexRangeScan_9 | 1.17 KB | N/A |
| └─IndexRangeScan_9 | 50.80 | 227.31 | 49 | cop[tikv] | table:t2, index:idx_id1(id1, id) | tikv_task:{time:1ms, loops:2}, scan_detail: {total_process_keys: 49, total_process_keys_size: 2695, total_keys: 98, get_snapshot_time: 37.6µs, rocksdb: {delete_skipped_count: 22, key_skipped_count: 71, block: {cache_hit_count: 245}}}, time_detail: {total_process_time: 412.1µs, total_wait_time: 47µs, total_kv_read_wall_time: 1ms, tikv_wall_time: 502.6µs} | range: decided by [eq(tttttt.t2.id, tttttt.t1.id) eq(tttttt.t2.id1, 1)], keep order:false | N/A | N/A |
+------------------------------+----------+------------+---------+-----------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------+---------+------+
6 rows in set (0.00 sec)
```
### 3. What did you see instead (Required)
```
mysql> explain analyze format=verbose select t1.* from t1,t2 where t1.id=t2.id and t1.id4<500 and t2.id1=1;
+------------------------------+----------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | estCost | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------+----------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------+---------+------+
| IndexJoin_15 | 50.80 | 273699.39 | 49 | root | | time:2.24ms, open:26.4µs, close:1.13µs, loops:2, RU:20.31, inner:{total:1.04ms, concurrency:5, task:1, construct:24.6µs, fetch:1.01ms, build:7.17µs}, probe:6.84µs | inner join, inner:TableReader_11, outer key:tttttt.t1.id, inner key:tttttt.t2.id, equal cond:eq(tttttt.t1.id, tttttt.t2.id) | 47.1 KB | N/A |
| ├─TableReader_47(Build) | 50.80 | 246278.14 | 49 | root | | time:1.14ms, open:25.9µs, close:500ns, loops:3, cop_task: {num: 10, max: 1.06ms, min: 690.2µs, avg: 834.8µs, p95: 1.06ms, max_proc_keys: 1001, p95_proc_keys: 1001, tot_proc: 4.62ms, tot_wait: 1.04ms, copr_cache_hit_ratio: 0.00, build_task_duration: 9.92µs, max_distsql_concurrency: 10}, rpc_info:{Cop:{num_rpc:10, total_time:8.28ms}} | data:Selection_46 | 1.15 KB | N/A |
| │ └─Selection_46 | 50.80 | 3678079.74 | 49 | cop[tikv] | | tikv_task:{proc max:1ms, min:0s, avg: 600µs, p80:1ms, p95:1ms, iters:60, tasks:10}, scan_detail: {total_process_keys: 10000, total_process_keys_size: 581986, total_keys: 10010, get_snapshot_time: 948.3µs, rocksdb: {key_skipped_count: 10000, block: {cache_hit_count: 76}}}, time_detail: {total_process_time: 4.62ms, total_wait_time: 1.04ms, total_kv_read_wall_time: 5ms, tikv_wall_time: 6.52ms} | lt(tttttt.t1.id4, 500), not(isnull(tttttt.t1.id)) | N/A | N/A |
| │ └─TableFullScan_45 | 10000.00 | 2680079.74 | 10000 | cop[tikv] | table:t1 | tikv_task:{proc max:1ms, min:0s, avg: 500µs, p80:1ms, p95:1ms, iters:60, tasks:10} | keep order:false | N/A | N/A |
| └─TableReader_11(Probe) | 50.80 | 21.99 | 49 | root | | time:930.8µs, open:0s, close:3.5µs, loops:2, cop_task: {num: 10, max: 453µs, min: 190.3µs, avg: 306.4µs, p95: 453µs, max_proc_keys: 8, p95_proc_keys: 8, tot_proc: 1.05ms, tot_wait: 261µs, copr_cache_hit_ratio: 0.00, build_task_duration: 25.7µs, max_distsql_concurrency: 1, max_extra_concurrency: 4}, rpc_info:{Cop:{num_rpc:10, total_time:3.04ms}} | data:Selection_10 | N/A | N/A |
| └─Selection_10 | 50.80 | 266.50 | 49 | cop[tikv] | | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:10, tasks:10}, scan_detail: {total_process_keys: 49, total_process_keys_size: 2528, total_keys: 49, get_snapshot_time: 166.4µs, rocksdb: {block: {cache_hit_count: 99}}}, time_detail: {total_process_time: 1.05ms, total_wait_time: 261µs, tikv_wall_time: 1.72ms} | eq(tttttt.t2.id1, 1) | N/A | N/A |
| └─TableRangeScan_9 | 50.80 | 216.60 | 49 | cop[tikv] | table:t2 | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:10, tasks:10} | range: decided by [tttttt.t1.id], keep order:false | N/A | N/A |
+------------------------------+----------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------+---------+------+
7 rows in set, 2 warnings (0.00 sec)
```
### 4. What is your TiDB version? (Required)
TiDB-v9.0.0-beta.1.pre-432-gc508e4b8ac
Contributor guide
Assessment
This issue has not been assessed yet.