PlanRegressionSql is ~90% of store statement cancellations: 25-90s dedup sort, no CommandTimeout, and a faster identical-result formulation #2827

Summary

PgFactCollector.PlanRegressionSql is ~90% of all statement cancellations on the use1 Darling store: 325 of 371 for 2026-09-03, and 31 of 34 in the two hours to 08:50Z. It has no explicit CommandTimeout, so it inherits Npgsql's undocumented 30 s default, and it measures 25–90 s on the busier servers. Every cancellation is then silently swallowed (see #2826).

Grouping every canceling statement due to user request in the store's PG log by the statement that follows it (with multi-line STATEMENT: continuation capture):

The plan is healthy — this is not #2796's or #2820's shape

EXPLAIN (ANALYZE, BUFFERS), server 1671144557, work_mem=31MB:

• Chunk exclusion works. All 5 chunks are read, but only because the query windows on 14 days (PlanRegressionWindowDays) 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 is doing its job and simply has nothing left to exclude. Compressed chunks use Custom Scan (ColumnarScan) at ~7–8k cost each; the recent uncompressed chunk is 1.195M of the 1.571M Append cost.
• No OR'd non-equi join (that was io_latency baseline times out: the robust scaffold's MAD tiers join on an OR'd non-equi predicate (23.7s -> 4.2s) #2820). Run Condition: (row_number() OVER w1 <= 1) pushdown is working, incremental sorts throughout.
• Execution Time: 26,565 ms, shared hit=146705 read=105041, temp read=70188 written=70208 (~548 MB spill, external merge).
• Funnel: ~4M rows scanned → 1,724,344 deduped → 81,127 plan_agg → 30,925 plan_dedup → 2,091 past the CPU floor → 20 rows.

work_mem is not the constraint, and raising it makes things worse — measured, not assumed:

Not a one-server outlier: the 3rd-busiest server measures 25,141 ms on the same plan shape. Several servers sit just under the 30 s cliff, which is why cancellation is intermittent (325/day) rather than total.

The dedup is the cost, and there is a faster formulation

Isolating the deduped CTE alone (same filters, same server, count(*) of the result):

(row-count differences are live-data growth between runs, not semantics — DISTINCT ON and array_agg agree exactly)

The window-function form forces a global sort of ~4M rows on 8 keys. The GROUP BY form lets the planner hash-aggregate at the interval grain and sort only within each small group.

Results are byte-identical. md5 over the full ordered result set, both arms, 6 servers:

Paired timings in that run (ORIG → NEW): 27.8→25.7, 25.5→24.0, 46.1→31.6, 43.6→39.0, 91.0→41.3 s.

Honest limits

• A later alternating A/B, run while the box was loaded by this investigation's own queries, was noisier: NEW won 3 of 4 and lost once (33.6 → 40.9 s). NEW's spread was 38.9–40.9 s against ORIG's 33.6–83.9 s, so the clearer benefit may be variance rather than median.
• This does not get the query under 30 s. It reduces and stabilises the cost; it does not by itself stop the cancellations. The unchosen deadline (Server-scoped watermark read is unbounded, times out, and silently degrades every collector to its fallback window #2795) and the swallowed failure (PlanRegressionSql timeouts are silently swallowed — a cancelled query is indistinguishable from 'no plan regressions' #2826) are the other two halves.

Correction to a prior finding

PlanRegressionSql was reported earlier as having "dropped off the cancellation list entirely". It did not — WITH deduped AS ( is PlanRegressionSql's leading CTE. The earlier grouping labelled it by method name and this one by query text; they are the same statement. Its rate fell from ~93/hr at 01:00Z to ~17/hr, tracking the overall decline after #2796 and the retention arming, but it has been the dominant source throughout.