diff options
author | Yorick Peterse <yorickpeterse@gmail.com> | 2017-08-03 15:18:57 +0200 |
---|---|---|
committer | Yorick Peterse <yorickpeterse@gmail.com> | 2017-08-03 15:20:59 +0200 |
commit | 9b3f0569fa298f192735af10d685ffc68b86afe3 (patch) | |
tree | 69bb3470b3f356e2ae251c90c11f98589eb35b99 /db | |
parent | 27d34789c99510c5005e2db0a57c308e2eca2bba (diff) | |
download | gitlab-ce-9b3f0569fa298f192735af10d685ffc68b86afe3.tar.gz |
Re-organise "issues" indexes for faster orderingreorganise-issues-indexes-for-sorting
By adding various composite indexes we can reduce the time spent
retrieving issue lists. Because of the way these indexes are built
column wise we can also remove some standalone indexes, keeping the
total number of indexes in check.
Diffstat (limited to 'db')
-rw-r--r-- | db/migrate/20170803130232_reorganise_issues_indexes_for_faster_sorting.rb | 43 | ||||
-rw-r--r-- | db/schema.rb | 7 |
2 files changed, 47 insertions, 3 deletions
diff --git a/db/migrate/20170803130232_reorganise_issues_indexes_for_faster_sorting.rb b/db/migrate/20170803130232_reorganise_issues_indexes_for_faster_sorting.rb new file mode 100644 index 00000000000..eb7d1be1732 --- /dev/null +++ b/db/migrate/20170803130232_reorganise_issues_indexes_for_faster_sorting.rb @@ -0,0 +1,43 @@ +# See http://doc.gitlab.com/ce/development/migration_style_guide.html +# for more information on how to write migrations for GitLab. + +class ReorganiseIssuesIndexesForFasterSorting < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + # Set this constant to true if this migration requires downtime. + DOWNTIME = false + + disable_ddl_transaction! + + REMOVE_INDEX_COLUMNS = %i[project_id created_at due_date updated_at].freeze + + ADD_INDEX_COLUMNS = [ + %i[project_id created_at id state], + %i[project_id due_date id state], + %i[project_id updated_at id state] + ].freeze + + TABLE = :issues + + def up + add_indexes(ADD_INDEX_COLUMNS) + remove_indexes(REMOVE_INDEX_COLUMNS) + end + + def down + add_indexes(REMOVE_INDEX_COLUMNS) + remove_indexes(ADD_INDEX_COLUMNS) + end + + def add_indexes(columns) + columns.each do |column| + add_concurrent_index(TABLE, column) unless index_exists?(TABLE, column) + end + end + + def remove_indexes(columns) + columns.each do |column| + remove_concurrent_index(TABLE, column) if index_exists?(TABLE, column) + end + end +end diff --git a/db/schema.rb b/db/schema.rb index 4ba218e1e0d..f2f35acef95 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: 20170728101014) do +ActiveRecord::Schema.define(version: 20170803130232) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" @@ -641,12 +641,13 @@ ActiveRecord::Schema.define(version: 20170728101014) do add_index "issues", ["assignee_id"], name: "index_issues_on_assignee_id", using: :btree add_index "issues", ["author_id"], name: "index_issues_on_author_id", using: :btree 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", ["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", "created_at", "id", "state"], name: "index_issues_on_project_id_and_created_at_and_id_and_state", using: :btree + add_index "issues", ["project_id", "due_date", "id", "state"], name: "index_issues_on_project_id_and_due_date_and_id_and_state", using: :btree add_index "issues", ["project_id", "iid"], name: "index_issues_on_project_id_and_iid", unique: true, using: :btree + add_index "issues", ["project_id", "updated_at", "id", "state"], name: "index_issues_on_project_id_and_updated_at_and_id_and_state", using: :btree add_index "issues", ["relative_position"], name: "index_issues_on_relative_position", 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"} |