feat: add index to workspace_agent_stats (#10009)

mtojek · web-flow · commit cb60409a8c3c · 2023-10-03T11:13:44.000Z
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000159_workspace_agent_stats_template_id_created_at_user_id_idx.down.sql b/coderd/database/migrations/000159_workspace_agent_stats_template_id_created_at_user_id_idx.down.sql
@@ -0,0 +1 @@
+DROP INDEX workspace_agent_stats_template_id_created_at_user_id_idx;
diff --git a/coderd/database/migrations/000159_workspace_agent_stats_template_id_created_at_user_id_idx.up.sql b/coderd/database/migrations/000159_workspace_agent_stats_template_id_created_at_user_id_idx.up.sql
@@ -0,0 +1,3 @@
+CREATE INDEX workspace_agent_stats_template_id_created_at_user_id_idx ON workspace_agent_stats USING btree (template_id, created_at DESC, user_id) WHERE connection_count > 0;
+
+COMMENT ON INDEX workspace_agent_stats_template_id_created_at_user_id_idx IS 'Support index for template insights endpoint to build interval reports faster.';

Original file line number	Diff line number	Diff line change
`@@ -0,0 +1 @@`
	`1`	`+DROP INDEX workspace_agent_stats_template_id_created_at_user_id_idx;`
Original file line number	Diff line number	Diff line change
`@@ -0,0 +1,3 @@`
	`1`	`+CREATE INDEX workspace_agent_stats_template_id_created_at_user_id_idx ON workspace_agent_stats USING btree (template_id, created_at DESC, user_id) WHERE connection_count > 0;`
	`2`	`+`
	`3`	`+COMMENT ON INDEX workspace_agent_stats_template_id_created_at_user_id_idx IS 'Support index for template insights endpoint to build interval reports faster.';`