Need a more clear behavior about how to load needed statistics

[winoros]

Enhancement

Some facts of the current TiDB:

• We've introduced ANALYZE TABLE PREDICATE COLUMNS and ANALYZE TABLE COLUMNS to collect parts of table columns to improve the speed of analyze and optimize the collected statistics' memory usage.
• When we use this two ways to collect statistics, the stats of one column that is not a predicate or not specified in COLUMN_LIST will not be deleted.
• If one SQL marks that column as stats needed, we'll load its outdated stats into memory.
• In many cases, the loading procedure will not be a problem. But if those columns are quite large. It will affects the performance of sync load.
• And there'll be the same problem for change value of the variable tidb_analyze_skip_column_types

[hawkingrei]

ref #57144

[elsa0520]

User could not specified stats columns when tidb just convert stats from version1 to version2

This is another one case about the "load needed statistics". When user try to specified stats columns in version2, tidb analyze all of columns instead of specified column and throw warning : "Table test.xxxhas version 1 statistics so all the columns must be analyzed to overwrite the current statistics"

The reason is that if there are stats meta for all columns in version1, tidb can not mixed stats meta between version1 and version2. So it maybe happen when user just convert stats version from 1 to 2 and specified stats columns.

Reproduce step

v6.5.6

1. set analyze to version1
2. analyze all column in table
3. finish all column stats collect in version1
4. change analyze version from 1 to 2
5. specified columns in analyze stmt

analyze table test.xxxcolumns xx,xx;

6. tidb collect all columns instead of spesified column and throw warning

Table test.xxx has version 1 statistics so all the columns must be analyzed to overwrite the current statistics

Expect behavior

Users of wide tables need to specify the columns to collect, because wide columns will cause the statistics collection task to fail due to insufficient memory.
So when users switch to version 2, auto analyze usually fails (because the default behavior is to collect all columns), and user must specify columns to collect statistics.
And we rejected this user's behavior, resulting in users being unable to avoid memory problems during statistics collection.
Of course, we can also achieve this goal by letting users manually drop stats first and then specify columns. But this is not easy to use.

[songrijie]

For original questions created by @winoros , I prefer to expire the column stats that are no longer maintained.

• The column stats that are not maintained could be far behind actual ones, which is "dangerous" to use. However, we are not aware of this.
• The way we treat pseudo stats is supposed to be evolved. The cardinality should be estimated wisely.
• SPM is planning to identify the plans that are created in improper ways (pseudo stats, out-of-range, etc), and make them targets for plan evolution or sql tuning.

Based on the above, it makes sense to me we go for the "pseudo" process instead of retrieving historical stats.

As per my understanding, in this case, we can also make the transition from V1 to V2 smooth by replacing the expired options with new ones.

@elsa0520 @Rustin170506 @terry1purcell