fix(coderd): optimize template app insights query for speed and decrease intervals (coder#9302)

mafredri · web-flow · commit af939d1e9460 · 2023-08-24T14:34:38.000Z
diff --git a/coderd/database/dbfake/dbfake.go b/coderd/database/dbfake/dbfake.go
@@ -2048,8 +2048,8 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
 			t = arg.StartTime
 		}
 		for t.Before(s.SessionEndedAt) && t.Before(arg.EndTime) {
-			appUsageIntervalsByUserAgentApp[key][t] = 300 // 5 minutes.
-			t = t.Add(5 * time.Minute)
+			appUsageIntervalsByUserAgentApp[key][t] = 60 // 1 minute.
+			t = t.Add(1 * time.Minute)
 		}
 	}
 
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/insights.sql b/coderd/database/queries/insights.sql
@@ -61,19 +61,10 @@ FROM agent_stats_by_interval_and_user;
 -- GetTemplateAppInsights returns the aggregate usage of each app in a given
 -- timeframe. The result can be filtered on template_ids, meaning only user data
 -- from workspaces based on those templates will be included.
-WITH ts AS (
-	SELECT
-		d::timestamptz AS from_,
-		(d::timestamptz + '5 minute'::interval) AS to_,
-		EXTRACT(epoch FROM '5 minute'::interval) AS seconds
-	FROM
-		-- Subtract 1 second from end_time to avoid including the next interval in the results.
-		generate_series(@start_time::timestamptz, (@end_time::timestamptz) - '1 second'::interval, '5 minute'::interval) d
-), app_stats_by_user_and_agent AS (
+WITH app_stats_by_user_and_agent AS (
 	SELECT
-		ts.from_,
-		ts.to_,
-		ts.seconds,
+		s.start_time,
+		60 as seconds,
 		w.template_id,
 		was.user_id,
 		was.agent_id,
@@ -82,12 +73,7 @@ WITH ts AS (
 		wa.display_name,
 		wa.icon,
 		(wa.slug IS NOT NULL)::boolean AS is_app
-	FROM ts
-	JOIN workspace_app_stats was ON (
-		(was.session_started_at >= ts.from_ AND was.session_started_at < ts.to_)
-		OR (was.session_ended_at > ts.from_ AND was.session_ended_at < ts.to_)
-		OR (was.session_started_at < ts.from_ AND was.session_ended_at >= ts.to_)
-	)
+	FROM workspace_app_stats was
 	JOIN workspaces w ON (
 		w.id = was.workspace_id
 		AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN w.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
@@ -98,7 +84,20 @@ WITH ts AS (
 		wa.agent_id = was.agent_id
 		AND wa.slug = was.slug_or_port
 	)
-	GROUP BY ts.from_, ts.to_, ts.seconds, w.template_id, was.user_id, was.agent_id, was.access_method, was.slug_or_port, wa.display_name, wa.icon, wa.slug
+	-- This table contains both 1 minute entries and >1 minute entries,
+	-- to calculate this with our uniqueness constraints, we generate series
+	-- for the longer intervals.
+	CROSS JOIN LATERAL generate_series(
+		date_trunc('minute', was.session_started_at),
+		-- Subtract 1 microsecond to avoid creating an extra series.
+		date_trunc('minute', was.session_ended_at - '1 microsecond'::interval),
+		'1 minute'::interval
+	) s(start_time)
+	WHERE
+		s.start_time >= @start_time::timestamptz
+		-- Subtract one minute because the series only contains the start time.
+		AND s.start_time < (@end_time::timestamptz) - '1 minute'::interval
+	GROUP BY s.start_time, w.template_id, was.user_id, was.agent_id, was.access_method, was.slug_or_port, wa.display_name, wa.icon, wa.slug
 )
 
 SELECT
diff --git a/coderd/insights_test.go b/coderd/insights_test.go
@@ -778,7 +778,19 @@ func TestTemplateInsights_Golden(t *testing.T) {
 						endedAt:   frozenWeekAgo.Add(time.Hour),
 						requests:  1,
 					},
-					{ // used an app on the last day, counts as active user, 12m -> 15m rounded.
+					{ // 30s of app usage -> 1m rounded.
+						app:       users[0].workspaces[0].apps[0],
+						startedAt: frozenWeekAgo.Add(2*time.Hour + 10*time.Second),
+						endedAt:   frozenWeekAgo.Add(2*time.Hour + 40*time.Second),
+						requests:  1,
+					},
+					{ // 1m30s of app usage -> 2m rounded (included in São Paulo).
+						app:       users[0].workspaces[0].apps[0],
+						startedAt: frozenWeekAgo.Add(3*time.Hour + 30*time.Second),
+						endedAt:   frozenWeekAgo.Add(3*time.Hour + 90*time.Second),
+						requests:  1,
+					},
+					{ // used an app on the last day, counts as active user, 12m.
 						app:       users[0].workspaces[0].apps[2],
 						startedAt: frozenWeekAgo.AddDate(0, 0, 6),
 						endedAt:   frozenWeekAgo.AddDate(0, 0, 6).Add(12 * time.Minute),
diff --git a/coderd/testdata/insights/multiple_users_and_workspaces_week_all_templates.json.golden b/coderd/testdata/insights/multiple_users_and_workspaces_week_all_templates.json.golden
@@ -66,7 +66,7 @@
         "display_name": "app1",
         "slug": "app1",
         "icon": "/icon1.png",
-        "seconds": 25200
+        "seconds": 25380
       },
       {
         "template_ids": [
@@ -76,7 +76,7 @@
         "display_name": "app3",
         "slug": "app3",
         "icon": "/icon2.png",
-        "seconds": 900
+        "seconds": 720
       },
       {
         "template_ids": [
diff --git a/coderd/testdata/insights/multiple_users_and_workspaces_week_deployment_wide.json.golden b/coderd/testdata/insights/multiple_users_and_workspaces_week_deployment_wide.json.golden
@@ -66,7 +66,7 @@
         "display_name": "app1",
         "slug": "app1",
         "icon": "/icon1.png",
-        "seconds": 25200
+        "seconds": 25380
       },
       {
         "template_ids": [
@@ -76,7 +76,7 @@
         "display_name": "app3",
         "slug": "app3",
         "icon": "/icon2.png",
-        "seconds": 900
+        "seconds": 720
       },
       {
         "template_ids": [
diff --git a/coderd/testdata/insights/multiple_users_and_workspaces_week_first_template.json.golden b/coderd/testdata/insights/multiple_users_and_workspaces_week_first_template.json.golden
@@ -55,7 +55,7 @@
         "display_name": "app1",
         "slug": "app1",
         "icon": "/icon1.png",
-        "seconds": 3600
+        "seconds": 3780
       },
       {
         "template_ids": [
@@ -65,7 +65,7 @@
         "display_name": "app3",
         "slug": "app3",
         "icon": "/icon2.png",
-        "seconds": 900
+        "seconds": 720
       }
     ],
     "parameters_usage": []
diff --git a/coderd/testdata/insights/multiple_users_and_workspaces_week_other_timezone_(São_Paulo).json.golden b/coderd/testdata/insights/multiple_users_and_workspaces_week_other_timezone_(São_Paulo).json.golden
@@ -51,13 +51,14 @@
       },
       {
         "template_ids": [
+          "00000000-0000-0000-0000-000000000001",
           "00000000-0000-0000-0000-000000000002"
         ],
         "type": "app",
         "display_name": "app1",
         "slug": "app1",
         "icon": "/icon1.png",
-        "seconds": 21600
+        "seconds": 21720
       },
       {
         "template_ids": [
@@ -67,7 +68,7 @@
         "display_name": "app3",
         "slug": "app3",
         "icon": "/icon2.png",
-        "seconds": 4500
+        "seconds": 4320
       },
       {
         "template_ids": [

Original file line number	Diff line number	Diff line change
`@@ -2048,8 +2048,8 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G`
`2048`	`2048`	`t = arg.StartTime`
`2049`	`2049`	`}`
`2050`	`2050`	`for t.Before(s.SessionEndedAt) && t.Before(arg.EndTime) {`
`2051`		`- appUsageIntervalsByUserAgentApp[key][t] = 300 // 5 minutes.`
`2052`		`- t = t.Add(5 * time.Minute)`
	`2051`	`+ appUsageIntervalsByUserAgentApp[key][t] = 60 // 1 minute.`
	`2052`	`+ t = t.Add(1 * time.Minute)`
`2053`	`2053`	`}`
`2054`	`2054`	`}`
`2055`	`2055`