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.

3 files changed, 51 insertions, 3 deletions

diff --git a/changelogs/unreleased/reorganise-issues-indexes-for-sorting.yml b/changelogs/unreleased/reorganise-issues-indexes-for-sorting.yml
new file mode 100644
index 00000000000..5bfe55e562f
--- /dev/null
+++ b/changelogs/unreleased/reorganise-issues-indexes-for-sorting.yml
@@ -0,0 +1,4 @@
+---
+title: Re-organise "issues" indexes for faster ordering
+merge_request:
+author:
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"}