Inaccurate Execution Plan

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

Original topic: 执行计划不准

| username: TiDBer_hwEZA4rV

I found a slow SQL statement

SELECT max(id) FROM ua_ad_message WHERE task_id = '434362';

Result:

+-----------+
| max(id)   |
+-----------+
| 390061695 |
+-----------+
1 row in set (7.64 sec)

Count of rows matching the condition:

SELECT count(*) FROM ua_ad_message WHERE task_id = '434362';

Result:

+----------+
| count(*) |
+----------+
|    38392 |
+----------+

Table indexes:

KEY `idx_inner_batch_no_version` (`inner_batch_no`,`version`),
KEY `idx_task_id_tel` (`task_id`,`tel`),
KEY `idx_task_id_status` (`task_id`,`status`)

Execution plan:

EXPLAIN SELECT max(id) FROM ua_ad_message WHERE task_id = '434362';

Result:

+--------------------------------+---------+-----------+---------------------+----------------------------------------------------------+
| id                             | estRows | task      | access object       | operator info                                            |
+--------------------------------+---------+-----------+---------------------+----------------------------------------------------------+
| StreamAgg_10                   | 1.00    | root      |                     | funcs:max(tidb_mycat_onesms.ua_ad_message.id)->Column#22 |
| └─Limit_14                     | 1.00    | root      |                     | offset:0, count:1                                        |
|   └─TableReader_23             | 1.00    | root      |                     | data:Limit_22                                            |
|     └─Limit_22                 | 1.00    | cop[tikv] |                     | offset:0, count:1                                        |
|       └─Selection_21           | 1.00    | cop[tikv] |                     | eq(tidb_mycat_onesms.ua_ad_message.task_id, 434362)      |
|         └─TableFullScan_20     | 4333.97 | cop[tikv] | table:ua_ad_message | keep order:true, desc                                    |
+--------------------------------+---------+-----------+---------------------+----------------------------------------------------------+

From the execution plan, it shows a full table scan with an estimated 4333 rows.

After manually executing ANALYZE TABLE, the execution plan remains unchanged.

Using a specified index resolves the issue:

SELECT /*+ USE_INDEX(ua_ad_message, idx_task_id_status) */ max(id) FROM ua_ad_message WHERE task_id = '434362';

Result:

+-----------+
| max(id)   |
+-----------+
| 390061695 |
+-----------+

Total number of rows in the table:

SELECT count(*) FROM ua_ad_message;

Result:

+----------+
| count(*) |
+----------+
| 97841443 |
+----------+
1 row in set (6.30 sec)

Analyze-related configurations:

SHOW VARIABLES LIKE '%analyze%';

Result:

+----------------------------------------+-------------+
| Variable_name                          | Value       |
+----------------------------------------+-------------+
| tidb_analyze_partition_concurrency     | 1           |
| tidb_analyze_version                   | 2           |
| tidb_auto_analyze_end_time             | 23:59 +0000 |
| tidb_auto_analyze_partition_batch_size | 1           |
| tidb_auto_analyze_ratio                | 0.5         |
| tidb_auto_analyze_start_time           | 00:00 +0000 |
| tidb_enable_analyze_snapshot           | OFF         |
| tidb_enable_auto_analyze               | ON          |
| tidb_enable_fast_analyze               | OFF         |
| tidb_max_auto_analyze_time             | 43200       |
| tidb_mem_quota_analyze                 | -1          |
| tidb_persist_analyze_options           | ON          |
+----------------------------------------+-------------+

What could be the reason for this?

| username: zhanggame1 | Original post link

Try restarting the TiDB server and then try again after a while?

| username: h5n1 | Original post link

Please provide the table structure and the execution plan from EXPLAIN ANALYZE. Then try using SELECT /*+ AGG_TO_COP() */ and share the execution plan.

| username: Kongdom | Original post link

Is the table’s health not good? It is estimated that the low health caused the wrong execution plan to be executed.

| username: TIDB-Learner | Original post link

Personally, I feel that TiDB’s statistical estimates inherently have errors. Even after a series of optimizations, issues still exist. For example, if you know exactly which index to use, you can manually specify it. Add as many indexes as possible; some composite indexes might have problems, so optimize and break them down according to your own scripts.

| username: dba远航 | Original post link

Explain is not accurate, you need to use explain analyze.

| username: 春风十里 | Original post link

Is ID the primary key? It seems to be an issue with the optimizer. Try the following optimization:

SELECT id FROM ua_ad_message WHERE task_id = '434362' ORDER BY id DESC LIMIT 1;

It would be best to provide the table structure and the output of EXPLAIN ANALYZE for further analysis.

| username: oceanzhang | Original post link

Specifying the index seems to have solved the problem, but it hasn’t addressed the root cause. For the developers, they might be quite frustrated.

| username: 小龙虾爱大龙虾 | Original post link

Post the table structure. I guess your id is the primary key because when you perform a table full scan with keep order set to true, TiDB might think that scanning the table in order will quickly find the task_id=‘434362’. TiDB has a similar issue where it always thinks the cost of sorting is high. In fact, creating an index on the single task_id column should be faster.

| username: Kongdom | Original post link

:yum: Then this developer can be let go, their emotions are too unstable~

| username: andone | Original post link

show stats_healthy to check the health of the table

| username: oceanzhang | Original post link

This is indeed not a development issue. In our company, we can’t just release a new version to fix the program whenever there’s a problem with SQL. Since it’s not following the optimal path, it’s definitely the DBA’s responsibility to solve the problem.

| username: zhanggame1 | Original post link

Is the task_id of ua_ad_message the primary key?