get_daily_summary's cold call pays the rollup coverage probe: about 10 s, 537 MB read and 322 MB of temp, two-thirds of it one view's oldest chunk #4957

Class: bug (performance). On a 43-server SQL Server store running v3.9.0, a get_daily_summary call that finds the rollup-coverage cache cold costs about 13 s. That happens:

• on the process's first call;
• after a failed probe;
• when a view's floor-cache entry is over an hour old (RollupFloorMaxReuse).

Of the 13 s, about 9.7 s is the store-wide coverage probe (TimescaleSupport.RollupCoverageProbeSql, cold form). The summary statement itself is about 3.7 s for today on the busiest server.

Census (get_read_latency hours=168, about 4.5 h since the install)

The store's pg_stat_statements (mcp role) for the coverage probe: 3 calls, mean 3.9 s, max 9.8 s, 192 k blocks read and 104 k temp blocks written. That fits a few cold probes among warm ones.

Plans (EXPLAIN (ANALYZE, BUFFERS), one run each)

The probe's plan output was cut by the transport's size limit after the first views. The total is the plan's top node, and "—" means not captured.

What the probe's plans show:

• Each min(bucket) is a DeferredChunkAppend (Order: bucket, Chunks Visited: 1) over the view's oldest chunk.
• For a compressed chunk, that oldest chunk is decompressed and sorted to find one value.
• query_store_stats_interval_daily's oldest chunk spans ten days (09-14 to 09-24); the rest are one day each. It alone is two-thirds of the probe.

RangeSql for today: the coll CTE (collection_log, 1.67 s), queries (raw query_stats, 202 k rows, 1.43 s) and waits (0.50 s) are the main costs.

What earlier work changed

• Web viewer server page: the two daily-summary reads are ~26 of the Overview tab's ~28 s on a busy production primary, one at the viewer role's 15 s timeout — raw CTEs over the whole window plus a per-call rollup-coverage probe #3905: the Overview's daily-summary reads. It probes each day once, and the coverage gate is cached.
• The Daily Summary calendar recomputes a month of closed days from raw on every refresh: 0.55 s warm, 4.5 s cold per poll for 25 days that no longer change (WPF Daily Summary tab every minute, web server Overview every 60 s) #4232: the closed-day cache, 1 h.
• The rollup coverage probe writes about 170 MB of temp every five minutes by sorting two rollups' compressed batches #4539 / Store: re-measure a rollup's coverage floor only when its oldest chunk changes (#4539) #4553: the coverage probe's per-view floor cache, keyed on the oldest chunk's identity, with a 1 h maximum reuse. This cut the probe's re-sort from every five minutes to at most hourly.
• Darling reads a wait stored under both spellings as one wait under its clean name #4941: the wait CTEs' rtrim regroup. It's cheap.

Suspected cause

1. The cold probe's cost is one min() per view over a compressed oldest chunk (measured). The code comments already name this cost: "seq-scans and sorts that chunk's batch list".
2. How often it runs (inferred, from the code, not measured live): the floor cache is per NpgsqlDataSource. DetectRollupCoverageAsync runs on a timer and on demand, so each data source (the service's worker, the MCP host, the web host) re-pays the full sort at least hourly, plus on every process start.
3. A cold MCP call pays it inline, ahead of the summary itself, so the first answer takes about 13 s.

Options (inferred, for whoever designs the fix)

• Read each view's floor from chunk metadata, for example the compressed chunk's _ts_meta_min columns or range_start plus one bounded probe, instead of a decompress-and-sort.
• Share one floor cache across the process's data sources.
• Warm the probe in the background after start, so no caller pays it inline.

Code:

• DarlingHealthReader.GetDailySummaryRangeAsync → GetMeasuredRollupsAsync → ComposeStoreAvailability.ProbeAsync → TimescaleSupport.DetectRollupCoverageAsync / RollupCoverageProbeSql;
• DailySummarySql.RangeSql.