Replace issues and merge requests trigram indexes with constrained indexes for `deleted_at`constrained-indexes

3 files changed, 44 insertions, 4 deletions

diff --git a/CHANGELOG b/CHANGELOG
index dc06303aecf..5ccd07b87b5 100644
--- a/CHANGELOG
+++ b/CHANGELOG
@@ -21,6 +21,7 @@ v 8.13.0 (unreleased)
   - Add more tests for calendar contribution (ClemMakesApps)
   - Update Gitlab Shell to fix some problems with moving projects between storages
   - Cache rendered markdown in the database, rather than Redis
+  - Replace issues and merge requests trigram indexes with constrained indexes to improve their performance
   - Avoid database queries on Banzai::ReferenceParser::BaseParser for nodes without references
   - Simplify Mentionable concern instance methods
   - Fix permission for setting an issue's due date
diff --git a/db/migrate/20160607185958_add_constrained_trigram_indexes_on_issuables.rb b/db/migrate/20160607185958_add_constrained_trigram_indexes_on_issuables.rb
new file mode 100644
index 00000000000..e733c8b9901
--- /dev/null
+++ b/db/migrate/20160607185958_add_constrained_trigram_indexes_on_issuables.rb
@@ -0,0 +1,39 @@
+# See http://doc.gitlab.com/ce/development/migration_style_guide.html
+# for more information on how to write migrations for GitLab.
+
+class AddConstrainedTrigramIndexesOnIssuables < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  disable_ddl_transaction!
+
+  def up
+    return unless Gitlab::Database.postgresql?
+
+    to_index.each do |table, columns|
+      columns.each do |column|
+        remove_index table, name: "index_#{table}_on_#{column}_trigram"
+        execute "CREATE INDEX CONCURRENTLY index_#{table}_on_#{column}_trigram ON #{table} USING gin(#{column} gin_trgm_ops) WHERE deleted_at IS NULL;"
+      end
+    end
+  end
+
+  def down
+    return unless Gitlab::Database.postgresql?
+
+    to_index.each do |table, columns|
+      columns.each do |column|
+        remove_index table, name: "index_#{table}_on_#{column}_trigram"
+        execute "CREATE INDEX CONCURRENTLY index_#{table}_on_#{column}_trigram ON #{table} USING gin(#{column} gin_trgm_ops);"
+      end
+    end
+  end
+
+  def to_index
+    {
+      issues:         [:title, :description],
+      merge_requests: [:title, :description]
+    }
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index 56da70b3c02..92587fdc7ce 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -485,12 +485,12 @@ ActiveRecord::Schema.define(version: 20160926145521) do
   add_index "issues", ["confidential"], name: "index_issues_on_confidential", using: :btree
   add_index "issues", ["created_at"], name: "index_issues_on_created_at", using: :btree
   add_index "issues", ["deleted_at"], name: "index_issues_on_deleted_at", using: :btree
-  add_index "issues", ["description"], name: "index_issues_on_description_trigram", using: :gin, opclasses: {"description"=>"gin_trgm_ops"}
+  add_index "issues", ["description"], name: "index_issues_on_description_trigram", where: "(deleted_at IS NULL)", using: :gin, opclasses: {"description"=>"gin_trgm_ops)"}
   add_index "issues", ["due_date"], name: "index_issues_on_due_date", using: :btree
   add_index "issues", ["milestone_id"], name: "index_issues_on_milestone_id", using: :btree
   add_index "issues", ["project_id", "iid"], name: "index_issues_on_project_id_and_iid", unique: true, using: :btree
   add_index "issues", ["state"], name: "index_issues_on_state", using: :btree
-  add_index "issues", ["title"], name: "index_issues_on_title_trigram", using: :gin, opclasses: {"title"=>"gin_trgm_ops"}
+  add_index "issues", ["title"], name: "index_issues_on_title_trigram", where: "(deleted_at IS NULL)", using: :gin, opclasses: {"title"=>"gin_trgm_ops)"}
 
   create_table "keys", force: :cascade do |t|
     t.integer  "user_id"
@@ -650,14 +650,14 @@ ActiveRecord::Schema.define(version: 20160926145521) do
   add_index "merge_requests", ["author_id"], name: "index_merge_requests_on_author_id", using: :btree
   add_index "merge_requests", ["created_at"], name: "index_merge_requests_on_created_at", using: :btree
   add_index "merge_requests", ["deleted_at"], name: "index_merge_requests_on_deleted_at", using: :btree
-  add_index "merge_requests", ["description"], name: "index_merge_requests_on_description_trigram", using: :gin, opclasses: {"description"=>"gin_trgm_ops"}
+  add_index "merge_requests", ["description"], name: "index_merge_requests_on_description_trigram", where: "(deleted_at IS NULL)", using: :gin, opclasses: {"description"=>"gin_trgm_ops)"}
   add_index "merge_requests", ["milestone_id"], name: "index_merge_requests_on_milestone_id", using: :btree
   add_index "merge_requests", ["source_branch"], name: "index_merge_requests_on_source_branch", using: :btree
   add_index "merge_requests", ["source_project_id"], name: "index_merge_requests_on_source_project_id", using: :btree
   add_index "merge_requests", ["target_branch"], name: "index_merge_requests_on_target_branch", using: :btree
   add_index "merge_requests", ["target_project_id", "iid"], name: "index_merge_requests_on_target_project_id_and_iid", unique: true, using: :btree
   add_index "merge_requests", ["title"], name: "index_merge_requests_on_title", using: :btree
-  add_index "merge_requests", ["title"], name: "index_merge_requests_on_title_trigram", using: :gin, opclasses: {"title"=>"gin_trgm_ops"}
+  add_index "merge_requests", ["title"], name: "index_merge_requests_on_title_trigram", where: "(deleted_at IS NULL)", using: :gin, opclasses: {"title"=>"gin_trgm_ops)"}
 
   create_table "merge_requests_closing_issues", force: :cascade do |t|
     t.integer  "merge_request_id", null: false