Execution Plans of Two TiDB Servers Are Inconsistent

Translated
1

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

Original topic: 两台tidb-server执行计划不一致

| username: 扬仔_tidb

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] v5.3.0
[Reproduction Path] Perform analyze on one of the tidb-servers, and the SQL execution plan on the other server is incorrect, leading to slow queries.
[Encountered Problem: Phenomenon and Impact] Execution plan inconsistency

  • First tidb-server

Performed analyze on a table on one of the tidb-servers, and the query execution plan on the other tidb-server is incorrect (pseudo).
Here is the screenshot:
(root@10.30.2.49:4000) [noah_tidb]> analyze table yangzai_wxhc_sub_biz_order;
Query OK, 0 rows affected, 1 warning (1 min 20.91 sec)
(root@10.30.2.49:4000) [noah_tidb]> explain analyze SQL statement
The result is as follows:

image
image1380×136 57.8 KB

What is the reason for this? The execution plans on the two tidb-servers are inconsistent. Do I need to perform analyze on both servers separately?

2
| username: 我是咖啡哥 | Original post link

Try logging in again on the second machine?

3
| username: 扬仔_tidb | Original post link

After logging in again, the execution plan is correct.
What about in a production environment with long connections? Will it always use the old execution plan if connected to the same server?

4
| username: 我是咖啡哥 | Original post link

I guess there should be some kind of refresh mechanism, and the execution plan is cached. It shouldn’t be so dumb as to always use the old execution plan, right?

5
| username: 扬仔_tidb | Original post link

Thank you, Coffee Brother.

6
| username: zhanggame1 | Original post link

You can check the documentation. TiDB’s session will cache the execution plan, and you can use a command to clear it.

Prepare Statement Execution Plan Cache | PingCAP Documentation Center

7
| username: redgame | Original post link

Reset Query Cache