4019: Add index for efficient jsonb query on collection properties

Arvados-DCO-1.1-Signed-off-by: Peter Amstutz <pamstutz@veritasgenetics.com>

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 (file)
index 0000000..ce2403e
--- /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 8f405c00c2869202835f70cb5de6947615bb8b5f..14729d31bc91a558dbead7de381e80f85ffb0cfe 100644 (file)
--- 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');
+