The forced-plan-failures read outgrew its measured deadline alone: site 2 of the alerting-read degradation, contention-rider theory falsified #3573
Description
Summary
#3496 deferred its second read site with a stated reopen condition, and the condition has now been met with the cleanest possible evidence: since the v3.8.0 install (01:54Z), the biggest store's alerting-side read failures number exactly three — all on the forced-plan-failures read (newest at 10,322ms against the 10-second deadline) — while the fixed recent-runs site sits at zero across the same 8.6 hours. The #3496 fix holds and its acceptance stands; the theory that site 2 merely rode site 1's contention is falsified: the contention source went silent and site 2 kept failing alone.
What is known without a new measurement
The read (ForcePlanFailuresSql) was measured at 1,744.9ms cold when its 10-second deadline was derived — the deadline's own documentation carries the number — and it now exceeds 10.3s: a 6x drift as the store grew. Unlike #3496's site, this read is already time-windowed (collection_time > $2, ~2 hours) and server-scoped, so naive chunk-append over the whole retention is not the mechanism. The static suspects, from the statement's own shape:
- The grouping is row-per-collection:
GROUP BY database_name, query_id, plan_id, collection_time— withcollection_timein the key, the aggregate touches every raw row the window holds for the server, and a busy server's two hours ofquery_store_statsrows grew with query volume since the measurement day. - The ranking sorts it all:
ROW_NUMBER() OVER (PARTITION BY database_name, query_id, plan_id ORDER BY collection_time DESC)needs the full grouped set sorted per key to find rn=1 and rn=2 — the newest two collections per plan — so the statement's real question ("did the failure counter move between the last two sightings?") is answered by materializing and sorting everything in the window. - Cold-tail shape matches: warm it is presumably fine (it runs every 30-second pass and self-warms); the failures are excursions, exactly the The alerting layer's recent-runs read sorts a server's whole retention history to return ten rows — order by the column the chunks can #3496 presentation one seam over.
The discriminator, before any code
The #3496 methodology transfers directly: EXPLAIN (ANALYZE, BUFFERS) on the exact shipped statement against the production store, busiest server id, real 2-hour window — rows touched, sort strategy, chunk count, buffers. The statement lacks #3496's lucky in-statement control, so the comparison baseline is the measurement-day figure (1,744.9ms / ~6.0GB scanned) against today's. Payload staged; runs on the next SSO window. No fix shape is committed until the plan speaks — the candidate directions (a lateral newest-2-per-key read that stops early per plan; a tighter window; pre-aggregation at collection time) pull in different directions and the plan decides.
Evidence chain: the #3496 issue's deferral clause and its PR's watched-not-co-fixed note; the monitoring seat's tick series since the v3.8.0 install (3 failures, all site-2, site-1 zero); the deadline derivation doc. Detection instrumentation is already adjusted: site-2 failures log-with-count without re-raising, re-escalation on rate class-jump or a third site.
Activity
erikdarlingdata commentedon Sep 18, 2026
Discriminator ran (live EXPLAIN ANALYZE, production store, busiest server, real 2-hour window) — mechanism found, and it is neither of the static suspects
The plan absolves the aggregation shape and convicts the index:
query_store_statscarries a collection_time-only index, so every server's forced-plan read walks the entire fleet's two-hour slice — ~729K rows — and discards 95% of it to keep one server's 37,878. The read runs per server, every 30-second alert pass, 43 servers deep: the whole fleet's window, re-read and re-filtered, 43 times per pass cycle. Warm it costs 422ms (54,664 buffer hits — the fleet slice lives in cache because this statement itself keeps it there); the 10-second excursions are the cold tail whenever cache pressure evicts a slice that is now ~4x bigger than measurement day (the table has grown 6GB → 23GB). Chunk exclusion is fine (one chunk in the window at this hour; boundary hours briefly make it two, a secondary contributor). The GROUP BY/ROW_NUMBER shape I suspected statically is downstream of the scan and cheap at the real row count — quicksorts in single-digit MB.Why this is the same disease as #3496 wearing a different coat
collection_log's fix was ordering the read by the column the chunks could use. Here the window is already chunk-aligned — what's missing is the server dimension in the index: the exact(server_id, collection_time)composite thatcollection_logalready carries and that this table never got. Every read is paying a 19x fleet-width tax (729K walked / 37.8K kept) that grows with both fleet size and query volume.Fix direction, pending the owner's word before any code — this one touches a 23GB production hypertable
The candidate is a
(server_id, collection_time)index onquery_store_stats(a V126 rung). Unlike every prior rung this one has real build cost on a 23GB table across ~its chunk count, and the deployment questions deserve a ruling rather than a lane's judgment call: build strategy on existing chunks (Timescale builds per-chunk; the store stays live), whether compressed chunks are excluded from backfill (queries only touch the recent uncompressed window, so indexing only uncompressed/new chunks may capture all the value at a fraction of the cost), and lock/IO behavior on the busiest store's disk during the build. Alternatives if the index is unpalatable: reshape the read to two chunk-ordered probes (newest-2 collections per key, LIMIT-driven), or accept the tail and raise the deadline with the margin re-derived — both weaker than fixing the access path.Series context: 3 failures in 8.6h post-install, all this read, site 1 at zero throughout. Degradation-not-outage stands.
The forced-plan-failures alert read gets the covering index the plann…
erikdarlingdata commentedon Sep 18, 2026
Closed by #3586 (merged to dev).
erikdarlingdata commentedon Sep 18, 2026
Closing record — the fix that shipped (#3586, merged) differs from the fix this issue proposed, and the difference is the lesson. The
(server_id, collection_time DESC)composite this issue asked for has existed since V1 (idx_query_store_stats_time); a catalog read of the production hypertable and of the exact chunk in the failing plan confirmed it present. The planner refused it:server_id's physical correlation on the chunk is 0.022 (servers interleave across every collection pass) againstcollection_time's 0.99999, so the cost model priced the composite's heap fetches as random I/O and chose to stream the fleet's whole two-hour slice through the time index instead. A session-onlySET random_page_cost = 1.1— planning only, no data touched — flipped the identical statement onto the existing composite at 5,063 buffers against 57,307: the index was never the missing piece, the heap fetches were. What shipped is a covering index over exactly the read's columns (INCLUDEthe seven it projects), so the plan becomes an Index Only Scan with zero heap fetches — 50 buffers against 1,514 on the rig — applied at startup viaPgTableTuning.Statements, the repo's home for results-invariant, EXPLAIN-backed perf indexes, which is why no migration rung was consumed. Footprint is the stated tradeoff: ~86 bytes/row (INCLUDEdisables deduplication) → ~0.7–0.9 GB per live day-chunk on the largest store, self-limiting because compressed chunks carry 8 KB index shells. The deadline does not move. Eleven sibling reads with the same predicate shape but wider column lists are named in the PR as not covered; they mostly hit compressed chunks wheresegmentby server_idalready serves them.