pingcap / pingcap/tidb

IndexLookUp cost mismatches actual execution time compared with TableScan by clustered index

Open
#49,901 9 comments 0 reactions 1 assignee Claimed by @hawkingrei View on GitHub
affects-8.1 epic/cost-model found/gs report/customer sig/planner type/enhancement type/performance type/regression
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

(1) Create a sysbench table like this:
```
CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`, `k`) /*T![clustered_index] CLUSTERED */
);
```
(2) Run insert.sh to insert some rows.
(3) Execute the following sqls:
```
alter table sbtest1 add column id_1 int;
alter table sbtest1 add key(id_1);
update sbtest1 set id_1=id;
analyze table sbtest1;
```
(4) Compare the following plans:
```
explain analyze select * from sbtest1 where id=1 and id_1=1;
explain analyze select * from sbtest1 where id=1 and id_1=0;
explain analyze select * from sbtest1 use index(primary) where id=1 and id_1=1;
explain analyze select * from sbtest1 use index(primary) where id=1 and id_1=0;
```
### 2. What did you expect to see? (Required)
Execution plan uses clustered index.
```
mysql> explain format='verbose' select * from sbtest1 use index(primary) where id=1 and id_1=1;
+--------------------------+---------+---------+-----------+---------------+-------------------------------+
| id | estRows | estCost | task | access object | operator info |
+--------------------------+---------+---------+-----------+---------------+-------------------------------+
| TableReader_7 | 0.00 | 24.95 | root | | data:Selection_6 |
| └─Selection_6 | 0.00 | 374.21 | cop[tikv] | | eq(sbtest.sbtest1.id_1, 1) |
| └─TableRangeScan_5 | 1.01 | 323.71 | cop[tikv] | table:sbtest1 | range:[1,1], keep order:false |
+--------------------------+---------+---------+-----------+---------------+-------------------------------+
3 rows in set (0.00 sec)
mysql> explain analyze select * from sbtest1 use index(primary) where id=1 and id_1=1;
+--------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+-----------+------+
| TableReader_7 | 0.00 | 1 | root | | time:751.6µs, loops:2, RU:0.302888, cop_task: {num: 1, max: 651.8µs, proc_keys: 1, tot_proc: 148.1µs, tot_wait: 65.2µs, rpc_num: 1, rpc_time: 625.7µs, copr_cache_hit_ratio: 0.00, build_task_duration: 11.3µs, max_distsql_concurrency: 1} | data:Selection_6 | 519 Bytes | N/A |
| └─Selection_6 | 0.00 | 1 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 230, total_keys: 3, get_snapshot_time: 27.1µs, rocksdb: {key_skipped_count: 2, block: {cache_hit_count: 6}}} | eq(sbtest.sbtest1.id_1, 1) | N/A | N/A |
| └─TableRangeScan_5 | 1.01 | 1 | cop[tikv] | table:sbtest1 | tikv_task:{time:0s, loops:1} | range:[1,1], keep order:false | N/A | N/A |
+--------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+-----------+------+
3 rows in set (0.00 sec)
mysql> explain analyze select * from sbtest1 use index(primary) where id=1 and id_1=0;
+--------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+-----------+------+
| TableReader_7 | 0.00 | 0 | root | | time:742.4µs, loops:1, RU:0.301466, cop_task: {num: 1, max: 668.4µs, proc_keys: 1, tot_proc: 143.9µs, tot_wait: 68.9µs, rpc_num: 1, rpc_time: 642.6µs, copr_cache_hit_ratio: 0.00, build_task_duration: 9.59µs, max_distsql_concurrency: 1} | data:Selection_6 | 238 Bytes | N/A |
| └─Selection_6 | 0.00 | 0 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 230, total_keys: 3, get_snapshot_time: 27.7µs, rocksdb: {key_skipped_count: 2, block: {cache_hit_count: 6}}} | eq(sbtest.sbtest1.id_1, 0) | N/A | N/A |
| └─TableRangeScan_5 | 1.01 | 1 | cop[tikv] | table:sbtest1 | tikv_task:{time:0s, loops:1} | range:[1,1], keep order:false | N/A | N/A |
+--------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+-----------+------+
3 rows in set (0.00 sec)
```

### 3. What did you see instead (Required)
The plan uses IndexRangeScan and IndexLookUp.
```
mysql> explain format='verbose' select * from sbtest1 where id=1 and id_1=1;
+-------------------------------+---------+---------+-----------+---------------------------------+-------------------------------+
| id | estRows | estCost | task | access object | operator info |
+-------------------------------+---------+---------+-----------+---------------------------------+-------------------------------+
| IndexLookUp_11 | 0.00 | 18.48 | root | | |
| ├─Selection_10(Build) | 0.00 | 277.21 | cop[tikv] | | eq(sbtest.sbtest1.id, 1) |
| │ └─IndexRangeScan_8 | 1.00 | 227.31 | cop[tikv] | table:sbtest1, index:id_1(id_1) | range:[1,1], keep order:false |
| └─TableRowIDScan_9(Probe) | 0.00 | 0.00 | cop[tikv] | table:sbtest1 | keep order:false |
+-------------------------------+---------+---------+-----------+---------------------------------+-------------------------------+
4 rows in set (0.00 sec)
mysql> explain analyze select * from sbtest where id=1 and id_1=1;
+-------------------------------+---------+---------+-----------+--------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------------+---------+---------+-----------+--------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+------+
| IndexLookUp_11 | 0.00 | 1 | root | | time:1.5ms, loops:2, RU:0.657320, index_task: {total_time: 660.2µs, fetch_handle: 656.5µs, build: 882ns, wait: 2.86µs}, table_task: {total_time: 734.5µs, num: 1, concurrency: 5}, next: {wait_index: 741.1µs, wait_table_lookup_build: 59.3µs, wait_table_lookup_resp: 667.5µs} | | 63.0 KB | N/A |
| ├─Selection_10(Build) | 0.00 | 1 | cop[tikv] | | time:646.8µs, loops:3, cop_task: {num: 1, max: 574.6µs, proc_keys: 1, tot_proc: 117.9µs, tot_wait: 63.7µs, rpc_num: 1, rpc_time: 552.9µs, copr_cache_hit_ratio: 0.00, build_task_duration: 20.6µs, max_distsql_concurrency: 1}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 57, total_keys: 2, get_snapshot_time: 25.7µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 7}}} | eq(sbtest.sbtest.id, 1) | N/A | N/A |
| │ └─IndexRangeScan_8 | 1.01 | 1 | cop[tikv] | table:sbtest, index:id_1(id_1) | tikv_task:{time:0s, loops:1} | range:[1,1], keep order:false | N/A | N/A |
| └─TableRowIDScan_9(Probe) | 0.00 | 1 | cop[tikv] | table:sbtest | time:646.1µs, loops:2, cop_task: {num: 1, max: 569.6µs, proc_keys: 1, tot_proc: 172.5µs, tot_wait: 44.2µs, rpc_num: 1, rpc_time: 555.1µs, copr_cache_hit_ratio: 0.00, build_task_duration: 14µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 3910, total_keys: 2, get_snapshot_time: 19.8µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 9}}} | keep order:false | N/A | N/A |
+-------------------------------+---------+---------+-----------+--------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+------+
4 rows in set (0.00 sec)

mysql> explain analyze select * from sbtest where id=1 and id_1=0;
+-------------------------------+---------+---------+-----------+--------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------------+---------+---------+-----------+--------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+-----------+------+
| IndexLookUp_11 | 0.00 | 0 | root | | time:643µs, loops:1, RU:0.286461 | | 236 Bytes | N/A |
| ├─Selection_10(Build) | 0.00 | 0 | cop[tikv] | | time:558.2µs, loops:1, cop_task: {num: 1, max: 506.3µs, proc_keys: 0, tot_proc: 109.4µs, tot_wait: 59.7µs, rpc_num: 1, rpc_time: 491.3µs, copr_cache_hit_ratio: 0.00, build_task_duration: 11.8µs, max_distsql_concurrency: 1}, tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 23.9µs, rocksdb: {block: {cache_hit_count: 7}}} | eq(sbtest.sbtest.id, 1) | N/A | N/A |
| │ └─IndexRangeScan_8 | 1.01 | 0 | cop[tikv] | table:sbtest, index:id_1(id_1) | tikv_task:{time:0s, loops:1} | range:[0,0], keep order:false | N/A | N/A |
| └─TableRowIDScan_9(Probe) | 0.00 | 0 | cop[tikv] | table:sbtest | | keep order:false | N/A | N/A |
+-------------------------------+---------+---------+-----------+--------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+-----------+------+
4 rows in set (0.00 sec)
```

### 4. What is your TiDB version? (Required)

v7.1.2

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.