Skip to content

Commit a59a84b

Browse files
cstyancstyan
authored
perf: optimize GetTemplateAppInsightsByTemplate by pre-filtering on start/end times (#20669)
In this PR we're optimizing the `GetTemplateAppInsightsByTemplate` query by pre-filtering out apps which do not have an active session during the start/end time window. --------- Signed-off-by: Callum Styan <callumstyan@gmail.com>
1 parent 6abb889 commit a59a84b

File tree

2 files changed

+96
-36
lines changed

2 files changed

+96
-36
lines changed

‎coderd/database/queries.sql.go‎

Lines changed: 48 additions & 18 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/insights.sql‎

Lines changed: 48 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -350,52 +350,67 @@ GROUP BY
350350
-- GetTemplateAppInsightsByTemplate is used for Prometheus metrics. Keep
351351
-- in sync with GetTemplateAppInsights and UpsertTemplateUsageStats.
352352
WITH
353+
filtered_stats AS (
354+
SELECT
355+
was.workspace_id,
356+
was.user_id,
357+
was.agent_id,
358+
was.access_method,
359+
was.slug_or_port,
360+
was.session_started_at,
361+
was.session_ended_at
362+
FROM
363+
workspace_app_stats AS was
364+
WHERE
365+
was.session_ended_at >= @start_time::timestamptz
366+
AND was.session_started_at < @end_time::timestamptz
367+
),
353368
-- This CTE is used to explode app usage into minute buckets, then
354369
-- flatten the users app usage within the template so that usage in
355370
-- multiple workspaces under one template is only counted once for
356371
-- every minute.
357372
app_insights AS (
358373
SELECT
359374
w.template_id,
360-
was.user_id,
375+
fs.user_id,
361376
-- Both app stats and agent stats track web terminal usage, but
362377
-- by different means. The app stats value should be more
363378
-- accurate so we don't want to discard it just yet.
364379
CASE
365-
WHEN was.access_method = 'terminal'
380+
WHEN fs.access_method = 'terminal'
366381
THEN '[terminal]' -- Unique name, app names can't contain brackets.
367-
ELSE was.slug_or_port
382+
ELSE fs.slug_or_port
368383
END::text AS app_name,
369384
COALESCE(wa.display_name, '') AS display_name,
370385
(wa.slug IS NOT NULL)::boolean AS is_app,
371386
COUNT(DISTINCT s.minute_bucket) AS app_minutes
372387
FROM
373-
workspace_app_stats AS was
388+
filtered_stats AS fs
374389
JOIN
375390
workspaces AS w
376391
ON
377-
w.id = was.workspace_id
392+
w.id = fs.workspace_id
378393
-- We do a left join here because we want to include user IDs that have used
379394
-- e.g. ports when counting active users.
380395
LEFT JOIN
381396
workspace_apps wa
382397
ON
383-
wa.agent_id = was.agent_id
384-
AND wa.slug = was.slug_or_port
398+
wa.agent_id = fs.agent_id
399+
AND wa.slug = fs.slug_or_port
385400
-- Generate a series of minute buckets for each session for computing the
386401
-- mintes/bucket.
387402
CROSS JOIN
388403
generate_series(
389-
date_trunc('minute', was.session_started_at),
404+
date_trunc('minute', fs.session_started_at),
390405
-- Subtract 1 μs to avoid creating an extra series.
391-
date_trunc('minute', was.session_ended_at - '1 microsecond'::interval),
406+
date_trunc('minute', fs.session_ended_at - '1 microsecond'::interval),
392407
'1 minute'::interval
393408
) AS s(minute_bucket)
394409
WHERE
395410
s.minute_bucket >= @start_time::timestamptz
396411
AND s.minute_bucket < @end_time::timestamptz
397412
GROUP BY
398-
w.template_id, was.user_id, was.access_method, was.slug_or_port, wa.display_name, wa.slug
413+
w.template_id, fs.user_id, fs.access_method, fs.slug_or_port, wa.display_name, wa.slug
399414
)
400415

401416
SELECT
@@ -480,37 +495,52 @@ WITH
480495
FROM
481496
template_usage_stats
482497
),
498+
filtered_app_stats AS (
499+
SELECT
500+
was.workspace_id,
501+
was.user_id,
502+
was.agent_id,
503+
was.access_method,
504+
was.slug_or_port,
505+
was.session_started_at,
506+
was.session_ended_at
507+
FROM
508+
workspace_app_stats AS was
509+
WHERE
510+
was.session_ended_at >= (SELECT t FROM latest_start)
511+
AND was.session_started_at < NOW()
512+
),
483513
workspace_app_stat_buckets AS (
484514
SELECT
485515
-- Truncate the minute to the nearest half hour, this is the bucket size
486516
-- for the data.
487517
date_trunc('hour', s.minute_bucket) + trunc(date_part('minute', s.minute_bucket) / 30) * 30 * '1 minute'::interval AS time_bucket,
488518
w.template_id,
489-
was.user_id,
519+
fas.user_id,
490520
-- Both app stats and agent stats track web terminal usage, but
491521
-- by different means. The app stats value should be more
492522
-- accurate so we don't want to discard it just yet.
493523
CASE
494-
WHEN was.access_method = 'terminal'
524+
WHEN fas.access_method = 'terminal'
495525
THEN '[terminal]' -- Unique name, app names can't contain brackets.
496-
ELSE was.slug_or_port
526+
ELSE fas.slug_or_port
497527
END AS app_name,
498528
COUNT(DISTINCT s.minute_bucket) AS app_minutes,
499529
-- Store each unique minute bucket for later merge between datasets.
500530
array_agg(DISTINCT s.minute_bucket) AS minute_buckets
501531
FROM
502-
workspace_app_stats AS was
532+
filtered_app_stats AS fas
503533
JOIN
504534
workspaces AS w
505535
ON
506-
w.id = was.workspace_id
536+
w.id = fas.workspace_id
507537
-- Generate a series of minute buckets for each session for computing the
508538
-- mintes/bucket.
509539
CROSS JOIN
510540
generate_series(
511-
date_trunc('minute', was.session_started_at),
541+
date_trunc('minute', fas.session_started_at),
512542
-- Subtract 1 μs to avoid creating an extra series.
513-
date_trunc('minute', was.session_ended_at - '1 microsecond'::interval),
543+
date_trunc('minute', fas.session_ended_at - '1 microsecond'::interval),
514544
'1 minute'::interval
515545
) AS s(minute_bucket)
516546
WHERE
@@ -519,7 +549,7 @@ WITH
519549
s.minute_bucket >= (SELECT t FROM latest_start)
520550
AND s.minute_bucket < NOW()
521551
GROUP BY
522-
time_bucket, w.template_id, was.user_id, was.access_method, was.slug_or_port
552+
time_bucket, w.template_id, fas.user_id, fas.access_method, fas.slug_or_port
523553
),
524554
agent_stats_buckets AS (
525555
SELECT

0 commit comments

Comments
 (0)