Repository navigation
Make the row count estimation for Range able to make use more stats when stats on this index are unavailable #46375
Description
Example
set global tidb_enable_auto_analyze = false;
create table t(a int, b int, c int, d int,index iab(a,b));
-- execute the INSERT 16 times
insert into t values (10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand()),(10,rand()*100,rand()*10,rand());
-- execute the INSERT 3 times
insert into t select * from t;
set @@tidb_opt_prefer_range_scan = 1;
explain analyze select * from t where a = 10 and b < 5 and c < 5;
alter table add index iac(a,c);
explain analyze select * from t where a = 10 and b < 5 and c < 5;Expected
Always choose index ibc because it has much less row count.
Actual
> explain analyze select * from t where a = 10 and b < 5 and c < 5;
+-------------------------------+---------+---------+-----------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------------+---------+---------+-----------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------+---------+------+
| IndexLookUp_12 | 43.20 | 56 | root | | time:32ms, loops:2, RU:12.399413, index_task: {total_time: 24.9ms, fetch_handle: 24.9ms, build: 3.12µs, wait: 14.1µs}, table_task: {total_time: 10.1ms, num: 2, concurrency: 5}, next: {wait_index: 25.5ms, wait_table_lookup_build: 1.88ms, wait_table_lookup_resp: 4.5ms} | | 59.1 KB | N/A |
| ├─IndexRangeScan_9(Build) | 54.00 | 1440 | cop[tikv] | table:t, index:iac(a, c) | time:24.5ms, loops:4, cop_task: {num: 4, max: 9.4ms, min: 1.7ms, avg: 5.98ms, p95: 9.4ms, tot_proc: 22ms, rpc_num: 4, rpc_time: 23.6ms, copr_cache_hit_ratio: 0.00, build_task_duration: 362.8µs, max_distsql_concurrency: 1}, tikv_task:{proc max:9.12ms, min:1.61ms, avg: 5.79ms, p80:9.12ms, p95:9.12ms, iters:0, tasks:4} | range:[10 -inf,10 5), keep order:false, stats:partial[iac:missing] | N/A | N/A |
| └─Selection_11(Probe) | 43.20 | 56 | cop[tikv] | | time:7.2ms, loops:4, cop_task: {num: 2, max: 4.23ms, min: 2.44ms, avg: 3.33ms, p95: 4.23ms, tot_proc: 6ms, rpc_num: 2, rpc_time: 6.53ms, copr_cache_hit_ratio: 0.00, build_task_duration: 188.6µs, max_distsql_concurrency: 1}, tikv_task:{proc max:4.08ms, min:2.26ms, avg: 3.17ms, p80:4.08ms, p95:4.08ms, iters:0, tasks:2} | lt(test.t.b, 5) | N/A | N/A |
| └─TableRowIDScan_10 | 54.00 | 1440 | cop[tikv] | table:t | tikv_task:{proc max:4.08ms, min:2.26ms, avg: 3.17ms, p80:4.08ms, p95:4.08ms, iters:0, tasks:2} | keep order:false, stats:partial[iac:missing] | N/A | N/A |
+-------------------------------+---------+---------+-----------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------+---------+------+
> explain analyze select * from t use index (iab) where a = 10 and b < 5 and c < 5;
+-------------------------------+---------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------+--------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------------+---------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------+--------+------+
| IndexLookUp_8 | 43.20 | 56 | root | | time:7.87ms, loops:2, RU:1.991412, index_task: {total_time: 2.73ms, fetch_handle: 2.71ms, build: 4.85µs, wait: 15.5µs}, table_task: {total_time: 4.38ms, num: 1, concurrency: 5}, next: {wait_index: 3.27ms, wait_table_lookup_build: 857.7µs, wait_table_lookup_resp: 3.54ms} | | 12 KB | N/A |
| ├─IndexRangeScan_5(Build) | 96.00 | 96 | cop[tikv] | table:t, index:iab(a, b) | time:2.61ms, loops:3, cop_task: {num: 1, max: 2.15ms, proc_keys: 0, tot_proc: 1ms, rpc_num: 1, rpc_time: 1.98ms, copr_cache_hit_ratio: 0.00, build_task_duration: 119.5µs, max_distsql_concurrency: 1}, tikv_task:{time:1.81ms, loops:0} | range:[10 -inf,10 5), keep order:false | N/A | N/A |
| └─Selection_7(Probe) | 43.20 | 56 | cop[tikv] | | time:3.41ms, loops:2, cop_task: {num: 1, max: 2.87ms, proc_keys: 0, tot_proc: 2ms, rpc_num: 1, rpc_time: 2.71ms, copr_cache_hit_ratio: 0.00, build_task_duration: 73.3µs, max_distsql_concurrency: 1}, tikv_task:{time:2.53ms, loops:0} | lt(test.t.c, 5) | N/A | N/A |
| └─TableRowIDScan_6 | 96.00 | 96 | cop[tikv] | table:t | tikv_task:{time:2.53ms, loops:0} | keep order:false | N/A | N/A |
+-------------------------------+---------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------+--------+------+
Enhancement
Note the estimation error here. The error caused the wrong index.
This is because the estimated row count for index iac uses pseudo estimation, and this is because the newly added iac doesn't have statistics.
To resolve this, we need to make the estimation for index iac able to use more loaded column stats instead of fallback to pseudo estimation.
Activity
planner: Choose index with statistics vs one without (#58593)
planner: Choose index with statistics vs one without (#58593) (#59222)
planner: Choose index with statistics vs one without (#58593) (#59221)
planner: Choose index with statistics vs one without (#58593) (#59223)