18943: modified_by index doesn't need to be (desc, desc)

Can use an (asc, asc) index for a (desc, desc) "order by".

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
index 308e69c9bc14ec5ea46aa2fe131f2cd83ebef943..5908ab4bc714438aac34a4a4af051409b4236f44 100644 (file)
--- a/services/api/db/migrate/20220401153101_fix_created_at_indexes.rb
+++ b/services/api/db/migrate/20220401153101_fix_created_at_indexes.rb
@@ -14,7 +14,7 @@ class FixCreatedAtIndexes < ActiveRecord::Migration[5.2]
       ActiveRecord::Base.connection.execute("DROP INDEX IF EXISTS index_#{table.to_s}_on_modified_at_and_uuid")
 
       ActiveRecord::Base.connection.execute("CREATE INDEX IF NOT EXISTS index_#{table.to_s}_on_created_at_and_uuid ON #{table.to_s} USING btree (created_at, uuid)")
       ActiveRecord::Base.connection.execute("DROP INDEX IF EXISTS index_#{table.to_s}_on_modified_at_and_uuid")
 
       ActiveRecord::Base.connection.execute("CREATE INDEX IF NOT EXISTS index_#{table.to_s}_on_created_at_and_uuid ON #{table.to_s} USING btree (created_at, uuid)")

-      ActiveRecord::Base.connection.execute("CREATE INDEX IF NOT EXISTS index_#{table.to_s}_on_modified_at_and_uuid ON #{table.to_s} USING btree (modified_at desc, uuid desc)")
+      ActiveRecord::Base.connection.execute("CREATE INDEX IF NOT EXISTS index_#{table.to_s}_on_modified_at_and_uuid ON #{table.to_s} USING btree (modified_at, uuid)")

     end
   end
 
     end
   end
 

diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql
index 8cb27ab4df6f24cc99ee0801c810f5b0568ecdd2..2858bba4f2f5ca20d90a9b85251fbb1304e6cbc6 100644 (file)
--- a/services/api/db/structure.sql
+++ b/services/api/db/structure.sql
@@ -1943,7 +1943,7 @@ CREATE INDEX index_collections_on_is_trashed ON public.collections USING btree (
 -- Name: index_collections_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
 -- Name: index_collections_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 

-CREATE INDEX index_collections_on_modified_at_and_uuid ON public.collections USING btree (modified_at DESC, uuid DESC);
+CREATE INDEX index_collections_on_modified_at_and_uuid ON public.collections USING btree (modified_at, uuid);

 
 
 --
 
 
 --


@@ -2006,7 +2006,7 @@ CREATE INDEX index_container_requests_on_created_at_uuid ON public.container_req
 -- Name: index_container_requests_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
 -- Name: index_container_requests_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 

-CREATE INDEX index_container_requests_on_modified_at_and_uuid ON public.container_requests USING btree (modified_at DESC, uuid DESC);
+CREATE INDEX index_container_requests_on_modified_at_and_uuid ON public.container_requests USING btree (modified_at, uuid);

 
 
 --
 
 
 --


@@ -2146,7 +2146,7 @@ CREATE INDEX index_groups_on_is_trashed ON public.groups USING btree (is_trashed
 -- Name: index_groups_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
 -- Name: index_groups_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 

-CREATE INDEX index_groups_on_modified_at_and_uuid ON public.groups USING btree (modified_at DESC, uuid DESC);
+CREATE INDEX index_groups_on_modified_at_and_uuid ON public.groups USING btree (modified_at, uuid);

 
 
 --
 
 
 --


@@ -2398,7 +2398,7 @@ CREATE INDEX index_links_on_head_uuid ON public.links USING btree (head_uuid);
 -- Name: index_links_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
 -- Name: index_links_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 

-CREATE INDEX index_links_on_modified_at_and_uuid ON public.links USING btree (modified_at DESC, uuid DESC);
+CREATE INDEX index_links_on_modified_at_and_uuid ON public.links USING btree (modified_at, uuid);

 
 
 --
 
 
 --


@@ -2468,7 +2468,7 @@ CREATE INDEX index_logs_on_event_type ON public.logs USING btree (event_type);
 -- Name: index_logs_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
 -- Name: index_logs_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 

-CREATE INDEX index_logs_on_modified_at_and_uuid ON public.logs USING btree (modified_at DESC, uuid DESC);
+CREATE INDEX index_logs_on_modified_at_and_uuid ON public.logs USING btree (modified_at, uuid);

 
 
 --
 
 
 --


@@ -2636,7 +2636,7 @@ CREATE INDEX index_repositories_on_created_at_uuid ON public.repositories USING
 -- Name: index_repositories_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
 -- Name: index_repositories_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 

-CREATE INDEX index_repositories_on_modified_at_and_uuid ON public.repositories USING btree (modified_at DESC, uuid DESC);
+CREATE INDEX index_repositories_on_modified_at_and_uuid ON public.repositories USING btree (modified_at, uuid);

 
 
 --
 
 
 --


@@ -2741,7 +2741,7 @@ CREATE UNIQUE INDEX index_users_on_identity_url ON public.users USING btree (ide
 -- Name: index_users_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
 -- Name: index_users_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 

-CREATE INDEX index_users_on_modified_at_and_uuid ON public.users USING btree (modified_at DESC, uuid DESC);
+CREATE INDEX index_users_on_modified_at_and_uuid ON public.users USING btree (modified_at, uuid);

 
 
 --
 
 
 --


@@ -2790,7 +2790,7 @@ CREATE INDEX index_virtual_machines_on_hostname ON public.virtual_machines USING
 -- Name: index_virtual_machines_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
 -- Name: index_virtual_machines_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 

-CREATE INDEX index_virtual_machines_on_modified_at_and_uuid ON public.virtual_machines USING btree (modified_at DESC, uuid DESC);
+CREATE INDEX index_virtual_machines_on_modified_at_and_uuid ON public.virtual_machines USING btree (modified_at, uuid);

 
 
 --
 
 
 --


@@ -2825,7 +2825,7 @@ CREATE INDEX index_workflows_on_created_at_uuid ON public.workflows USING btree
 -- Name: index_workflows_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
 -- Name: index_workflows_on_modified_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 

-CREATE INDEX index_workflows_on_modified_at_and_uuid ON public.workflows USING btree (modified_at DESC, uuid DESC);
+CREATE INDEX index_workflows_on_modified_at_and_uuid ON public.workflows USING btree (modified_at, uuid);

 
 
 --
 
 
 --