planner: large cardinality estimation error for IndexJoin with IndexLookup when join estimation is zero

[qw4990]

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;

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.

[winoros]

The estimation of hash join is correct, but the estimation of index join is wrong.

[seiya-annie]

/found customer