You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
analyze_server's plan regression fact reads far more than its answer needs for a point-in-time window #9225
Build 3.13.0-nightly.20261121.714. analyze_server with an as_of a few hours back takes many seconds on a busy store, both the first call and a repeat. Its longest fact statement is WITH plan_agg AS (PlanRegressionDailyAsOfSql, PgFactCollector.QueryPerf.cs ~571).
It returns about a hundred rows, but each call reads hundreds of megabytes cold:
plan_regression_daily through a bitmap heap scan on ix_plan_regression_daily_server_day, estimated at hundreds of thousands of rows for the as-of day range;
the newest partitions of query_store_interval_latest, through bitmap scans on the server and first-execution-time index, estimated at more than a hundred thousand rows each.
With a generic plan, the same statement prunes the partitions at run time and uses an index scan on the daily table, with a much smaller estimate. So the custom plan reads more than it needs.
Two smaller statements in the same call are also slow when cold: WITH local_rows (PgFindingStore.GetPriorOccurrencesSql) and WITH snaps (PgAnomalyDetector).
Part of #8570. #9218 covers the separate case of an as_of before retention.
Proposed
On a seeded store, find why the read is wide: the day range it reads, the columns it needs and no index covers, and why the custom plan does not prune the interval partitions.
Fix it so the statement reads only the rows its answer is built from, through an index that leads with the server and covers what it reads, or a statement shape that lets the partitions prune.
The answer stays the same rows in the same order.
Lite: check whether its facts run the same statement and need the same change.
Tests
Seeded store: the plan_agg plan reads the daily table and the interval partitions through the intended index, with the interval partitions outside the as-of range pruned. Assert plan nodes, not timings.
The facts for a window inside retention stay the same.
Diagnosis from a seeded store (17 days of intervals in day partitions, one busy server with 40,000 plans a day, built days through T-3, as_of at 22:00 the previous day). No fix pushed: the one safe saving needs a design call.
What the as_of statement reads. Days that were whole one skew margin before the as_of come from plan_regression_daily. Those rows are the answer, so a narrower range or a covering index does not shrink them (a covering index would copy most of the table). The live half reads query_store_interval_latest from a floor of one day below the first day not taken from totals.
Where it is wide. The floor day's partition is read in full (12,360 blocks of about 57,700 in the plan, 21 percent) and every row is thrown away by the watermark test, because a built day keeps only rows at or above its watermark. Only rows that cross midnight into the first unbuilt day survive, and no index finds them: last_execution_time is not indexed, and indexing it would break HOT updates on the open-interval refresh. The two later partitions are needed. A partition after the as_of is not excluded by the bound, because the bound is on collection_time, not the partition key. A first_execution_time upper bound would need a skew slack and would not prune anything for an as_of a few hours back.
What would fix it. Raise the floor to the first unbuilt day minus the server's longest interval length instead of a fixed day. The wide-table read already resolves that length (ResolveEdgeMarginAsync) with fallbacks. Measured with the floor at one hour below the first unbuilt day: the floor partition is pruned and blocks fall from 57,700 to 44,700.
snaps. On a store with the TimescaleDB extension the DISTINCT ... LIMIT 2 plans as a SkipScan and reads 2 index entries. Without the extension it walks the whole newest snapshot (120,001 entries on the test store). The slow cold read is cold buffers, no change made.
local_rows. Reads the saved window rows the answer is built from. Cold reads only.
Lite: not checked beyond noting it keeps its facts in DuckDB, which has no day-partitioned interval table.
Activity
erikdarlingdata commentedon Nov 21, 2026
Claude posting for Erik Darling.
Diagnosis from a seeded store (17 days of intervals in day partitions, one busy server with 40,000 plans a day, built days through T-3, as_of at 22:00 the previous day). No fix pushed: the one safe saving needs a design call.
What the as_of statement reads. Days that were whole one skew margin before the as_of come from
plan_regression_daily. Those rows are the answer, so a narrower range or a covering index does not shrink them (a covering index would copy most of the table). The live half readsquery_store_interval_latestfrom a floor of one day below the first day not taken from totals.Where it is wide. The floor day's partition is read in full (12,360 blocks of about 57,700 in the plan, 21 percent) and every row is thrown away by the watermark test, because a built day keeps only rows at or above its watermark. Only rows that cross midnight into the first unbuilt day survive, and no index finds them:
last_execution_timeis not indexed, and indexing it would break HOT updates on the open-interval refresh. The two later partitions are needed. A partition after the as_of is not excluded by the bound, because the bound is oncollection_time, not the partition key. Afirst_execution_timeupper bound would need a skew slack and would not prune anything for an as_of a few hours back.What would fix it. Raise the floor to the first unbuilt day minus the server's longest interval length instead of a fixed day. The wide-table read already resolves that length (
ResolveEdgeMarginAsync) with fallbacks. Measured with the floor at one hour below the first unbuilt day: the floor partition is pruned and blocks fall from 57,700 to 44,700.snaps. On a store with the TimescaleDB extension theDISTINCT ... LIMIT 2plans as a SkipScan and reads 2 index entries. Without the extension it walks the whole newest snapshot (120,001 entries on the test store). The slow cold read is cold buffers, no change made.local_rows. Reads the saved window rows the answer is built from. Cold reads only.Lite: not checked beyond noting it keeps its facts in DuckDB, which has no day-partitioned interval table.
Bind-check the live floor through a test seam, one clock anchor, keep…
Merge pull request #9234 from erikdarlingdata/fix/9225-plan-agg-floor
erikdarlingdata commentedon Nov 21, 2026
Claude posting for Erik Darling. Fixed by #9234, merged to dev. It ships in the next release.