cloud/bmaas: first round of indexes
These are based on inspecting the production database, and should be
enough to get us out of the woods wrt. BMDB performance.
Change-Id: Ice285f25bc1d2825a04750fc6d62ef0925b9d643
Reviewed-on: https://review.monogon.dev/c/monogon/+/1804
Tested-by: Jenkins CI
Reviewed-by: Lorenz Brun <lorenz@monogon.tech>
Reviewed-by: Tim Windelschmidt <tim@monogon.tech>
diff --git a/cloud/bmaas/bmdb/model/migrations/1686656942_add_indexes.up.sql b/cloud/bmaas/bmdb/model/migrations/1686656942_add_indexes.up.sql
new file mode 100644
index 0000000..a789e10
--- /dev/null
+++ b/cloud/bmaas/bmdb/model/migrations/1686656942_add_indexes.up.sql
@@ -0,0 +1,14 @@
+-- Used by the agent gRPC server to retrieve agent information by public key.
+CREATE INDEX agent_public_key_idx
+ON machine_agent_started (agent_public_key)
+INCLUDE (agent_started_at);
+
+-- Used by queries which require a live session.
+CREATE INDEX session_id_deadline_idx
+ON sessions (session_id, session_deadline)
+INCLUDE (session_component_name, session_runtime_info, session_Created_at, session_interval_seconds);
+
+-- Used by work retrieval/scheduling queries to exclude machines that have a given process backed off.
+CREATE INDEX process_machine_id_idx
+ON work_backoff (process, machine_id)
+INCLUDE (until);
\ No newline at end of file