feat: modify workspace_agent_stats index (#10073)

mtojek · web-flow · commit ab9276bd088f · 2023-10-05T14:49:08.000+02:00
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000161_workspace_agent_stats_template_id_created_at_user_id_include_sessions.down.sql b/coderd/database/migrations/000161_workspace_agent_stats_template_id_created_at_user_id_include_sessions.down.sql
@@ -0,0 +1,5 @@
+DROP INDEX workspace_agent_stats_template_id_created_at_user_id_idx;
+
+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.';
diff --git a/coderd/database/migrations/000161_workspace_agent_stats_template_id_created_at_user_id_include_sessions.up.sql b/coderd/database/migrations/000161_workspace_agent_stats_template_id_created_at_user_id_include_sessions.up.sql
@@ -0,0 +1,5 @@
+DROP INDEX workspace_agent_stats_template_id_created_at_user_id_idx;
+
+CREATE INDEX workspace_agent_stats_template_id_created_at_user_id_idx ON workspace_agent_stats (template_id, created_at, user_id) INCLUDE (session_count_vscode, session_count_jetbrains, session_count_reconnecting_pty, session_count_ssh, connection_median_latency_ms) 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.';
