From 590ed2ae27ea6d7888ddc5ccd9dcf502bde6e853 Mon Sep 17 00:00:00 2001 From: Yorick Peterse Date: Thu, 14 Sep 2017 16:02:17 +0200 Subject: Add missing index for getting 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. --- ...70914135630_add_index_for_recent_push_events.rb | 40 ++++++++++++++++++++++ db/schema.rb | 4 +-- 2 files changed, 42 insertions(+), 2 deletions(-) create mode 100644 db/migrate/20170914135630_add_index_for_recent_push_events.rb 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 -- cgit v1.2.1