get_query_store_top reads about 1 GB per call at its 24 h default, and about 30 s at 168 h, after the per-interval table and BRIN work #4952

Class: bug (performance). On a 43-server SQL Server store running v3.9.0, get_query_store_top costs 5-10 s and 0.5-1.1 GB of disk reads per call at its default hours_back = 24. At its widest window, 168 h, the main read alone takes about 28 s, against the mcp role's 60 s statement_timeout.

Most of the cost isn't the rows the window needs:

• the gate's legacy-row probe reads the server's whole raw Query Store slice just to answer "no";
• the table read walks every index entry the server has.

Census (the service's own read-latency histograms, get_read_latency hours=168 surface=mcp, about 4.5 h since the install)

The store's pg_stat_statements (mcp role) shows the same two statements every call:

• The table read (QueryStoreTopTableSql): 201 calls, mean 10.2 s, max 38.0 s. It read 22.4 M shared blocks from outside shared buffers, about 870 MB per call, at a 2.5 % hit ratio.
• Clause 6's legacy-row probe (QueryStoreIntervalWide.HasLegacyRowSql): 201 calls, mean 4.1 s, max 16.4 s. It read 6.8 M blocks, about 260 MB per call.

This one tool's calls held the store volume at its 250 MB/s throughput cap for about five minutes, and slowed an unrelated export about 5x.

Plans (EXPLAIN (ANALYZE, BUFFERS), the exact statements as bound, one run each)

What the plans do:

• Legacy probe:
  • It does a Bitmap Index Scan on each raw chunk's (server_id, collection_time) index, then a Bitmap Heap Scan.
  • Filter: (interval_start_time_utc IS NULL) then discards every row, 855 k at 24 h.
  • It is an EXISTS that finds nothing, so it has to fetch every heap page the server's window touches.
• Table read, 24 h:
  • It does a BitmapAnd of ix_query_store_interval_wide_collection_time_brin (all servers' 24 h) and ux_query_store_interval_wide (Index Cond: server_id = $1 AND first_execution_time >= $2 - 1560 min).
  • The ux side alone took 4.34 s and 32,787 index blocks, more than half the statement.
  • first_execution_time is the 7th key of that index (after database_name, runtime_stats_interval_id, plan_id, query_id, replica_role). The condition therefore cannot narrow the range: the scan reads every entry the server has and tests the bound on each one. It kept 845 k entries, out of about 2.1 M the server holds (estimated from the index's size, inferred).
  • The heap side is 56,328 lossy blocks for 407 k rows, then a 70 MB external sort.
• Table read, 168 h:
  • The planner switches to a plain Index Scan on ux_query_store_interval_wide, in index order, feeding an Incremental Sort. That is random heap access for 2.02 M rows: 24.0 s of the 28.2 s is shared-read I/O.
  • The sort spilled 84 MB.

What last week's tuning already changed on this path

• PLAN_REGRESSION's read deduplicates the whole raw Query Store slice every pass; keep the latest snapshot per interval as it is written #3953: ≥ 24 h windows read query_store_interval_wide instead of deduplicating raw query_store_stats.
• Per-server Query Store reads of the per-interval table bound first_execution_time so they fetch fewer rows #4861 (under Custom Views panels over a recent window scan raw hypertables with no bound; a Query Store panel spilled ~23 GB and tore collector writes #4605): the per-server table reads bound first_execution_time, so the ux index filters entries before the heap.
• Build a BRIN index on query_store_interval_wide (collection_time) in the background #4862: the BRIN index on collection_time, built in the background.
• The managed store sets random_page_cost = 1.1, tuned for SSD storage #4863: random_page_cost = 1.1 on the managed store.
• V154 (The per-interval Query Store tables' daily purge full-scans them: minutes per call on a fleet store, with no index on first_execution_time #4608): idx_query_store_interval_wide_first_exec (first_execution_time). It doesn't lead with server_id, so it doesn't serve this read.
• Before all of these (schema 141), the vendor review measured the web Queries tab's raw query_store_top at 2.7 s, with a store mean of 5.5 s. That is a different window and route, so it's context, not a like-for-like baseline.

Suspected cause

1. The legacy-row probe pays for a full raw read to return false (measured).
   • HasLegacyRowSql's remarks call it "a bounded, indexed (server_id, collection_time) read on the common path" that "costs nothing extra there".
   • On this store it is 25-40 % of a call's I/O: 440-565 MB to prove that no interval_start_time_utc IS NULL row exists.
   • It visits the same raw rows the raw path itself would read, so the table route's "saving" is spent before the table read starts (inferred: the raw path wasn't EXPLAINed here).
2. ux_query_store_interval_wide can't range-scan server_id + first_execution_time (measured): the 24 h index walk reads the server's whole index range.
3. The 168 h plan is an ordered index scan with random heap fetches (measured). It sits at half the mcp role's 60 s timeout warm. The census max, 54.8 s, is within 6 s of the timeout.
4. The heap rows themselves are the window's share (measured). fillfactor = 50 doubles the pages they occupy. That is by design (HOT updates).

Options (inferred, for whoever designs the fix; not measured)

• For the probe, either:
  • a partial index on query_store_stats (server_id, collection_time) WHERE interval_start_time_utc IS NULL. It would be empty on a field store, so it costs near-zero writes and turns the probe into an index probe; or
  • retiring the probe once raw retention has aged past every pre-tier-2 row (recorded once, not re-proven per call).
• For the index walk: a btree on (server_id, first_execution_time).
  • first_execution_time is part of the upsert's identity and isn't rewritten by ON CONFLICT ... DO UPDATE, so it shouldn't cost HOT updates the way a collection_time btree would. Verify with the same rolled-back HOT test Build a BRIN index on query_store_interval_wide (collection_time) in the background #4862 used.
  • It would also let the 168 h read take a bitmap heap scan instead of index-order heap fetches.
• The tool description doesn't tell the caller that a wide window costs tens of seconds.

Code:

• DarlingDataReader.GetQueryStoreTopWithReachAsync / TryGetQueryStoreTopFromTableAsync;
• QueryStoreIntervalWide.ResolveReadAsync (clause 6), HasLegacyRowSql, PlainTableFloorSql;
• V145's ux_query_store_interval_wide.