MAINTAINER DECISION: the plan-regression detector's "14-day" window only ever sees about 4-5 days of Query Store history on Darling stores with armed purges #3958

MAINTAINER DECISION: the plan-regression detector's "14-day" comparison window sees about 4-5 days on Darling

Darling's PLAN_REGRESSION detector and its regressed-queries drill-down both compare a query's current plan against the best plan it ran within 14 days. The fact uses PgFactCollector.PlanRegressionWindowDays = 14 and binds TimeRangeStart.AddDays(-14) on last_execution_time; the drill-down binds the same AddDays(-14). The code comments give the purpose as "the days-old 'best plan' baseline".

On any Darling store whose retention policies are armed, the rows that window reads are gone after about 4 days. query_store_stats is one of the three raw tables TimescaleDB drops at TimescaleSupport.RawRetentionInterval = "4 days" (RawTierCoverage feeding RetentionPolicies). So a plan whose last execution was 5 to 14 days ago cannot serve as the baseline. That is exactly the case the 14-day window was written for: a query that ran well on plan A last week, switched to plan B four days ago, and has run only on B since, is never reported, because plan A's rows are purged.

Don't fix this as a side effect of anything. Either direction changes findings, so the choice belongs to the maintainer.

How this was established

• Code: PgFactCollector.QueryPerf.cs, where PlanRegressionWindowDays = 14 is bound on last_execution_time, and PgDrillDownCollector.Queries.cs, which binds the same 14 days. Raw retention is TimescaleSupport.RawRetentionInterval = "4 days", applied to query_store_stats through RawTierCoverage and RetentionPolicies.
• DARLING01 (read-only probe, 2026-09-22 22:34 UTC):
  • timescaledb_information.jobs shows job 1091 as policy_retention on query_store_stats with {"drop_after": "4 days"}.
  • The hypertable holds five 1-day chunks, 2026-09-18 → 2026-09-23.
  • min(collection_time) = 2026-09-18 00:01:34, so 4.9 days of Query Store history exist, against the 14 days the detector reads.
• Production (use1): PlanRegressionSql is ~90% of store statement cancellations: 25-90s dedup sort, no CommandTimeout, and a faster identical-result formulation #2827's EXPLAIN already says it: "All 5 chunks are read, but only because the query windows on 14 days while retention now keeps 4 — the collection_time >= $3 bound added by [BUG] Darling: PLAN_REGRESSION analysis query full-scans/decompresses entire query_store_stats history every cycle (no collection_time bound) #2387 … simply has nothing left to exclude."

The window's real length depends on the store

So the same detector answers a different question depending on the store's retention state, and Darling and Lite disagree on the same data.

The decision

1. Make the window truly 14 days on Darling. That needs a source that retains 14 days of per-interval Query Store data. The latest-snapshot-per-interval table proposed in PLAN_REGRESSION's read deduplicates the whole raw Query Store slice every pass; keep the latest snapshot per interval as it is written #3953 could carry a 15-day horizon at interval-count cost; raising raw query_store_stats retention would multiply the store's largest QS hypertable by about 3.5. Findings change: more regressions, reported against baselines up to two weeks old.
2. Document it as about 4 days, and set PlanRegressionWindowDays (and the drill-down's literal 14) to the raw horizon so the code says what it does. On stores with armed purges, findings are unchanged. On held-purge and plain-PG stores, findings change (fewer), and so do Lite's if Lite follows for parity.
3. Leave it as is, with the store-dependent behaviour written down where the window is declared.

Related

• analyze_server takes 15–21 s on the production SQL store that ingests Query Store, dominated by PlanRegressionSql's per-call 14-day dedup — not the latest-value reads #3896 measured #3902 / The plan-regression drill-down reads only the fact's offenders instead of re-deduplicating the whole Query Store slice, and the PSP drill-down resolves text for its printed rows only (#3902) #3956: found while measuring the regression reads; The plan-regression drill-down reads only the fact's offenders instead of re-deduplicating the whole Query Store slice, and the PSP drill-down resolves text for its printed rows only (#3902) #3956 does not touch the window.
• PLAN_REGRESSION's read deduplicates the whole raw Query Store slice every pass; keep the latest snapshot per interval as it is written #3953: the structural fix for the regression read, which could carry option 1.
• [BUG] Darling: PLAN_REGRESSION analysis query full-scans/decompresses entire query_store_stats history every cycle (no collection_time bound) #2387: the chunk-exclusion bound that made the gap visible in plans.
• Retention policies run their first check immediately — no external window to pause them, caused permanent data loss #1680 / Rollup CAGGs serve only materialized buckets: old windows read empty and raw purges stay held #1759: retention arming and the held-purge store profile.