Merge branch '20990-name-btree' refs #20990

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

diff --git a/services/api/db/migrate/20230922000000_add_btree_name_index_to_collections_and_groups.rb b/services/api/db/migrate/20230922000000_add_btree_name_index_to_collections_and_groups.rb
new file mode 100644 (file)
index 0000000..7e6e725
--- /dev/null
+++ b/services/api/db/migrate/20230922000000_add_btree_name_index_to_collections_and_groups.rb
@@ -0,0 +1,24 @@
+# Copyright (C) The Arvados Authors. All rights reserved.
+#
+# SPDX-License-Identifier: AGPL-3.0
+
+class AddBtreeNameIndexToCollectionsAndGroups < ActiveRecord::Migration[5.2]
+  #
+  # We previously added 'index_groups_on_name' and
+  # 'index_collections_on_name' but those are 'gin_trgm_ops' which is
+  # used with 'ilike' searches but despite documentation suggesting
+  # they would be, experience has shown these indexes don't get used
+  # for '=' (and/or they are much slower than the btree for exact
+  # matches).
+  #
+  # So we want to add a regular btree index.
+  #
+  def up
+    ActiveRecord::Base.connection.execute 'CREATE INDEX index_groups_on_name_btree on groups USING btree (name)'
+    ActiveRecord::Base.connection.execute 'CREATE INDEX index_collections_on_name_btree on collections USING btree (name)'
+  end
+  def down
+    ActiveRecord::Base.connection.execute 'DROP INDEX IF EXISTS index_collections_on_name_btree'
+    ActiveRecord::Base.connection.execute 'DROP INDEX IF EXISTS index_groups_on_name_btree'
+  end
+end

diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql
index 6e8b128c9e5e3be02da7b5da622e00661269ee71..a26db2e5dbd659b73ee414aaf7768f4fb4a8e983 100644 (file)
--- a/services/api/db/structure.sql
+++ b/services/api/db/structure.sql
@@ -2037,6 +2037,13 @@ CREATE INDEX index_collections_on_modified_at_and_uuid ON public.collections USI
 CREATE INDEX index_collections_on_name ON public.collections USING gin (name public.gin_trgm_ops);
 
 
+--
+-- Name: index_collections_on_name_btree; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX index_collections_on_name_btree ON public.collections USING btree (name);
+
+
 --
 -- Name: index_collections_on_owner_uuid; Type: INDEX; Schema: public; Owner: -
 --
@@ -2233,6 +2240,13 @@ CREATE INDEX index_groups_on_modified_at_and_uuid ON public.groups USING btree (
 CREATE INDEX index_groups_on_name ON public.groups USING gin (name public.gin_trgm_ops);
 
 
+--
+-- Name: index_groups_on_name_btree; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX index_groups_on_name_btree ON public.groups USING btree (name);
+
+
 --
 -- Name: index_groups_on_owner_uuid; Type: INDEX; Schema: public; Owner: -
 --
@@ -3293,6 +3307,7 @@ INSERT INTO "schema_migrations" (version) VALUES
 ('20230421142716'),
 ('20230503224107'),
 ('20230815160000'),
-('20230821000000');
+('20230821000000'),
+('20230922000000');