Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit0da29d7

Browse files
authored
fix(coderd/database): improve query perf ofGetTemplateAppInsights (#12767)
Refs#12122
1 parenta74ef40 commit0da29d7

File tree

4 files changed

+266
-214
lines changed

4 files changed

+266
-214
lines changed

‎coderd/database/dbmem/dbmem.go

Lines changed: 95 additions & 72 deletions
Original file line numberDiff line numberDiff line change
@@ -2986,68 +2986,93 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
29862986
*/
29872987

29882988
/*
2989-
app_insights AS (
2989+
-- Create a list of all unique apps by template, this is used to
2990+
-- filter out irrelevant template usage stats.
2991+
apps AS (
2992+
SELECT DISTINCT ON (ws.template_id, app.slug)
2993+
ws.template_id,
2994+
app.slug,
2995+
app.display_name,
2996+
app.icon
2997+
FROM
2998+
workspaces ws
2999+
JOIN
3000+
workspace_builds AS build
3001+
ON
3002+
build.workspace_id = ws.id
3003+
JOIN
3004+
workspace_resources AS resource
3005+
ON
3006+
resource.job_id = build.job_id
3007+
JOIN
3008+
workspace_agents AS agent
3009+
ON
3010+
agent.resource_id = resource.id
3011+
JOIN
3012+
workspace_apps AS app
3013+
ON
3014+
app.agent_id = agent.id
3015+
WHERE
3016+
-- Partial query parameter filter.
3017+
CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN ws.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
3018+
ORDER BY
3019+
ws.template_id, app.slug, app.created_at DESC
3020+
),
3021+
-- Join apps and template usage stats to filter out irrelevant rows.
3022+
-- Note that this way of joining will eliminate all data-points that
3023+
-- aren't for "real" apps. That means ports are ignored (even though
3024+
-- they're part of the dataset), as well as are "[terminal]" entries
3025+
-- which are alternate datapoints for reconnecting pty usage.
3026+
template_usage_stats_with_apps AS (
29903027
SELECT
3028+
tus.start_time,
3029+
tus.template_id,
29913030
tus.user_id,
2992-
array_agg(DISTINCT tus.template_id)::uuid[] AS template_ids,
2993-
app_usage.key::text AS app_name,
2994-
COALESCE(wa.display_name, '') AS display_name,
2995-
COALESCE(wa.icon, '') AS icon,
2996-
(wa.slug IS NOT NULL)::boolean AS is_app,
2997-
LEAST(SUM(app_usage.value::int), 30) AS app_usage_mins
3031+
apps.slug,
3032+
apps.display_name,
3033+
apps.icon,
3034+
tus.app_usage_mins
29983035
FROM
2999-
template_usage_stats AS tus, jsonb_each(app_usage_mins) AS app_usage
3000-
LEFT JOIN LATERAL (
3001-
-- Fetch the latest app info for each app based on slug and template.
3002-
SELECT
3003-
app.display_name,
3004-
app.icon,
3005-
app.slug
3006-
FROM
3007-
workspace_apps AS app
3008-
JOIN
3009-
workspace_agents AS agent
3010-
ON
3011-
agent.id = app.agent_id
3012-
JOIN
3013-
workspace_resources AS resource
3014-
ON
3015-
resource.id = agent.resource_id
3016-
JOIN
3017-
workspace_builds AS build
3018-
ON
3019-
build.job_id = resource.job_id
3020-
JOIN
3021-
workspaces AS workspace
3022-
ON
3023-
workspace.id = build.workspace_id
3024-
WHERE
3025-
-- Requires lateral join.
3026-
app.slug = app_usage.key
3027-
AND workspace.owner_id = tus.user_id
3028-
AND workspace.template_id = tus.template_id
3029-
ORDER BY
3030-
app.created_at DESC
3031-
LIMIT 1
3032-
) AS wa
3036+
apps
3037+
JOIN
3038+
template_usage_stats AS tus
30333039
ON
3034-
true
3035-
WHERE
3040+
-- Query parameter filter.
30363041
tus.start_time >= @start_time::timestamptz
30373042
AND tus.end_time <= @end_time::timestamptz
30383043
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN tus.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
3044+
-- Primary join condition.
3045+
AND tus.template_id = apps.template_id
3046+
AND apps.slug IN (SELECT jsonb_object_keys(tus.app_usage_mins))
3047+
),
3048+
-- Group the app insights by interval, user and unique app. This
3049+
-- allows us to deduplicate a user using the same app across
3050+
-- multiple templates.
3051+
app_insights AS (
3052+
SELECT
3053+
user_id,
3054+
slug,
3055+
display_name,
3056+
icon,
3057+
-- See motivation in GetTemplateInsights for LEAST(SUM(n), 30).
3058+
LEAST(SUM(app_usage.value::smallint), 30) AS usage_mins
3059+
FROM
3060+
template_usage_stats_with_apps, jsonb_each(app_usage_mins) AS app_usage
3061+
WHERE
3062+
app_usage.key = slug
30393063
GROUP BY
3040-
tus.start_time,tus.user_id,app_usage.key::text, wa.display_name,wa.icon, wa.slug
3064+
start_time, user_id,slug,display_name, icon
30413065
),
30423066
*/
30433067

3068+
// Due to query optimizations, this logic is somewhat inverted from
3069+
// the above query.
30443070
typeappInsightsGroupBystruct {
30453071
StartTime time.Time
30463072
UserID uuid.UUID
3047-
AppNamestring
3073+
Slugstring
30483074
DisplayNamestring
30493075
Iconstring
3050-
IsAppbool
30513076
}
30523077
typeappInsightsRowstruct {
30533078
appInsightsGroupBy
@@ -3066,18 +3091,20 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
30663091
}
30673092

30683093
// json_each
3069-
forappName,appUsage:=rangestat.AppUsageMins {
3070-
// LEFT JOIN LATERAL
3071-
app,_:=q.getLatestWorkspaceAppByTemplateIDUserIDSlugNoLock(ctx,stat.TemplateID,stat.UserID,appName)
3094+
forslug,appUsage:=rangestat.AppUsageMins {
3095+
// FROM apps JOIN template_usage_stats
3096+
app,_:=q.getLatestWorkspaceAppByTemplateIDUserIDSlugNoLock(ctx,stat.TemplateID,stat.UserID,slug)
3097+
ifapp.Slug=="" {
3098+
continue
3099+
}
30723100

30733101
// SELECT
30743102
key:=appInsightsGroupBy{
30753103
StartTime:stat.StartTime,
30763104
UserID:stat.UserID,
3077-
AppName:appName,
3105+
Slug:slug,
30783106
DisplayName:app.DisplayName,
30793107
Icon:app.Icon,
3080-
IsApp:app.Slug!="",
30813108
}
30823109
row,ok:=appInsightRows[key]
30833110
if!ok {
@@ -3092,25 +3119,26 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
30923119
}
30933120

30943121
/*
3122+
-- Even though we allow identical apps to be aggregated across
3123+
-- templates, we still want to be able to report which templates
3124+
-- the data comes from.
30953125
templates AS (
30963126
SELECT
3097-
app_name,
3127+
slug,
30983128
display_name,
30993129
icon,
3100-
is_app,
31013130
array_agg(DISTINCT template_id)::uuid[] AS template_ids
31023131
FROM
3103-
app_insights, unnest(template_ids) AS template_id
3132+
template_usage_stats_with_apps
31043133
GROUP BY
3105-
app_name, display_name, icon, is_app
3134+
slug, display_name, icon
31063135
)
31073136
*/
31083137

31093138
typeappGroupBystruct {
3110-
AppNamestring
3139+
Slugstring
31113140
DisplayNamestring
31123141
Iconstring
3113-
IsAppbool
31143142
}
31153143
typetemplateRowstruct {
31163144
appGroupBy
@@ -3120,10 +3148,9 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
31203148
templateRows:=make(map[appGroupBy]templateRow)
31213149
for_,aiRow:=rangeappInsightRows {
31223150
key:=appGroupBy{
3123-
AppName:aiRow.AppName,
3151+
Slug:aiRow.Slug,
31243152
DisplayName:aiRow.DisplayName,
31253153
Icon:aiRow.Icon,
3126-
IsApp:aiRow.IsApp,
31273154
}
31283155
row,ok:=templateRows[key]
31293156
if!ok {
@@ -3138,23 +3165,21 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
31383165
/*
31393166
SELECT
31403167
t.template_ids,
3141-
array_agg(DISTINCT ai.user_id)::uuid[] ASactive_user_ids,
3142-
ai.app_name AS slug_or_port,
3168+
COUNT(DISTINCT ai.user_id) ASactive_users,
3169+
ai.slug,
31433170
ai.display_name,
31443171
ai.icon,
3145-
ai.is_app,
3146-
(SUM(ai.app_usage_mins) * 60)::bigint AS usage_seconds
3172+
(SUM(ai.usage_mins) * 60)::bigint AS usage_seconds
31473173
FROM
31483174
app_insights AS ai
31493175
JOIN
31503176
templates AS t
31513177
ON
3152-
ai.app_name = t.app_name
3153-
AND ai.display_name = t.display_name
3154-
AND ai.icon = t.icon
3155-
AND ai.is_app = t.is_app
3178+
t.slug = ai.slug
3179+
AND t.display_name = ai.display_name
3180+
AND t.icon = ai.icon
31563181
GROUP BY
3157-
t.template_ids, ai.app_name, ai.display_name, ai.icon, ai.is_app;
3182+
t.template_ids, ai.slug, ai.display_name, ai.icon;
31583183
*/
31593184

31603185
typetemplateAppInsightsRowstruct {
@@ -3165,10 +3190,9 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
31653190
groupedRows:=make(map[appGroupBy]templateAppInsightsRow)
31663191
for_,aiRow:=rangeappInsightRows {
31673192
key:=appGroupBy{
3168-
AppName:aiRow.AppName,
3193+
Slug:aiRow.Slug,
31693194
DisplayName:aiRow.DisplayName,
31703195
Icon:aiRow.Icon,
3171-
IsApp:aiRow.IsApp,
31723196
}
31733197
row:=groupedRows[key]
31743198
row.ActiveUserIDs=append(row.ActiveUserIDs,aiRow.UserID)
@@ -3181,10 +3205,9 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
31813205
rows=append(rows, database.GetTemplateAppInsightsRow{
31823206
TemplateIDs:templateRows[key].TemplateIDs,
31833207
ActiveUsers:int64(len(uniqueSortedUUIDs(gr.ActiveUserIDs))),
3184-
SlugOrPort:key.AppName,
3208+
Slug:key.Slug,
31853209
DisplayName:key.DisplayName,
31863210
Icon:key.Icon,
3187-
IsApp:key.IsApp,
31883211
UsageSeconds:gr.UsageSeconds,
31893212
})
31903213
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp