gethomepage stats query is 3x slower than the dashboard and spills to disk

[9technologygroup]

What is the installation method of your PatchMon server?

Any

What is the version of your PatchMon server?

main

Description

GetHomepageStats is about 3x slower than GetDashboardStats while computing nearly the same aggregates over the same table, and it spills to disk.

The dashboard query computes its distinct counts as GROUP BY subqueries, which stream off the covering partial indexes. The gethomepage query still uses COUNT(DISTINCT ...), which sorts the whole matching set. On a seeded 3000 host fleet (3.74M host_packages rows) that is ~277ms and two external sorts spilling 6.2MB and 9.2MB, against ~83ms for the dashboard.

Raising work_mem is not the fix. It only recovers the spill, leaves ~237ms on the table, and adds a transaction round trip to a frequently polled endpoint. Matching the dashboard's query shape removes the spill as a side effect.

Steps to reproduce

1. Seed a fleet large enough for host_packages to exceed a few million rows.
2. EXPLAIN (ANALYZE) the GetHomepageStats query.
3. Observe Sort Method: external sort with multi-megabyte spills, and several times the dashboard's runtime.

Expected behavior

The widget query should compute its distinct counts the same way the dashboard does, and should not spill.

Additional context

The two must keep returning identical numbers: making them disagree was #1026. This is a plan change only. COUNT(DISTINCT x) and GROUP BY x differ only on NULL handling, and both columns are NOT NULL.