Statistics and Execution Plans

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

Original topic: 关于统计信息以及执行计划

| username: 大钢镚13146

Does the statistical information only affect the index of the execution plan and the join order? If the chosen index and join order are correct, will the statistical information not affect the final execution time?

| username: Jellybean | Original post link

An SQL statement is usually optimized during the physical optimization phase based on statistical information (such as table row count, column details, index information, etc.), so the final execution plan is influenced by the statistical information. Moreover, the accuracy of a plan is determined by the system based on the current available information (including statistical information) and will automatically adjust to what it considers the optimal solution, even if the final execution result is not the best.

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

Yes, if the execution plan generated with incorrect statistics is the same as the one generated with correct statistics, the final execution effect will be the same.

| username: dba远航 | Original post link

Statistics are used as a reference for cost when generating a physical execution plan, but they cannot determine execution time. Execution time is the actual time spent on CPU, I/O, network communication, etc.

| username: Kongdom | Original post link

I think your understanding is correct. It’s not necessary to insist that table health is 100%.

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

Statistics are only a reference factor when generating the execution plan. Once the execution plan is generated, the execution speed is not affected by the statistics.

| username: andone | Original post link

The role of statistics is to enable the database system to more accurately understand the distribution and quantity of data, so as to generate more accurate execution plans.

| username: heiwandou | Original post link

If the execution plan is not considered, statistical information does not affect the final execution time.

| username: zhanggame1 | Original post link

The execution plan is based on statistical information, and incorrect statistical information may result in an incorrect execution plan, leading to a significant drop in query performance.