tidb
tidb copied to clipboard
pingcap
optimizer access table unnecessarily
Bug Report
1. Minimal reproduce step (Required)
CREATE TABLE tb (
object_id bigint(20),
a bigint(20) ,
b bigint(20) ,
c bigint(20) ,
PRIMARY KEY (object_id),
KEY ab (a,b)
);
explain analyze select count(1) from (select /*+ force_index(tb, ab) */ 1 from tb where a=1 and b=1 limit 100) a;
2. What did you expect to see? (Required)
The execution plan should use index only without accessing table data.
3. What did you see instead (Required)
explain analyze select count(1) from (select /*+ force_index(tb, ab) */ 1 from tb where a=1 and b=1 limit 100) a;
+----------------------------------+---------+---------+-----------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+---------+---------+-----------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
| StreamAgg_11 | 1.00 | 1 | root | | time:1.49ms, loops:2, RU:0.482109 | funcs:count(1)->Column#6 | 1.49 KB | N/A |
| └─IndexLookUp_17 | 0.10 | 0 | root | | time:1.48ms, loops:1 | limit embedded(offset:0, count:100) | 243 Bytes | N/A |
| ├─Limit_16(Build) | 0.10 | 0 | cop[tikv] | | time:1.34ms, loops:1, cop_task: {num: 1, max: 1.28ms, proc_keys: 0, tot_proc: 21.3µs, tot_wait: 26.4µs, rpc_num: 1, rpc_time: 1.26ms, copr_cache_hit_ratio: 0.00, build_task_duration: 19.6µs, max_distsql_concurrency: 1}, tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 8.75µs, rocksdb: {block: {}}} | offset:0, count:100 | N/A | N/A |
| │ └─IndexRangeScan_14 | 0.10 | 0 | cop[tikv] | table:tb, index:ab(a, b) | tikv_task:{time:0s, loops:1} | range:[1 1,1 1], keep order:false, stats:pseudo | N/A | N/A |
| └─TableRowIDScan_15(Probe) | 0.10 | 0 | cop[tikv] | table:tb | | keep order:false, stats:pseudo | N/A | N/A |
+----------------------------------+---------+---------+-----------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
5 rows in set (0.00 sec)
4. What is your TiDB version? (Required)
select @@version;
+--------------------+
| @@version |
+--------------------+
| 8.0.11-TiDB-v7.5.1 |
+--------------------+
1 row in set (0.00 sec)
{
"type":"DataSource",
"property":"",
"info":"table:tb",
"children":[
],
"id":1,
"cost":0,
"selected":false
},
{
"type":"Aggregation",
"property":"",
"info":"funcs:count(1), firstrow(test.tb.object_id), firstrow(test.tb.a), firstrow(test.tb.b), firstrow(test.tb.c)",
"children":[
1
],
"id":3,
"cost":0,
"selected":false
},
{
"type":"Limit",
"property":"",
"info":"offset:0, count:100",
"children":[
3
],
"id":9,
"cost":0,
"selected":false
},
{
"type":"Aggregation",
"property":"",
"info":"funcs:count(1)",
"children":[
9
],
"id":6,
"cost":0,
"selected":false
},
{
"type":"Projection",
"property":"",
"info":"Column#6",
"children":[
6
],
"id":7,
"cost":0,
"selected":false
}
It seems that the outer-most count(1) cause the inner count(1) to get all fields
"funcs:count(1), firstrow(test.tb.object_id), firstrow(test.tb.a), firstrow(test.tb.b), firstrow(test.tb.c)"
Since the index only contains column tb.a and tb.b, IndexLookup is used.
