[RFE] Optimize Group Status Timeline query performance

[Asp-irin]

Current Problem

The status timeline endpoint runs an inefficient query:

FROM time_series
LEFT JOIN filtered_status_history
  ON created_ts >= ts - interval '3 days'
  AND created_ts < ts

What happens:

• Loads 33,389 history rows
• Checks each row against 11 timeline buckets
• Keeps matches, discards the rest

The waste:

• Total comparisons: 11 × 33,389 = 367,279
• Rows discarded: 333,890 (91% wasted)
• Execution time: 64.3 ms

---

Note: As i don't have direct data access i generated 200k rows randomly using a query to get some relevant results. Maintainers are requested to run the queries against the DB may be staging or prod depending on the input data we have. If there is any test DB with good amount of data I am happy to work on it to share correct metrics!

---

Why This Matters

As history rows grow, the query does more unnecessary work.

For groups with lots of status updates, this slows down timeline reports.

The query loads all rows first, then filters. It should fetch only relevant rows per bucket instead.

---

The Solution

Instead of: Get all rows, then check them against buckets

Do this: For each bucket, get only rows that belong to it

Improved query:

FROM ranges AS r
LEFT JOIN LATERAL (
  SELECT status, version, instance_id
  FROM instance_status_history
  WHERE group_id = $1
    AND created_ts >= r.from_ts
    AND created_ts < r.ts
) AS ish ON true

Results:

• Execution time: 20.5 ms (3.1x faster)
• Comparisons: ~30-50K instead of 367K
• Minimal wasted work

---

Implementation Steps

Step 1: Add Index

CREATE INDEX ON instance_status_history (group_id, created_ts);

Step 2: Rewrite Query

Use LATERAL join to filter per-bucket instead of loading all rows at once.

---

Performance Comparison

Metric	Current	Improved
Execution time	64.3 ms	20.5 ms
Speed improvement	Baseline	3.1x faster
Rows checked	367K	30-50K
Wasted comparisons	333K (91%)	Minimal

---

Validation Needed

Before a PR is raised I would like maintainers to confirm this:

1. Run EXPLAIN (ANALYZE, BUFFERS) on both queries with real production data
2. Verify both queries return identical results
3. Test different time ranges (1h, 1d, 7d, 30d)
4. Check index size and write overhead on instance_status_history
5. Confirm performance improvement on live data

---

What Gets Changed

• New index on (group_id, created_ts)
• Query rewrite using LATERAL join
• Unit/integration tests for regression prevention

---

Summary

Move filtering from after-the-join to before-the-join by using LATERAL with targeted index scans per bucket instead of checking all rows against all buckets.

Proof of Work

Proposed-Query-Explain-Report
Existing-Query-Explain-Report

[dongsupark]

Transferred the issue from Flatcar to here

[Asp-irin]

Thanks for moving this -- missed it completely :)