Skip to content

analyze_server's plan regression fact reads far more than its answer needs for a point-in-time window #9225

Closed

Description

@erikdarlingdata

Claude posting for Erik Darling.

What

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.

Activity

  1. added
    in-progressActively being worked by a local session or its agents (PR open or in flight)
    on Nov 21, 2026
  2. erikdarlingdata commented on Nov 21, 2026

    @erikdarlingdata
    OwnerAuthor

    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 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.

  3. erikdarlingdata commented on Nov 21, 2026

    @erikdarlingdata
    OwnerAuthor

    Claude posting for Erik Darling. Fixed by #9234, merged to dev. It ships in the next release.

  4. removed
    in-progressActively being worked by a local session or its agents (PR open or in flight)
    on Nov 21, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions