From 79a2d819d596e610f26c08beee53f5432bfbb360 Mon Sep 17 00:00:00 2001 From: Peter Amstutz Date: Tue, 12 Dec 2017 10:40:40 -0500 Subject: [PATCH] 4019: Add index for efficient jsonb query on collection properties Arvados-DCO-1.1-Signed-off-by: Peter Amstutz --- ...71212153352_add_gin_index_to_collection_properties.rb | 8 ++++++++ services/api/db/structure.sql | 9 +++++++++ 2 files changed, 17 insertions(+) create mode 100644 services/api/db/migrate/20171212153352_add_gin_index_to_collection_properties.rb diff --git a/services/api/db/migrate/20171212153352_add_gin_index_to_collection_properties.rb b/services/api/db/migrate/20171212153352_add_gin_index_to_collection_properties.rb new file mode 100644 index 0000000000..ce2403e743 --- /dev/null +++ b/services/api/db/migrate/20171212153352_add_gin_index_to_collection_properties.rb @@ -0,0 +1,8 @@ +class AddGinIndexToCollectionProperties < ActiveRecord::Migration + def up + ActiveRecord::Base.connection.execute("CREATE INDEX collection_index_on_properties ON collections USING gin (properties);") + end + def down + ActiveRecord::Base.connection.execute("DROP INDEX collection_index_on_properties") + end +end diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql index 8f405c00c2..14729d31bc 100644 --- a/services/api/db/structure.sql +++ b/services/api/db/structure.sql @@ -1605,6 +1605,13 @@ CREATE INDEX api_clients_search_index ON api_clients USING btree (uuid, owner_uu CREATE INDEX authorized_keys_search_index ON authorized_keys USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, name, key_type, authorized_user_uuid); +-- +-- Name: collection_index_on_properties; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX collection_index_on_properties ON collections USING gin (properties); + + -- -- Name: collections_full_text_search_idx; Type: INDEX; Schema: public; Owner: - -- @@ -3040,3 +3047,5 @@ INSERT INTO schema_migrations (version) VALUES ('20171027183824'); INSERT INTO schema_migrations (version) VALUES ('20171208203841'); +INSERT INTO schema_migrations (version) VALUES ('20171212153352'); + -- 2.39.5