Repository navigation
[BUG] Darling: PLAN_REGRESSION analysis query full-scans/decompresses entire query_store_stats history every cycle (no collection_time bound) #2387
Description
Component
Darling
Performance Monitor Version
3.5
SQL Server Version
SQL Server Version: N/A — this is the Darling PostgreSQL/TimescaleDB store's own internal analysis query, not a monitored-server DMV query. (Monitored fleet is a mix of SQL Server 2014–2022.)
Windows Version
Windows 11
Describe the Bug
PgFactCollector.QueryPerf.cs 's PlanRegressionSql (feeds the PLAN_REGRESSION analysis fact, run every scheduled analysis cycle — default 30 minutes — for every monitored server) filters collect.query_store_stats only on last_execution_time >= $2 (a 14-day window, no upper bound):
FROM v_query_store_stats
WHERE server_id = $1
AND execution_type_desc = 'Regular'
AND last_execution_time >= $2
query_store_stats is a TimescaleDB hypertable partitioned/compressed on collection_time (per TimescaleSupport.cs ), not last_execution_time . Since the predicate never touches collection_time , TimescaleDB can't exclude chunks by time — every cycle has to scan, and for compressed chunks decompress, the entire history of that table for the given server. PgTableTuning.cs 's covering indexes ( idx_query_store_stats_query_hash , idx_query_store_stats_server_db_query_plan_time ) don't cover last_execution_time either, so there's no index path around it.
The code comment on CollectPlanRegressionFactsAsync even flags this as deliberate: "Unlike other collectors this windows on last_execution_time (14-day comparison window), NOT collection_time." That tradeoff wasn't validated at scale — once the store's collect schema passes ~50GB and enough history has moved into compressed chunks (1-day compress_after policy), this query becomes a full-history decompress-and-scan, repeated per server, per cycle.
Steps to Reproduce
- Run a Darling store monitoring a non-trivial fleet (56 servers here) long enough that collect.query_store_stats exceeds ~50GB and most chunks are TimescaleDB-compressed.
- Let the scheduled analysis job run ( analysis.intervalMinutes , default 30) across all monitored servers.
- Watch postgres.exe CPU/backend count during an analysis pass.
Expected Behavior
Analysis queries should prune to a bounded, recent time range via the hypertable's partitioning column ( collection_time ), so per-cycle cost stays roughly constant as total history grows — compression should reduce disk footprint without inflating CPU cost per analysis cycle.
Actual Behavior
• Get-Service shows PerformanceMonitor Darling healthy, but the host sits at 100% CPU with 34 concurrent postgres.exe backend/worker processes during analysis passes.
• Checkpoints stall for minutes writing a few hundred KB ( write=267.522 s ... total=275.365 s for 317 buffers) — consistent with CPU starvation, not I/O.
• The exact PlanRegressionSql text ( regression_factor , latest_total_cpu_us >= 10000000 ) appears repeatedly in postgresql-*.log alongside background worker "parallel worker" ... exited with exit code 1 and unrelated statements (e.g. SELECT pg_database_size(current_database()) ) being canceled — collateral contention from the analysis query saturating the box.
• Host is a VM with only 4 cores / 8 logical processors (32GB RAM) — modest, but the query cost scales with total historical data rather than the configured analysis window, so no reasonable VM size fixes it long-term without also bounding the query.
Error Messages / Log Output
2026-08-20 17:47:14.291 UTC [5820] LOG: background worker "parallel worker" (PID 11876) exited with exit code 1
2026-08-20 17:47:14.431 UTC [5820] LOG: background worker "parallel worker" (PID 28864) exited with exit code 1
2026-08-20 17:47:23.150 UTC [12060] LOG: checkpoint complete: wrote 317 buffers (0.2%), wrote 2 SLRU buffers; ... write=267.522 s, sync=5.504 s, total=275.365 s; ...
2026-08-20 17:47:27.369 UTC [25268] ERROR: canceling statement due to user request
2026-08-20 17:47:27.369 UTC [25268] STATEMENT: SELECT pg_database_size(current_database())
(Full PlanRegressionSql CTE — latest / deduped / plan_agg / ranked / compared — visible verbatim in the same log window; omitted here for length, available on request.)
Screenshots
No response
Additional Context
• This started only after the store's collected metrics passed ~50GB; before that, the same unbounded scan was cheap enough to be invisible.
• Suspect fix: add a collection_time lower bound alongside last_execution_time in PlanRegressionSql (e.g., mirror the 14-day window onto collection_time too) so TimescaleDB can chunk-exclude, or add a covering index that includes last_execution_time scoped to server_id . Happy to open a PR once the intended semantics of the 14-day last_execution_time window (vs. collection_time ) are confirmed by a maintainer, since the current behavior is called out as intentional in the code comment.
• Not monitoring Azure SQL DB/MI/RDS for the affected analysis path — all servers involved are on-prem/VM SQL Server 2014–2022.
Activity
erikdarlingdata commentedon Aug 20, 2026
Confirmed, and thank you — this is an unusually good report. You correctly identified the mechanism, the reason the indexes do not rescue it, and the fact that the comment claims the tradeoff is deliberate. All three hold up.
Reproduced in the source:
collection_timeappears inPlanRegressionSqlexactly once, inside a window function'sORDER BY, which does nothing for chunk exclusion. So on a hypertable partitioned and compressed oncollection_time, every cycle reads the server's whole history and decompresses whatever is compressed — per server, per cycle.This is the same bug class we shipped a fix for in 3.5.0 as #2344, one query over. That one was an unbounded
MAX(last_execution_time)in the per-database watermark read, and the measured numbers there look a lot like yours: 25,766 buffer reads plus temp spill cold against 29 ms once bounded, with five chunks excluded. The cost scaled with STORE SIZE rather than with anything the monitored server was doing — which is exactly your point that no VM size fixes it, because the growth term is total history, not the analysis window.That we fixed the identical shape in the watermark read and left this one is on us, not on you for finding it.
On the "deliberate" comment
The comment is right that the SEMANTIC window belongs on
last_execution_time— a plan regression is about when the query last ran, not when we happened to collect it, and switching the window tocollection_timewould change which rows qualify. Your instinct to ask before PRing was correct.But adding a
collection_timebound ALONGSIDE it does not change the result set, and that is what makes this cheap to fix. We only ever learnlast_execution_timeby collecting it, so for any rowcollection_time >= last_execution_time— a row cannot be collected before the execution it reports. Any row satisfyinglast_execution_time >= now() - 14 daystherefore also satisfiescollection_time >= now() - 14 days. The extra predicate is redundant to the answer and load-bearing for the planner, which is precisely the property #2344 relied on ("provably free": every consumer's floor already made the excluded rows unable to change the result).One wrinkle to handle rather than hand-wave:
last_execution_timecomes from the monitored server's clock andcollection_timefrom the store's. A monitored server running ahead could report alast_execution_timeslightly greater than thecollection_timeof the row carrying it, so the bound needs a skew margin — a day is comfortable and still excludes essentially all of the history. Without the margin this would silently drop rows near the window edge on any fleet with clock drift, which is a worse bug than the one being fixed.Please do open the PR, if you still want it
Semantics confirmed: keep
last_execution_time >= $2as the comparison window, addcollection_time >= $2 - <skew margin>purely so TimescaleDB can exclude chunks, and update that comment to say the window is onlast_execution_timeand that thecollection_timebound is a redundant pruning aid rather than a second filter — otherwise the next person removes it as dead weight.Worth adding an
EXPLAIN (ANALYZE, BUFFERS)before and after against a store with compressed chunks; that is what made #2344's review easy to trust, and a chunks-excluded count in the plan is the direct evidence.If you would rather hand it off, say so and I will take it — you have already done the hard part. Either way this is going in.
Meanwhile
analysis.intervalMinutesis your lever if the box is unusable right now: raising it does not reduce the per-cycle cost but does reduce how often you pay it. Not a fix, and I would not leave it there.The 34 concurrent backends and the 267-second checkpoint write for 317 buffers are consistent with your CPU-starvation reading rather than an I/O problem, and the cancelled
pg_database_sizeis almost certainly the store-metrics snapshot getting caught in the same contention — collateral, not a second fault.Bound PLAN_REGRESSION on the partitioning column too (#2390)
erikdarlingdata commentedon Aug 21, 2026
Closing: fixed by #2390. Being explicit about what is proven and what is not, because the obvious end-to-end check does not work on this fleet yet and its result is misleading.
What is proven
The mechanism. Chunk exclusion requires a predicate on the partitioning column, and the query filtered only on the columns it compares. Measured separately during the investigation: with a bound on
collection_time,Chunks excluded during startupwent from 0 to 4 of 6.The predicate is in the shipped SQL and pinned by test — including that it is a bare parameter (
collection_time >= $3) rather than an expression like$2 - INTERVAL '1 day', since a bare comparison is the form runtime chunk exclusion handles most reliably, and that the exclusion bound sits below the comparison window by a skew margin (last_execution_timecomes from the monitored server's clock,collection_timefrom the store's, so a server running fast could otherwise have its newest rows excluded — a silent under-count, which is a worse bug than the one being fixed).What could not be verified on the dogfood fleet, and why the attempt misleads
I ran the shipped query against
prod-sql-use1-monitor-01(which holds the live Query Store data) both with and without the new bound:Identical, which looks like the fix doing nothing. It is not — there is nothing to exclude:
Neither store holds data older than the 15-day bound, because the #2296 split re-homed Query Store collection on 2026-08-15. A bound that nothing falls outside of excludes nothing, correctly. Reading that A/B as "the fix does not work" would be the same error as measuring a retention backlog against the wrong cutoff — the number is real and means something other than it appears to.
When it becomes observable
query_storedefaults to 30 days retention against a 14-day comparison window and a 15-day exclusion bound, so at steady state roughly half the chunks are skippable. use1 needs >15 days of Query Store history to show it, which is about 2026-09-14. Worth re-running the same A/B then; it belongs with #2019's early-September re-scan rather than holding this issue open for three weeks.The defect — a query bounded on everything except the column that governs chunk exclusion — is fixed and pinned either way.