Update table statistics before publishing new schema version after DDL

[bolt-leandro]

Feature Request

Is your feature request related to a problem? Please describe:
After DDL on large 800GB table, statistics were cleared, leading to degraded performance cause by bad query plans (eg: full table scans).

The root cause is that after the Modify-Col DDL, the old statistics related to this modified column will no longer be used, and below is the 2 reason of this behavior:

The old statistics is collected based on the column's prior type so the prior stats-data is not compatible with the new column type, for example, after changing the column type from INT to VARCHAR(32), its prior stats-data based on INT can't be used to estimate for predicates like "col = 'xxx'", so we have to collect the stats again;

When modifying the column type, TiDB will create a new column (or an index) with a new colID (or idxID) to replace the old one, and TiDB uses the colID (or idxID) to access its statistics, so after the DDL, TiDB will not be able to find its prior statistics.

Describe the feature you'd like:
TiDB will collect new statistics just before it publishes the final schema version, so that after new schema goes live, stats are already collected and up to date. Something like this could work:

1. DDL is started and goes through normal stages
2. While DDL is in progress, application still sees original schema version and uses original statistics
3. DDL is finished and new schema version is ready to be published
4. Before publishing, collect statistics against the new version
5. Now publish the new schema version, discard old stats and use new stats collected in 4.
6. This means that there would be no bad query plans as we don’t need to wait for new stats, they were preemptively collected.

Describe alternatives you've considered:
It was suggested to use plan binding feature to prevent bad queries. Such workaround is very management heavy and not really feasible to find every query hitting the table and try to make a Plan Binding preemptively.

Teachability, Documentation, Adoption, Migration Strategy:
Currently large DDLs with MODIFY COLUMN are somewhat of a trap, as they have potential to cause cluster performance degradation.
This feature would prevent bad query plans with outdated stats being executed after large DDLs. Currently, such bad plans are be used until ANALYZE TABLE is run manually or it is picked up by auto analyze.

[qw4990]

Three possible solutions:

1. Collect the new stats in advance based on the new column type, and publish new stats and new schema version together;
2. Cast the prior stats data (TopN, Histogram) to the new column type directly;
3. After finishing DDL, the auto-analyze automatically collects the new stats immediately; (but this solution is not perfect, there is still a time window of stats-missing depending on how long the stats collection would take)

[bolt-leandro]

We had another similar incident but this time it happened with:

• ADD KEY idx_topt_si_ea (stable_id, expired_at)
• Table was 80GB
• DDL finished and started getting full table scans:

+--------+------------------------------+-------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
| JOB_ID | DB_NAME                      | TABLE_NAME  | JOB_TYPE               | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME         | START_TIME          | END_TIME            | STATE  |
|--------+------------------------------+-------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------|
| 8060   | XXXXXX | daily_price | add index /* ingest */ | public       | 6920      | 6942     | 757031812 | 2025-01-02 15:28:36 | 2025-01-02 15:28:36 | 2025-01-02 15:37:15 | synced |
+--------+------------------------------+-------------+------------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+

• auto analyze kicked in, took 18min to finish

select update_time, start_time, end_time, table_schema, table_name, instance, state from mysql.analyze_jobs where table_schema='XXX' order by start_time
                                ->   desc limit 2 \G
***************************[ 1. row ]***************************
update_time  | 2025-01-02 15:55:08
start_time   | 2025-01-02 15:37:17
end_time     | 2025-01-02 15:55:08
table_schema | XXX
table_name   | daily_price
state        | finished

• After DDL finished and until auto analyze finished collecting stats, bad query plans with full table scans started leading to high load/latency.
• Once auto analyze finished, plans went good again and load dropped, without manual intervention.

[pcqz]

The index range of original indexes can cover only one column stable_id or expired_at. After adding the index idx_topt_si_ea (stable_id, expired_at), these two columns are covered, and the original indexes will be ignored according to the skyline-pruning rule: the more access conditions an indexed column set covers, the better it is in this dimension.

From slow query log, the warnings were also shown as [{"Level":"Note","Message":"[daily_price,idx_topt_si_ea] remain after pruning paths for daily_price given Prop {SortItems: [], TaskTp: rootTask}","IsExtra":true}] , which means only the index idx_topt_si_ea remained after pruning. However, the stats for the new index were missing, which may lead to the over-estimated rows of index scan and higher cost of index lookup compared to full table scans.

The optimizer should take into account the existence of stats for the index when applying the skyline-pruning rule. As a workaround, some additional operation steps are needed when adding index:

1. Add new index but make it invisible
2. Wait for stats to be updated by auto anlyze (or manually trigger them)
3. Make new index visible.

[bolt-leandro]

Hello @pcqz,

Thanks for the feedback!

The index range of original indexes can cover only one column stable_id or expired_at. After adding the index idx_topt_si_ea (stable_id, expired_at), these two columns are covered, and the original indexes will be ignored according to the skyline-pruning rule: the more access conditions an indexed column set covers, the better it is in this dimension.

This explains the optimizer choice. However, it's still better to use old single column index with updated stats than new, two column index with no stats. Using old single column index is certainly much better than doing full table scans. This is where I think the optimizer needs improvement.

which means only the index idx_topt_si_ea remained

No indexes were removed, only idx_topt_si_ea (stable_id, expired_at) was added.

The optimizer should take into account the existence of stats for the index when applying the skyline-pruning rule.

Totally agree and this is what is being asked for. Hopefully this can be implemented soon.

As a workaround, some additional operation steps are needed when adding index:

Add new index but make it invisible
Wait for stats to be updated by auto anlyze (or manually trigger them)
Make new index visible.

Thanks for this workaround. It will mitigate ADD (or remove) INDEX DDLs causing outdated stats but other DDL types (eg: MODIFY COLUMN) will still cause this problem.