Add missing index for getting recent push eventsindexes-for-recent-push-events

This index is required to allow fast retrieval of recent push events
without merge requests. Without this index in place the query would
lead to PostgreSQL scanning over 150 000 index entries all the time,
easily taking up between 200 and 500 milliseconds. This index
reduces the time spent in this process to around 40 milliseconds on
GitLab.com.

2 files changed, 42 insertions, 2 deletions

diff --git a/db/migrate/20170914135630_add_index_for_recent_push_events.rb b/db/migrate/20170914135630_add_index_for_recent_push_events.rb
new file mode 100644
index 00000000000..99f593b0465
--- /dev/null
+++ b/db/migrate/20170914135630_add_index_for_recent_push_events.rb
@@ -0,0 +1,40 @@
+# See http://doc.gitlab.com/ce/development/migration_style_guide.html
+# for more information on how to write migrations for GitLab.
+
+class AddIndexForRecentPushEvents < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  disable_ddl_transaction!
+
+  def up
+    add_concurrent_index_if_not_present(
+      :merge_requests,
+      [:source_project_id, :source_branch]
+    )
+
+    remove_concurrent_index_if_present(:merge_requests, :source_project_id)
+  end
+
+  def down
+    add_concurrent_index_if_not_present(:merge_requests, :source_project_id)
+
+    remove_concurrent_index_if_present(
+      :merge_requests,
+      [:source_project_id, :source_branch]
+    )
+  end
+
+  def add_concurrent_index_if_not_present(table, columns)
+    return if index_exists?(table, columns)
+
+    add_concurrent_index(table, columns)
+  end
+
+  def remove_concurrent_index_if_present(table, columns)
+    return unless index_exists?(table, columns)
+
+    remove_concurrent_index(table, columns)
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index dd0ef04788b..2d8c33591f0 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -11,7 +11,7 @@
 #
 # It's strongly recommended that you check this file into your version control system.
 
-ActiveRecord::Schema.define(version: 20170913131410) do
+ActiveRecord::Schema.define(version: 20170914135630) do
 
   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"
@@ -892,7 +892,7 @@ ActiveRecord::Schema.define(version: 20170913131410) do
   add_index "merge_requests", ["head_pipeline_id"], name: "index_merge_requests_on_head_pipeline_id", using: :btree
   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", ["source_project_id", "source_branch"], name: "index_merge_requests_on_source_project_id_and_source_branch", 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