Is there an issue with the optimizer in TiDB v6.5.3?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb v6.5.3 的优化器是不是有问题

| username: Vincent_Wang

[TiDB Usage Environment] Production Environment

[TiDB Version]
v6.5.3

[Reproduction Path] Operations performed that led to the issue
After upgrading to 6.5.3, SQL became slower. The health of the tables is above 95, but 6.5.3 chooses the wrong index.

[Encountered Issue: Symptoms and Impact]
The health of the tables is above 95, but 6.5.3 chooses the wrong index, causing the business to slow down.

[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]
Table structure

Execution plan of 6.5.3

Execution plan of 5.4.3

| username: 像风一样的男子 | Original post link

The statistics might be inaccurate. Try running an ANALYZE TABLE.

| username: Vincent_Wang | Original post link

Statistics health 99%

| username: 像风一样的男子 | Original post link

Why not create a composite index for the three fields in the where condition?

| username: Vincent_Wang | Original post link

Currently not built, 5.4.3 selects the task_id index quite quickly, why doesn’t 6.5.3 use the task_id index anymore?

| username: 像风一样的男子 | Original post link

The optimizer might think that using the task_id index is less efficient. You can try forcing the use of task_id and compare the results.

| username: Vincent_Wang | Original post link

Analyzed it again, it’s working now, strange.

| username: 像风一样的男子 | Original post link

This is generally caused by inaccurate statistics.

| username: Vincent_Wang | Original post link

The statistics just now showed that 99% of the time the wrong index was chosen. There’s a problem with this optimizer.

| username: Vincent_Wang | Original post link

Another table is written in the same way, analyzed 5 times, but still chooses the wrong index.

| username: 像风一样的男子 | Original post link

Which version did you upgrade from? Check if the system parameter “show variables like ‘%tidb_enable_index_merge%’” is set to on.

| username: Vincent_Wang | Original post link

Upgraded from 5.4.3, analyzed countless times, still chose the wrong index. The index was also analyzed separately, but still chose the wrong index.

| username: Fly-bird | Original post link

Information is not timely.

| username: tidb菜鸟一只 | Original post link

It should be that version 6.5 introduced a higher priority for using indexes on sorting fields, while version 5.4 had a lower priority. Therefore, version 5.4 can only use the id index, whereas version 6.5 will use the combined index of status and create_time. If it doesn’t work, try specifying the index with a hint.

| username: Vincent_Wang | Original post link

Can this be turned off, using an index for sorting fields?

| username: 路在何chu | Original post link

Just create a composite index for those three fields.

| username: Vincent_Wang | Original post link

It is possible to create an index on task_id and case_status. It is strange why the optimizer does not choose to use the single-column index on task_id.

| username: tidb菜鸟一只 | Original post link

Just use a hint to specify the index, /*+ USE_INDEX(t1, idx1) */

| username: Raymond | Original post link

Is there any documentation explaining the use of indexes for sorting fields?

| username: Raymond | Original post link

In version 5.4.3, why wasn’t there a separate sorting operator? Curious.