14008: Add index to speed up dispatcher queries.

Arvados-DCO-1.1-Signed-off-by: Tom Clegg <tclegg@veritasgenetics.com>

diff --git a/services/api/db/migrate/20180820132617_add_lock_index_to_containers.rb b/services/api/db/migrate/20180820132617_add_lock_index_to_containers.rb
new file mode 100644 (file)
index 0000000..94ca100
--- /dev/null
+++ b/services/api/db/migrate/20180820132617_add_lock_index_to_containers.rb
@@ -0,0 +1,13 @@
+# Copyright (C) The Arvados Authors. All rights reserved.
+#
+# SPDX-License-Identifier: AGPL-3.0
+
+class AddLockIndexToContainers < ActiveRecord::Migration
+  def change
+    # For the current code in sdk/go/dispatch:
+    add_index :containers, [:locked_by_uuid, :priority]
+    # For future dispatchers that use filters instead of offset for
+    # more predictable paging:
+    add_index :containers, [:locked_by_uuid, :uuid]
+  end
+end

diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql
index 12158e51b4568517a73b3d11abec97eada45d527..67f5b21b5d7e122dc663acdd2648870375db9a9f 100644 (file)
--- a/services/api/db/structure.sql
+++ b/services/api/db/structure.sql
@@ -1899,6 +1899,20 @@ CREATE UNIQUE INDEX index_container_requests_on_uuid ON public.container_request
 CREATE INDEX index_containers_on_auth_uuid ON public.containers USING btree (auth_uuid);
 
 
+--
+-- Name: index_containers_on_locked_by_uuid_and_priority; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX index_containers_on_locked_by_uuid_and_priority ON public.containers USING btree (locked_by_uuid, priority);
+
+
+--
+-- Name: index_containers_on_locked_by_uuid_and_uuid; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX index_containers_on_locked_by_uuid_and_uuid ON public.containers USING btree (locked_by_uuid, uuid);
+
+
 --
 -- Name: index_containers_on_modified_at_uuid; Type: INDEX; Schema: public; Owner: -
 --
@@ -3121,3 +3135,5 @@ INSERT INTO schema_migrations (version) VALUES ('20180608123145');
 
 INSERT INTO schema_migrations (version) VALUES ('20180806133039');
 
+INSERT INTO schema_migrations (version) VALUES ('20180820132617');
+