Importing Data from TiDB Lightning v5.4.1 to v6.5.3 Cluster: Execution Plan Inconsistency

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

Original topic: 用TiDB Lightning 把v5.4.1数据导入v6.5.3集群,执行计划不一致

| username: 你说嗨我说拜

[TiDB Usage Environment] Production Environment
[TiDB Version] v6.5.3
[Reproduction Path] Importing v5.4.1 data into v6.5.3 cluster using TiDB Lightning
[Encountered Problem: Phenomenon and Impact]
The table structure is the same, but the execution plan query has turned into a full table scan.

Found that a table’s query IndexRangeScan has turned into IndexFullScan global scan

[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

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

Recollect the statistics of the table on 6.5. Seeing the above keyword, all indicate that the statistics are inaccurate.

| username: 你说嗨我说拜 | Original post link

Execution plan after statistics

| username: 有猫万事足 | Original post link

One is from July, and the other is from June.
There is a significant difference in the amount of data based on the conditions.
July has only over 10,000, while June has nearly 3 million.

The main table, r, has always been using IndexRangeScan.
The other smaller sub-tables will choose between IndexRangeScan or IndexFullScan based on the amount of data returned by the main table. I don’t think there’s a big issue.

| username: 你说嗨我说拜 | Original post link

Changed to the June version.

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

Sorry, I made a mistake earlier. The statistics for your version 5.4 are also inaccurate. For table O, you need to collect statistics.

| username: 你说嗨我说拜 | Original post link

All changed to June, re-statistics were done, and the data was migrated from v5.4.1.

| username: Kongdom | Original post link

It was imported through a tool, so it should be necessary to manually or automatically collect the statistics once. The statistics information should not be imported as well.

| username: 胡杨树旁 | Original post link

I don’t know if the optimizer has made improvements or something, but the way the tables are joined and the execution order are different now.

| username: 你说嗨我说拜 | Original post link

@Kongdom @胡杨树旁 The v6.5.3 here is executed after manual statistics.

| username: redgame | Original post link

Which one is faster?

| username: 你说嗨我说拜 | Original post link

5.4.1 needs to execute quickly.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.