13006: Adds expression index on links.[tail|head]_uuid

Arvados-DCO-1.1-Signed-off-by: Lucas Di Pentima <ldipentima@veritasgenetics.com>

diff --git a/services/api/db/migrate/20181213183234_add_expression_index_to_links.rb b/services/api/db/migrate/20181213183234_add_expression_index_to_links.rb
new file mode 100644 (file)
index 0000000..2fdf830
--- /dev/null
+++ b/services/api/db/migrate/20181213183234_add_expression_index_to_links.rb
@@ -0,0 +1,11 @@
+class AddExpressionIndexToLinks < ActiveRecord::Migration
+  def up
+    ActiveRecord::Base.connection.execute 'CREATE INDEX index_links_on_substring_head_uuid on links (substring(head_uuid, 7, 5))'
+    ActiveRecord::Base.connection.execute 'CREATE INDEX index_links_on_substring_tail_uuid on links (substring(tail_uuid, 7, 5))'
+  end
+
+  def down
+    ActiveRecord::Base.connection.execute 'DROP INDEX index_links_on_substring_head_uuid'
+    ActiveRecord::Base.connection.execute 'DROP INDEX index_links_on_substring_tail_uuid'
+  end
+end

diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql
index aa29a1cbb409d59542d0d037cbdf703f9c407ea5..211fa5043fda2aedc33646f8c98dff863bec8d7a 100644 (file)
--- a/services/api/db/structure.sql
+++ b/services/api/db/structure.sql
@@ -2278,6 +2278,20 @@ CREATE INDEX index_links_on_modified_at_uuid ON public.links USING btree (modifi
 CREATE INDEX index_links_on_owner_uuid ON public.links USING btree (owner_uuid);
 
 
+--
+-- Name: index_links_on_substring_head_uuid; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX index_links_on_substring_head_uuid ON public.links USING btree ("substring"((head_uuid)::text, 7, 5));
+
+
+--
+-- Name: index_links_on_substring_tail_uuid; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX index_links_on_substring_tail_uuid ON public.links USING btree ("substring"((tail_uuid)::text, 7, 5));
+
+
 --
 -- Name: index_links_on_tail_uuid; Type: INDEX; Schema: public; Owner: -
 --
@@ -3201,3 +3215,5 @@ INSERT INTO schema_migrations (version) VALUES ('20181005192222');
 
 INSERT INTO schema_migrations (version) VALUES ('20181011184200');
 
+INSERT INTO schema_migrations (version) VALUES ('20181213183234');
+