How to Lock Statistics

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

Original topic: 如何锁定统计信息

| username: tidb狂热爱好者

【TiDB Usage Environment】Online
【TiDB Version】
【Encountered Problem】


【Reproduction Path】What operations were performed to encounter the problem
【Problem Phenomenon and Impact】
If tidb_enable_pseudo_for_outdated_stats is set to off,
what impact will it have?

Expected to improve execution performance by locking statistics.
Can you check if my expectation can be met?

| username: TiDBer_vfJBUcxl | Original post link

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

Setting tidb_enable_pseudo_for_outdated_stats to off means that even if the statistics are outdated, they will still be used as usual. Setting it to on means that if the statistics are outdated, fake statistics will be used. This value does not affect manual or automatic statistics collection.

lock stats directly locks the table’s statistics, preventing automatic statistics collection, and manual execution will also result in an error.

To completely stabilize the execution plan of an SQL statement, you should set tidb_enable_pseudo_for_outdated_stats to off and then use lock stats to lock the table’s statistics.

| username: zhanggame1 | Original post link

This prompt is not recommended to use, right?

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

Even if this thing becomes a normal feature, it’s still not recommended to use it. You can try it when you have no other options…

| username: cassblanca | Original post link

When statistics are outdated, using a pseudo-random number generator to estimate statistics instead of using real statistics can help avoid negative impacts on query performance to some extent. If you simply want a stable execution plan, using hints might be more reliable in my opinion.