From 03a9907f84d2722bd11ce8c3095de4a5ad3e07b6 Mon Sep 17 00:00:00 2001 From: Peter Amstutz Date: Fri, 22 Sep 2023 16:22:44 -0400 Subject: [PATCH] 20990: Add btree index on name for groups & collections Arvados-DCO-1.1-Signed-off-by: Peter Amstutz --- ...ee_name_index_to_collections_and_groups.rb | 24 +++++++++++++++++++ services/api/db/structure.sql | 17 ++++++++++++- 2 files changed, 40 insertions(+), 1 deletion(-) create mode 100644 services/api/db/migrate/20230922000000_add_btree_name_index_to_collections_and_groups.rb 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 index 0000000000..7e6e725c9b --- /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 6e8b128c9e..a26db2e5db 100644 --- 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'); -- 2.30.2