Repository navigation
Conversation
|
All three queries return the same rows as before on PG13 and PG17, including the edge cases. The related tests pass. Can't reproduce: the
The old query ran both aggregations in parallel workers. The single pass adds a single-threaded re-sort of the whole window. What data and Postgres version did you measure on? The other two gains reproduce: Suggestions (both return the same rows):
|
… pass - GetUserActivityInsights uses one grouped pass with GROUPING SETS instead of two aggregations and a sort over rows carrying template lists. - GetWorkspaceAgentStats computes totals and each agent's latest session counts from one scan of the window. - GetWorkspaceAgentUsageStats sorts only the needed columns and no longer groups on the session counts JSON. Generated by Coder Agents.
…ns after grouping Review on #31628 found the single pass GetWorkspaceAgentStats slower on realistic data. With 8k agents and about 150 stat rows each per hour it took 1264 ms vs 878 ms on PG17 and 1644 ms vs 1136 ms on PG13, so restore the previous query. The earlier gain only showed on sparse seed data with about 3 rows per agent. GetWorkspaceAgentUsageStats now aggregates first and joins latest_sessions per group, which drops the array_agg workaround. Output is identical; it is about 10% faster than the previous revision on PG13 and PG17. Generated by Coder Agents.
0b091f2 to
19bcdd5
Compare
cstyan
left a comment
There was a problem hiding this comment.
LGTM, the GROUPING bit in the first query was a bit hard to understand, potentially a different name for is_bucket_row and/or a comment on what the grouping/grouping sets portions produce would be useful. However I don't have any decent concrete suggestion for those, so not blocking.
Two insights and telemetry queries now do less work:
GetUserActivityInsightsuses one grouped pass withGROUPING SETSinstead of two aggregations and a sort over rows carrying template lists.GetWorkspaceAgentUsageStatssorts only the needed columns, aggregates per agent group first, and then joins the one-row-per-agentlatest_sessions. It no longer groups on the session counts JSON.GetUserActivityInsights, all templates / one template (PG17)GetWorkspaceAgentUsageStats, 1 hour, 8k agents / 1.2M rows, PG17GetWorkspaceAgentStatsis unchanged. An earlier revision rewrote it as a single pass, but on realistic data (few agents, many rows each) it was slower than the existing query: 1264 vs 878 ms on PG17 and 1644 vs 1136 ms on PG13. It only looked faster on sparse seed data with about 3 rows per agent per hour, so it was reverted. Thanks @EhabY for catching this.Equivalence:
EXCEPTboth ways.GetWorkspaceAgentUsageStats: row count and md5 of the sorted output matched the original query on PG13 and PG17, for both data shapes above.Depends on #31627.
Benchmark setup
The table was loaded into scratch databases on PG13.23 and PG17.11, shifted to the current time. Each server was capped at 4 GB, with
shared_buffers=1GBandwork_mem4 MB (the default) and 16 MB. Timings areEXPLAIN (ANALYZE, TIMING OFF)medians of 3 to 6 warm runs.Two data shapes were used:
Generated by Coder Agents on behalf of @kylecarbs.