18943: Migrate created_at indexes to (created_at, uuid)

Arvados-DCO-1.1-Signed-off-by: Peter Amstutz <peter.amstutz@curii.com>

diff --git a/services/api/db/migrate/20220401153101_fix_created_at_indexes.rb b/services/api/db/migrate/20220401153101_fix_created_at_indexes.rb
new file mode 100644 (file)
index 0000000..22d9b28
--- /dev/null
+++ b/services/api/db/migrate/20220401153101_fix_created_at_indexes.rb
@@ -0,0 +1,23 @@
+# Copyright (C) The Arvados Authors. All rights reserved.
+#
+# SPDX-License-Identifier: AGPL-3.0
+
+class FixCreatedAtIndexes < ActiveRecord::Migration[5.2]
+  def tables
+    %w{collections links logs groups users}
+  end
+
+  def up
+    tables.each do |t|
+      remove_index t.to_sym, :created_at
+      add_index t.to_sym, [:created_at, :uuid]
+    end
+  end
+
+  def down
+    tables.each do |t|
+      remove_index t.to_sym, [:created_at, :uuid]
+      add_index t.to_sym, :created_at
+    end
+  end
+end

diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql
index cfe21f7c9ae29307b42fd25236a1f4c195254da0..d874bce9405a6b2311d91642199801d008500a18 100644 (file)
--- a/services/api/db/structure.sql
+++ b/services/api/db/structure.sql
@@ -1905,10 +1905,10 @@ CREATE UNIQUE INDEX index_authorized_keys_on_uuid ON public.authorized_keys USIN
 
 
 --
--- Name: index_collections_on_created_at; Type: INDEX; Schema: public; Owner: -
+-- Name: index_collections_on_created_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
-CREATE INDEX index_collections_on_created_at ON public.collections USING btree (created_at);
+CREATE INDEX index_collections_on_created_at_and_uuid ON public.collections USING btree (created_at, uuid);
 
 
 --
@@ -2094,10 +2094,10 @@ CREATE UNIQUE INDEX index_frozen_groups_on_uuid ON public.frozen_groups USING bt
 
 
 --
--- Name: index_groups_on_created_at; Type: INDEX; Schema: public; Owner: -
+-- Name: index_groups_on_created_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
-CREATE INDEX index_groups_on_created_at ON public.groups USING btree (created_at);
+CREATE INDEX index_groups_on_created_at_and_uuid ON public.groups USING btree (created_at, uuid);
 
 
 --
@@ -2360,10 +2360,10 @@ CREATE UNIQUE INDEX index_keep_services_on_uuid ON public.keep_services USING bt
 
 
 --
--- Name: index_links_on_created_at; Type: INDEX; Schema: public; Owner: -
+-- Name: index_links_on_created_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
-CREATE INDEX index_links_on_created_at ON public.links USING btree (created_at);
+CREATE INDEX index_links_on_created_at_and_uuid ON public.links USING btree (created_at, uuid);
 
 
 --
@@ -2423,10 +2423,10 @@ CREATE UNIQUE INDEX index_links_on_uuid ON public.links USING btree (uuid);
 
 
 --
--- Name: index_logs_on_created_at; Type: INDEX; Schema: public; Owner: -
+-- Name: index_logs_on_created_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
-CREATE INDEX index_logs_on_created_at ON public.logs USING btree (created_at);
+CREATE INDEX index_logs_on_created_at_and_uuid ON public.logs USING btree (created_at, uuid);
 
 
 --
@@ -2689,10 +2689,10 @@ CREATE UNIQUE INDEX index_trashed_groups_on_group_uuid ON public.trashed_groups
 
 
 --
--- Name: index_users_on_created_at; Type: INDEX; Schema: public; Owner: -
+-- Name: index_users_on_created_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
-CREATE INDEX index_users_on_created_at ON public.users USING btree (created_at);
+CREATE INDEX index_users_on_created_at_and_uuid ON public.users USING btree (created_at, uuid);
 
 
 --
@@ -3185,6 +3185,7 @@ INSERT INTO "schema_migrations" (version) VALUES
 ('20211027154300'),
 ('20220224203102'),
 ('20220301155729'),
-('20220303204419');
+('20220303204419'),
+('20220401153101');