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

Claude posting for Erik Darling.

What happens

The daily purge of collect.query_store_interval_latest (V143) and collect.query_store_interval_wide (V145) costs minutes per statement on a fleet store, even while it has nothing to delete. On one production monitoring store (pg_stat_statements since 2026-09-24 ~22:00Z):

Both tables are still inside their horizons (15 and 9 days), so no row is old enough yet. The no-op purge already costs up to 2.7 minutes a call, and the cost grows with the table. Once real deletes start, TimeSlicedDeleteSql repeats it for every slice.

Why

• The purge shape. DarlingRetention.PurgeAsync purges both with TimeSlicedDeleteSql(table, "first_execution_time") (Darling/PerformanceMonitor.Darling.Service/DarlingRetention.cs ~738–770):

  DELETE FROM t WHERE first_execution_time < $1
    AND first_execution_time >= (SELECT min(first_execution_time) FROM t WHERE first_execution_time < $1)
    AND first_execution_time <  (SELECT min(first_execution_time) FROM t WHERE first_execution_time < $1) + INTERVAL '1 days'

• The missing index. Neither table has an index leading on first_execution_time. The only index on each is the unique identity key, where it comes last (PgMigrations.cs ~2129–2139 and ~2306–2317). Both are plain tables, not hypertables, so there's no chunk exclusion either.

• So each statement is a full scan, up to three with the two min() subqueries. The same store's stats show 9 seq scans reading 60 M rows on query_store_interval_latest.

• The read gate has the same problem. QueryStoreIntervalWide's MIN(first_execution_time) floor has the same shape (DarlingRetention.cs ~762, "The table floor the read gate checks").

Fix direction

One of the two, chosen by a rig measurement at a fleet-sized row count, and stated on the PR:

1. An index on first_execution_time for both tables (a migration rung). That makes the purge's slices and the read gate's floor index range scans.
   • first_execution_time never changes for a row, so the tables' upsert keeps its HOT updates.
   • The rung must be the only one in flight.
2. The The Query Store liveness touch costs ~13% of the Query Store–ingesting store's WAL (~33 GB/day) at its 6-hour guard: every touch is a non-HOT update #4250 shape (DarlingRetention.UnorderedRowCappedDeleteSql): no subquery and one capped pass per batch, with no index needed. The terminal "nothing left" batch is still a full scan, so it's cheaper but not free; the read gate's floor still needs its own fix.

Pins:

• the purge SQL each table gets;
• a live proof that the old rows are deleted and the new ones kept;
• EXPLAIN showing no seq scan (option 1), or the pass count (option 2);
• StorageCommandTimeoutTests for any new statement.

Related: #3953, #4382, #4250.