planner, statistics: plan cache is not invalidated after sync-load timeout fallback when stats become fully loaded

[winoros]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

set global tidb_enable_prepared_plan_cache = 1;
set global tidb_plan_cache_invalidation_on_fresh_stats = 1;
set global tidb_stats_load_pseudo_timeout = 1;
set session tidb_stats_load_sync_wait = 1;

drop table if exists t;
create table t(a int primary key, b int, key idx_b(b));
insert into t values
(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
analyze table t all columns;

prepare st from 'select * from t where b > ?';
set @p = 3;
execute st using @p;
execute st using @p;
select @@last_plan_from_cache;

-- sync-load timeout already enqueues background processing; wait until it finishes
-- poll and ensure needed stats are no longer allEvicted/unInitialized
show stats_histograms where db_name='test' and table_name='t';

execute st using @p;
select @@last_plan_from_cache;

Observed in our case: after async load success, @@last_plan_from_cache is still 1 (the plan generated under sync-load timeout fallback is reused).

2. What did you expect to see? (Required)

If a plan was generated when sync load timed out and optimizer fell back to pseudo/partial stats,
then once related stats become fully loaded, the cached plan should be invalidated (or bypassed once)
so optimizer can re-plan with loaded stats.

3. What did you see instead (Required)

The cached plan keeps being reused (@@last_plan_from_cache = 1) after stats become fully loaded in memory,
until an operation that bumps stats version (e.g. ANALYZE TABLE) or manual cache flush happens.

4. What is your TiDB version? (Required)

Built from current master branch for code verification:

• commit: d2b270aa57

(Please replace with SELECT tidb_version() output if needed in production reproductions.)

Code snippets (suspected root cause)

1. Timeout fallback keeps SQL going and can still allow cacheable plan generation:

// pkg/planner/core/rule/rule_collect_plan_stats.go
if err != nil {
    stmtCtx.IsSyncStatsFailed = true
    if vardef.StatsLoadPseudoTimeout.Load() {
        stmtCtx.AppendWarning(err)
        return nil
    }
}

2. Plan cache invalidation on fresh stats is keyed by stats version hash only:

// pkg/planner/core/plan_cache_utils.go
if sctx.GetSessionVars().PlanCacheInvalidationOnFreshStats {
    var statsVerHash uint64
    for _, t := range stmt.tables {
        statsVerHash += getLatestVersionFromStatsTable(sctx, t.Meta(), t.Meta().ID)
    }
    hash = codec.EncodeUint(hash, statsVerHash)
}

3. getLatestVersionFromStatsTable uses LastUpdateVersion from columns/indices:

// pkg/planner/core/logical_plan_builder.go
statsTbl.ForEachColumnImmutable(func(_ int64, col *statistics.Column) bool {
    version = max(version, col.LastUpdateVersion)
    return false
})
statsTbl.ForEachIndexImmutable(func(_ int64, idx *statistics.Index) bool {
    version = max(version, idx.LastUpdateVersion)
    return false
})

4. Sync-load mainly flips load status (allEvicted/fullLoaded) but does not bump LastUpdateVersion:

// pkg/statistics/handle/syncload/stats_syncload.go
if fullLoad {
    colHist.StatsLoadedStatus = statistics.NewStatsFullLoadStatus()
} else {
    colHist.StatsLoadedStatus = statistics.NewStatsAllEvictedStatus()
}

Impact

• A fallback plan generated under timeout can remain cached even after stats are fully loaded.
• Self-healing is delayed unless stats version changes.
• Can cause long-tail latency regressions for hot prepared statements.

Workarounds

1. Increase tidb_stats_load_sync_wait to reduce timeout fallback probability.
2. Trigger ANALYZE TABLE to bump stats version.
3. Use /*+ IGNORE_PLAN_CACHE() */ on affected SQL.
4. Flush plan cache manually if needed.

[winoros]

Follow-up with a more deterministic test-oriented reproduction idea:

A SQL-only reproduction can be timing-sensitive. For a stable regression test, we can force the sync-wait path to fail and then verify cache behavior before/after async processing of timed-out sync-load tasks finishes.

Suggested test skeleton (Go test style):

// 1) setup
set global tidb_enable_prepared_plan_cache=1;
set global tidb_plan_cache_invalidation_on_fresh_stats=1;
set global tidb_stats_load_pseudo_timeout=1;
set session tidb_stats_load_sync_wait=1;

create table t(a int, b int, key idx_b(b));
insert into t values ...;
analyze table t all columns;

// 2) force sync wait failure but do not fail SQL
require.NoError(t, failpoint.Enable("github.com/pingcap/tidb/pkg/planner/core/assertSyncWaitFailed", `return(true)`))
prepare st from 'select * from t where b > ?';
set @p = 3;
execute st using @p;
execute st using @p;
// plan is now cacheable and typically cached from fallback path
select @@last_plan_from_cache; // expect 1
require.NoError(t, failpoint.Disable("github.com/pingcap/tidb/pkg/planner/core/assertSyncWaitFailed"))

// 3) wait until async processing of timed-out sync-load tasks succeeds
// (poll show stats_histograms and ensure needed stats are no longer allEvicted/unInitialized)
require.Eventually(t, func() bool {
    rows := testKit.MustQuery("show stats_histograms where db_name='test' and table_name='t'").Rows()
    s := fmt.Sprint(rows)
    return !strings.Contains(s, "allEvicted") && !strings.Contains(s, "unInitialized")
}, 10*time.Second, 200*time.Millisecond)

// 4) execute again
execute st using @p;
select @@last_plan_from_cache;
// current behavior: often still 1
// expected (for this issue): should be 0 once, then 1 after re-cache

Potential acceptance criterion for a fix:

• After stats transition from partial/pseudo-load fallback to full-load after sync-load timeout (without analyze), the first next execution should bypass stale cache once (@@last_plan_from_cache=0), then subsequent executions can hit cache again (=1).

This should make the behavior and expected outcome explicit for a regression test.

[winoros]

Actually, there's another problem that TiKV's resource control/priority control failed.
The SQLs related to sync load are currently in the default resource group with high priority.
And most of the users' SQL belongs to the default resource group.

So SQLs related to sync load should be the highest priority ones.
However, total_process_time: 331.8µs, total_wait_time: 303.6ms, total_kv_read_wall_time: 1ms, tikv_wall_time: 304.1ms, the wall time of one of the SQL is 300+ms, causing the timeout of the sync load.

But this seems difficult to solve; it's related to the concurrency limiter of the tikv read pool.

The plan cache invalidation may be an easy one to do.

[qw4990]

A summary regarding this problem:

1. Plan Cache relies on stats-version (or its hash) to invalidate cached plans
2. Stats Sync Load doesn't update stats-version, since its work is just loading missing stats data
3. Plans generated with the same stats-version but with missing stats data won't be invalidated