pingcap / pingcap/tidb

TiDB optimizer didn't choose the best index

Open
#39,410 2 comments 2 reactions 0 assignees View on GitHub
affects-5.0 affects-5.1 affects-5.2 affects-5.3 affects-5.4 affects-6.0 affects-6.1 affects-6.2 affects-6.3 affects-6.4 affects-6.5 may-affects-4.0 sig/planner type/enhancement
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)

CREATE TABLE tt (
org_id_path varchar(256) NOT NULL,
res_id int(4) NOT NULL,
res_name varchar(128) NOT NULL,
status int(4) DEFAULT NULL,
KEY idx_org_status (org_id_path,status),
KEY idx_org_res_id (org_id_path,res_id)
)

sql1 := `explain select org_id_path,res_id,res_name,status from tt where org_id_path like '11%' order by res_id limit 0, 10;`

![image](https://user-images.githubusercontent.com/3000021/204191006-120609cd-7e56-4886-97d6-4a4c18cb0be9.png)

最优索引应该是 idx_org_res_id
sql2 := `explain select org_id_path,res_id,res_name,status from tt **use index (idx_org_res_id)** where org_id_path like '11%' order by res_id limit 0, 10;`
![image](https://user-images.githubusercontent.com/3000021/204191169-31df48a7-fd75-4e3a-a00c-3cf1e182ccdd.png)

原因分析:
baseLogicalPlan.findBestTask() => child.findBestTask()
Datasource 贪心找到的 best plan,但是并不是整体最优的:
第一步:【TableScan,TaskTp: copDoubleReadTask】DataSource.findBestTask() 有两个索引:idx_org_status,idx_org_res_id,他们的代价是一样的,选到了第一个索引 idx_org_status (cost相等时,先被估算的索引胜出)

第二步:【core.PhysicalTopN】baseLogicalPlan.findBestTask() 把子计划与自身结合起来curTask := pp.attach2Task(childTasks...),这时,两个索引 topN是不同的:pushedDownTopN 使用 indexPlan或tablePlan。但是,第一步只返回了一个索引,这里没得选,只能使用 idx_org_status。

如果建表时,把索引 idx_org_status,idx_org_res_id顺序调换一下,则 idx_org_res_id 胜出。

### 2. What did you expect to see? (Required)
总是能使用最优的索引 idx_org_res_id

### 3. What did you see instead (Required)
使用了错误的索引,而且与建表时索引的排列顺序有关

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

所有版本

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.