From f19b6ef351d2ae8d22dc8dd669393224ca42bc26 Mon Sep 17 00:00:00 2001 From: Andreas Brandl Date: Thu, 1 Nov 2018 15:48:13 +0100 Subject: Add index to find stuck merge requests. See https://gitlab.com/gitlab-org/gitlab-ce/issues/45608#note_113803790 for query and plan. Closes https://gitlab.com/gitlab-org/gitlab-ce/issues/45608. --- changelogs/unreleased/ab-45608-stuck-mr-query.yml | 5 +++++ .../20181101144347_add_index_for_stuck_mr_query.rb | 16 ++++++++++++++++ db/schema.rb | 3 ++- 3 files changed, 23 insertions(+), 1 deletion(-) create mode 100644 changelogs/unreleased/ab-45608-stuck-mr-query.yml create mode 100644 db/migrate/20181101144347_add_index_for_stuck_mr_query.rb diff --git a/changelogs/unreleased/ab-45608-stuck-mr-query.yml b/changelogs/unreleased/ab-45608-stuck-mr-query.yml new file mode 100644 index 00000000000..3b64534e480 --- /dev/null +++ b/changelogs/unreleased/ab-45608-stuck-mr-query.yml @@ -0,0 +1,5 @@ +--- +title: Add index to find stuck merge requests. +merge_request: 22749 +author: +type: performance diff --git a/db/migrate/20181101144347_add_index_for_stuck_mr_query.rb b/db/migrate/20181101144347_add_index_for_stuck_mr_query.rb new file mode 100644 index 00000000000..5d3ace54e5c --- /dev/null +++ b/db/migrate/20181101144347_add_index_for_stuck_mr_query.rb @@ -0,0 +1,16 @@ +# frozen_string_literal: true +class AddIndexForStuckMrQuery < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + DOWNTIME = false + + disable_ddl_transaction! + + def up + add_concurrent_index :merge_requests, [:id, :merge_jid], where: "merge_jid IS NOT NULL and state = 'locked'" + end + + def down + remove_concurrent_index :merge_requests, [:id, :merge_jid], where: "merge_jid IS NOT NULL and state = 'locked'" + end +end diff --git a/db/schema.rb b/db/schema.rb index 22474916034..1a8b556228d 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: 20181031190559) do +ActiveRecord::Schema.define(version: 20181101144347) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" @@ -1294,6 +1294,7 @@ ActiveRecord::Schema.define(version: 20181031190559) do add_index "merge_requests", ["created_at"], name: "index_merge_requests_on_created_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", ["head_pipeline_id"], name: "index_merge_requests_on_head_pipeline_id", using: :btree + add_index "merge_requests", ["id", "merge_jid"], name: "index_merge_requests_on_id_and_merge_jid", where: "((merge_jid IS NOT NULL) AND ((state)::text = 'locked'::text))", using: :btree add_index "merge_requests", ["latest_merge_request_diff_id"], name: "index_merge_requests_on_latest_merge_request_diff_id", using: :btree add_index "merge_requests", ["merge_user_id"], name: "index_merge_requests_on_merge_user_id", where: "(merge_user_id IS NOT NULL)", using: :btree add_index "merge_requests", ["milestone_id"], name: "index_merge_requests_on_milestone_id", using: :btree -- cgit v1.2.1