Repository navigation
PLAN_REGRESSION's read deduplicates the whole raw Query Store slice every pass; keep the latest snapshot per interval as it is written #3953
Description
Activity
Ruled (Erik, 2026-09-23): build it. Keep the latest snapshot per Query Store interval in a collector-written table with a 15-day horizon. That also makes #3958's 14-day window real (option 1 there). Conditions:
- A written design goes through an adversarial review before any code. It must cover:
- the writer upsert;
- retention;
- the coverage-aware fallback to the raw read (in place of a heavy backfill);
- the migration rung;
- Checkpoint fsync on the busy store is dirtied-file count, not IOPS and not inventory: provisioning would buy nothing, checkpoint_timeout is the near-term lever, and the durable question is which aggregates need an hourly refresh at all #3892's write amplification on the Query Store-ingesting store, as a first-class input.
- Not before Thursday's release.
- Findings will change: regressions against baselines up to two weeks old. That is intended.
- A written design goes through an adversarial review before any code. It must cover:
Ruled (Erik, 2026-09-23): go tonight. First finish the narrow v2 design round the review asked for, then build it.
Design v2 (the narrow round the review asked for). The table, key, 15-day retention and fallback rule are unchanged from v1.
The table.
collect.query_store_interval_latest: one row per Query Store interval, holding its latest Regular snapshot. Hypertable onfirst_execution_time, 1-day chunks, oneNULLS NOT DISTINCTunique index, compression at 2 days.How it stays current. Inside the raw COPY's transaction, one set-based upsert reads the batch back from raw by
collection_time = $n, behind a savepoint with a 5 slock_timeout. On an apply fault it rolls back to the savepoint, records a pending-replay row in the same transaction, and commits raw. So raw ingestion no longer depends on the table (review finding 1). The next apply for that server replays the pending rows. Nothing in the write transaction reads raw unbounded (finding 2). Coverage creation and an hourly gap check run before the COPY, bound by bare parameters.The read change. PLAN_REGRESSION and its drill-down aggregate the table instead of deduplicating raw. A server's reads use the table only when its coverage holds everything raw would read and it has no pending rows; otherwise they run the shipped raw SQL. Over the same snapshots the results are identical (md5 oracle).
Budget (finding 3). The ceiling is the mcp and viewer roles' 15 s
statement_timeout, and #3902's 5 s is the goal. The busy server's 14-day plan count can't be measured tonight (client-site), so it gates the merge. At or under 250K plan groups, the table ships alone. Over that, a per-(plan, day) rollup lands in the same PR.Best-plan age (finding 4). The drill-down carries
best_plan_last_seen, and the advice states the age. The unattended bot getsMaxBestPlanAgeDays = 4, which keeps its would-force journal in the regime it has been scored in.Migration. One rung (V140): the table, a coverage table and the pending table. Engine-plain; the hypertable conversion runs at startup,
collection_log's way.- added 6 commits that reference this issue
on Sep 23, 2026 Ruled (Erik, 2026-09-23): shelved. The latest-snapshot table was built and tested (branch
fix/3953-qs-interval-latest, commit f6075fb, with V140 and three tables). Measured on the rig, a full 14-day pass only goes from about 6.5 s to 5.5 s, and the followed drill-down gets 4x slower (71 ms to 286 ms). That doesn't pay for three new tables, a writer inside the Query Store COPY transaction, the replay path, and the compression-band conflict. Reopen if field data from the busy client-site server shows PLAN_REGRESSION dominating analyze_server; the branch and its handoff (design v2 plus the parked hypertable patch) are ready to resume.Field data for the shelving condition ("reopen if field data from the busy client-site server shows PLAN_REGRESSION dominating"). This comes from the production store that ingests the fleet's live Query Store data (43 SQL Servers), on build 3.8.0-nightly.20260924.467, via
get_store_query_stats role=owner. It's cumulative since the service restart, about 5 h of steady-state passes (2026-09-24 22:09Z → 2026-09-25 ~02:15Z).The
PlanRegressionSqlstatement (WITH deduped AS ( … (array_agg(query_plan_hash ORDER BY collection_time DESC, execution_count DESC))[1] …) is the store's single heaviest statement:measure value calls 344 (about one per server per pass) mean / max 15.0 s / 58.5 s (the rig figure at shelving was ~6.5 s for a 14-day pass) total 5,164 s = 22.5% of all owner-role store time (22,914 s) shared blocks read 19.7 M (≈162 GB) temp blocks written 10.35 M (≈85 GB in ~5 h), about 250 MB per call and 91% of the temp writes among the top 40 statements The next heaviest statements are 1,951 s and 1,756 s, so this one is roughly 2.6× anything else the store runs.
Two things the rig numbers didn't show:
- Write amplification. At this rate the spill alone is about 400 GB/day of temp writes on the gp3 volume. PLAN_REGRESSION's read deduplicates the whole raw Query Store slice every pass; keep the latest snapshot per interval as it is written #3953's v1 ruling named Checkpoint fsync on the busy store is dirtied-file count, not IOPS and not inventory: provisioning would buy nothing, checkpoint_timeout is the near-term lever, and the durable question is which aggregates need an hourly refresh at all #3892's write amplification as a first-class input, and this is that load, paid on every pass.
- Variance. A 58 s worst case, beside the 15 s mean, fits the other seats' reports of heavy reads getting killed while the store is busy.
What I did NOT measure:
- The per-server split.
pg_stat_statementsaggregates across servers, so "the busy server" specifically isn't isolated. analyze_server's own time composition. I didn't run it, because unanchored runs persist findings.
I'm not proposing
work_memas a mitigation: the statement's own comment records 512 MB making it slower (25.6 s → 59.3 s). Not reopening; that's Erik's call.Reopened on this issue's own shelving condition. The 2026-09-25 field data above shows
PlanRegressionSqlis the owner store's single heaviest statement: 22.5% of its time and 85 GB of temp writes in about 5 hours of steady-state passes, on build 3.8.0-nightly.20260924.467. Ranked tier 3 (measured store cost). It gets a lane when a slot frees up.31 remaining items
- added 8 commits that reference this issue
on Sep 26, 2026 Claude posting for Erik Darling.
#4389 is merged (112effd). It makes two live tests independent of setup order and the wall clock:
- the literal-end straddle pin enables TimescaleDB itself, and seeds interval-stamped rows;
- the shared baseline cache live test is anchored within one UTC day.
Both had been failing intermittently across open PRs.
Summary
PgFactCollector.PlanRegressionSql(Darling'sPLAN_REGRESSIONfact) deduplicates the server's whole raw Query Store slice on every analysis pass. Production measured it at 7.1 s or more on a 43-server store (#3902). #3956 (for #3902) removed the second copy of that work, in the regressed-queries drill-down, but the fact's own read is still proportional to the raw slice. No SQL-only rewrite changes that. This issue proposes the storage change that does, with the numbers behind it.Why a rewrite can't fix it
The dedup keeps one row per Query Store interval: the latest snapshot, because snapshots are cumulative. That needs grouping at the interval grain. PostgreSQL can only do it by sorting the slice on the eight-column interval identity, because an "argmax per group" can't be expressed as a hashable standard aggregate.
Measured on a PG 18.4 / TSDB 2.28.1 rig seeded to #2827's production funnel (4.15M raw rows per server, 1.72M intervals, 81K plans, work_mem 31MB):
GROUP BY+ orderedarray_agg)MATERIALIZED; 19.7 s grouped on a hash keyDISTINCT ON, or the planner forced off the uncompressed chunks' index scansThe existing interval-grain CAGG (
query_store_stats_interval_hourly) can't serve the read:query_plan_hash,last_execution_time,is_forced_planandforce_failure_count. A CAGG can't be ALTERed to add them.Proposal: keep the latest snapshot per interval as it is written
Add a table, maintained by the Query Store writer, with one row per interval identity:
(server_id, database_name, query_id, plan_id, execution_type_desc, replica_role, runtime_stats_interval_id, first_execution_time). The unique index needsNULLS NOT DISTINCT, becausereplica_roleis NULL off an AG and must still collapse. Each row holds the latest snapshot's values.ON CONFLICT ... DO UPDATE ... WHERE (EXCLUDED.collection_time, EXCLUDED.execution_count) > (t.collection_time, t.execution_count). That is the read's own tie-break, and backdated backfill slices (Query Store phase 2: newest-first backfill worker for the history the live path never takes #2022) lose to live snapshots as they do today.collection_timefor the server. The detector then sees exactly the intervals the raw dedup sees, whenever the table's own purge runs.What it needs
NULLS NOT DISTINCT, PostgreSQL 15+, within the product's minimum of 17).ON CONFLICT DO UPDATErefuses to touch one row twice in a single statement.first_execution_time. A hypertable's partition column must be NOT NULL, andcollection_timecan't be the key because the upsert moves it. The read-time floor above keeps findings identical whenever the purge runs.--backfill-rollups. Decide whether that store class needs one.Design input: #3892's write amplification
#3892 measured the busy store's checkpoint fsync cost as driven by the count of files dirtied between checkpoints. Query Store ingestion is the reason that store dirties about 4x more than its sibling (689 write operations per second against 181 on the same inventory). This table adds an upsert to exactly that write path: another relation's heap and index pages dirtied every cycle, the open intervals rewritten in place each time they are re-collected. The design needs a measured answer to whether that is affordable before anything ships. The options include batching the upsert per collector cycle, keeping the table unindexed beyond its unique key, and choosing a partitioning that keeps the hot open intervals in one small relation.
Out of scope here
collection_timebound like Darling's [BUG] Darling: PLAN_REGRESSION analysis query full-scans/decompresses entire query_store_stats history every cycle (no collection_time bound) #2387 (its views union the parquet archive), which is worth measuring in the same pass.Related
GROUP BY+array_agg.