Anomaly detector's "latest two object-stats snapshots" read has no usable index: ~350 MB read per server per pass (15.9 M blocks in 5 h on one store) #4196

Problem

PgAnomalyDetector.ObjectGrowthSql and ObjectContentionSql both start with:

WITH snaps AS (SELECT DISTINCT collection_time FROM v_index_object_stats
               WHERE server_id = $1 ORDER BY collection_time DESC LIMIT 2), …

index_object_stats has no index that leads with (server_id, collection_time). Its indexes are (server_id, database_id, object_id, index_id, collection_time DESC), (server_id, database_name, object_id, index_id, collection_time) and (collection_time DESC). So the planner walks the newest chunk's collection_time DESC index and filters out every other server's rows until it finds this server's newest snapshot. That snapshot is daily, so it's often not in the newest chunk at all. The cost is proportional to the whole fleet's rows in the newest chunk, paid once per server per analysis pass, twice (once per query).

Measured

• Store A (43 SQL Servers), build 3.8.0-nightly.20260924.467, 2026-09-25, EXPLAIN (ANALYZE, BUFFERS) of the snaps CTE for one server, read-only:

  • 568 ms, Buffers: shared hit=4601 read=44591 (about 350 MB read);
  • Custom Scan (SkipScan) over _…_index_object_stats_collection_time_idx with Filter: (server_id = …), Rows Removed by Filter: 499660, returning 1 row;
  • the older chunk then answered from its compressed index in 0.4 ms.

• get_store_query_stats (owner role, cumulative since the service restart ~5 h earlier):

  • store A, ObjectGrowthSql: 344 calls, mean 1.14 s, max 17.9 s, 15.9 M shared blocks read;
  • store B (42 SQL Servers): the two statements together 756 calls, mean 0.48 s / 0.27 s, 10.1 M + 1.1 M blocks read.

  That puts it among the largest disk readers on both stores, for a result of two timestamps.

Where

• Darling/PerformanceMonitor.Darling.Analysis/PgAnomalyDetector.cs ~:380–410, ObjectGrowthSql and ObjectContentionSql. Their cur/prv CTEs also filter collection_time = (SELECT t FROM …) per server, which the existing idx_index_object_stats_latest can serve once the two times are known.
• Darling/PerformanceMonitor.Darling.Storage/PgMigrations.cs ~:5869–5891 and PgSchemaGenerator.cs ~:112: the table's indexes.
• Lite/Analysis/AnomalyDetector.cs ~:165 has the same CTE over DuckDB. It's a different engine, but worth checking on a large Lite database.

Fix shape

Either of these:

1. Index: CREATE INDEX … ON collect.index_object_stats (server_id, collection_time DESC). The CTE becomes a two-step index probe per chunk, and TimescaleDB's SkipScan applies to it directly. On compressed chunks, check that the server_id segment-by keeps it cheap. This needs a migration rung (serialize with any rung in flight) plus the ladder generator entry.
2. No schema change: read the two newest snapshot times from what the writer already knows. The object-stats collector writes one snapshot per server per day, so it can record (server_id, latest, prior) in collector_state at write time, and the detector reads those.

Pin: a live test on a seeded multi-server store asserting the snaps read doesn't scan other servers' rows (via EXPLAIN buffers or a rows-removed bound).