planner: large cardinality estimation error for IndexJoin with IndexLookup when join estimation closes to zero
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
```
create table t1 (a int);
insert into t1 values (1), (1), (1), (1), (1), (1), (1), (1), (1), (1);
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
create table t2 (a int, b int, key(a));
insert into t2 values (1, 1), (1, 1), (1, 1), (1, 1), (1, 1);
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
analyze table t1, t2;
explain analyze select /*+ tidb_inlj(t2) */ * from t1, t2 where t1.a=t2.a and t2.b=2;
+---------------------------------+----------+---------+-----------+----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------------+----------+---------+-----------+----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------+------+
| Projection_9 | 0.00 | 0 | root | | time:147.1ms, loops:1, RU:321.051601, Concurrency:OFF | test.t1.a, test.t2.a, test.t2.b | 1.11 KB | N/A |
| └─IndexJoin_15 | 0.00 | 0 | root | | time:147.1ms, loops:1, inner:{total:705.1ms, concurrency:5, task:10, construct:20.6ms, fetch:684.5ms, build:916ns}, probe:1.3ms | inner join, inner:IndexLookUp_14, outer key:test.t1.a, inner key:test.t2.a, equal cond:eq(test.t1.a, test.t2.a) | 3.69 MB | N/A |
| ├─TableReader_28(Build) | 40960.00 | 40960 | root | | time:10.6ms, loops:46, cop_task: {num: 9, max: 6.06ms, min: 126.3µs, avg: 2ms, p95: 6.06ms, tot_proc: 15ms, rpc_num: 9, rpc_time: 17.9ms, copr_cache_hit_ratio: 0.00, build_task_duration: 12.9µs, max_distsql_concurrency: 1} | data:Selection_27 | 203.2 KB | N/A |
| │ └─Selection_27 | 40960.00 | 40960 | cop[tikv] | | tikv_task:{proc max:6.02ms, min:111.3µs, avg: 1.95ms, p80:5.85ms, p95:6.02ms, iters:0, tasks:9} | not(isnull(test.t1.a)) | N/A | N/A |
| │ └─TableFullScan_26 | 40960.00 | 40960 | cop[tikv] | table:t1 | tikv_task:{proc max:6.02ms, min:111.3µs, avg: 1.95ms, p80:5.85ms, p95:6.02ms, iters:0, tasks:9} | keep order:false | N/A | N/A |
| └─IndexLookUp_14(Probe) | 0.00 | 0 | root | | time:684.2ms, loops:10, index_task: {total_time: 663.7ms, fetch_handle: 663.2ms, build: 328.5µs, wait: 124.5µs}, table_task: {total_time: 88.5ms, num: 50, concurrency: 5}, next: {wait_index: 607.5ms, wait_table_lookup_build: 18.4ms, wait_table_lookup_resp: 58.1ms} | | 416 KB | N/A |
| ├─Selection_12(Build) | 0.00 | 204800 | cop[tikv] | | time:653.6ms, loops:220, cop_task: {num: 80, max: 18.1ms, min: 1.29ms, avg: 8.27ms, p95: 13ms, tot_proc: 620ms, rpc_num: 80, rpc_time: 660.8ms, copr_cache_hit_ratio: 0.00, build_task_duration: 82.9µs, max_distsql_concurrency: 1}, tikv_task:{proc max:18.1ms, min:1.28ms, avg: 8.24ms, p80:11.3ms, p95:13ms, iters:0, tasks:80} | not(isnull(test.t2.a)) | N/A | N/A |
| │ └─IndexRangeScan_10 | 0.00 | 204800 | cop[tikv] | table:t2, index:a(a) | tikv_task:{proc max:18.1ms, min:1.28ms, avg: 8.24ms, p80:11.3ms, p95:13ms, iters:0, tasks:80} | range: decided by [eq(test.t2.a, test.t1.a)], keep order:false | N/A | N/A |
| └─Selection_13(Probe) | 0.00 | 0 | cop[tikv] | | time:62.3ms, loops:50, cop_task: {num: 50, max: 5.14ms, min: 229.2µs, avg: 1.23ms, p95: 3.66ms, tot_proc: 35ms, rpc_num: 50, rpc_time: 61.1ms, copr_cache_hit_ratio: 0.00, build_task_duration: 1.75ms, max_distsql_concurrency: 1}, tikv_task:{proc max:5.13ms, min:210.2µs, avg: 1.21ms, p80:1.57ms, p95:3.64ms, iters:0, tasks:50} | eq(test.t2.b, 2) | N/A | N/A |
| └─TableRowIDScan_11 | 0.00 | 204800 | cop[tikv] | table:t2 | tikv_task:{proc max:5.13ms, min:210.2µs, avg: 1.21ms, p80:1.57ms, p95:3.64ms, iters:0, tasks:50} | keep order:false | N/A | N/A |
+---------------------------------+----------+---------+-----------+----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------+------+
```
The estimation error of `IndexRangeScan_10` is huge, `0` vs `204800`, which might lead to suboptimal plans.
Contributor guide
Assessment
This issue has not been assessed yet.