Remove various redundant indexes

One can see which indexes are used in PostgreSQL by running the
following query:

    SELECT relname as table_name, indexrelname as index_name, idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty(pg_relation_size(indexrelname::regclass))
    FROM pg_stat_all_indexes
    WHERE schemaname = 'public'
    AND "idx_scan" = 0
    ORDER BY pg_relation_size(indexrelname::regclass) desc;

Using this query I built a list of indexes that could be potentially
removed. After checking every single one by hand to make sure they
really aren't used I only found 1 index that _would_ be used. This was a
GitLab GEO index (EE) specific that's currently not used simply because
the table is empty.

Apart from this one index all indexes could be removed. The migration
also takes care of 6 composite indexes that can be replaced with a
single column index, which in most cases was already present.

For more information see gitlab-org/gitlab-ce#20767.

2 files changed, 116 insertions, 52 deletions

diff --git a/db/migrate/20160810142633_remove_redundant_indexes.rb b/db/migrate/20160810142633_remove_redundant_indexes.rb
new file mode 100644
index 00000000000..8641c6ffa8f
--- /dev/null
+++ b/db/migrate/20160810142633_remove_redundant_indexes.rb
@@ -0,0 +1,112 @@
+# See http://doc.gitlab.com/ce/development/migration_style_guide.html
+# for more information on how to write migrations for GitLab.
+
+class RemoveRedundantIndexes < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  disable_ddl_transaction!
+
+  def up
+    indexes = [
+      [:ci_taggings, 'ci_taggings_idx'],
+      [:audit_events, 'index_audit_events_on_author_id'],
+      [:audit_events, 'index_audit_events_on_type'],
+      [:ci_builds, 'index_ci_builds_on_erased_by_id'],
+      [:ci_builds, 'index_ci_builds_on_project_id_and_commit_id'],
+      [:ci_builds, 'index_ci_builds_on_type'],
+      [:ci_commits, 'index_ci_commits_on_project_id'],
+      [:ci_commits, 'index_ci_commits_on_project_id_and_committed_at'],
+      [:ci_commits, 'index_ci_commits_on_project_id_and_committed_at_and_id'],
+      [:ci_commits, 'index_ci_commits_on_project_id_and_sha'],
+      [:ci_commits, 'index_ci_commits_on_sha'],
+      [:ci_events, 'index_ci_events_on_created_at'],
+      [:ci_events, 'index_ci_events_on_is_admin'],
+      [:ci_events, 'index_ci_events_on_project_id'],
+      [:ci_jobs, 'index_ci_jobs_on_deleted_at'],
+      [:ci_jobs, 'index_ci_jobs_on_project_id'],
+      [:ci_projects, 'index_ci_projects_on_gitlab_id'],
+      [:ci_projects, 'index_ci_projects_on_shared_runners_enabled'],
+      [:ci_services, 'index_ci_services_on_project_id'],
+      [:ci_sessions, 'index_ci_sessions_on_session_id'],
+      [:ci_sessions, 'index_ci_sessions_on_updated_at'],
+      [:ci_tags, 'index_ci_tags_on_name'],
+      [:ci_triggers, 'index_ci_triggers_on_deleted_at'],
+      [:identities, 'index_identities_on_created_at_and_id'],
+      [:issues, 'index_issues_on_title'],
+      [:keys, 'index_keys_on_created_at_and_id'],
+      [:members, 'index_members_on_created_at_and_id'],
+      [:members, 'index_members_on_type'],
+      [:milestones, 'index_milestones_on_created_at_and_id'],
+      [:namespaces, 'index_namespaces_on_visibility_level'],
+      [:projects, 'index_projects_on_builds_enabled_and_shared_runners_enabled'],
+      [:services, 'index_services_on_category'],
+      [:services, 'index_services_on_created_at_and_id'],
+      [:services, 'index_services_on_default'],
+      [:snippets, 'index_snippets_on_created_at'],
+      [:snippets, 'index_snippets_on_created_at_and_id'],
+      [:todos, 'index_todos_on_state'],
+      [:web_hooks, 'index_web_hooks_on_created_at_and_id'],
+
+      # These indexes _may_ be used but they can be replaced by other existing
+      # indexes.
+
+      # There's already a composite index on (project_id, iid) which means that
+      # a separate index for _just_ project_id is not needed.
+      [:issues, 'index_issues_on_project_id'],
+
+      # These are all composite indexes for the columns (created_at, id). In all
+      # these cases there's already a standalone index for "created_at" which
+      # can be used instead.
+      #
+      # Because the "id" column of these composite indexes is never needed (due
+      # to "id" already being indexed as its a primary key) these composite
+      # indexes are useless.
+      [:issues, 'index_issues_on_created_at_and_id'],
+      [:merge_requests, 'index_merge_requests_on_created_at_and_id'],
+      [:namespaces, 'index_namespaces_on_created_at_and_id'],
+      [:notes, 'index_notes_on_created_at_and_id'],
+      [:projects, 'index_projects_on_created_at_and_id'],
+      [:users, 'index_users_on_created_at_and_id'],
+    ]
+
+    transaction do
+      indexes.each do |(table, index)|
+        remove_index(table, name: index) if index_exists_by_name?(table, index)
+      end
+    end
+
+    add_concurrent_index(:users, :created_at)
+    add_concurrent_index(:projects, :created_at)
+    add_concurrent_index(:namespaces, :created_at)
+  end
+
+  def down
+    # We're only restoring the composite indexes that could be replaced with
+    # individual ones, just in case somebody would ever want to revert.
+    transaction do
+      remove_index(:users, :created_at)
+      remove_index(:projects, :created_at)
+      remove_index(:namespaces, :created_at)
+    end
+
+    [:issues, :merge_requests, :namespaces, :notes, :projects, :users].each do |table|
+      add_concurrent_index(table, [:created_at, :id],
+                           name: "index_#{table}_on_created_at_and_id")
+    end
+  end
+
+  # Rails' index_exists? doesn't work when you only give it a table and index
+  # name. As such we have to use some extra code to check if an index exists for
+  # a given name.
+  def index_exists_by_name?(table, index)
+    indexes_for_table[table].include?(index)
+  end
+
+  def indexes_for_table
+    @indexes_for_table ||= Hash.new do |hash, table_name|
+      hash[table_name] = indexes(table_name).map(&:name)
+    end
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index b795eeaa23c..059c923bb93 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: 20160810102349) do
+ActiveRecord::Schema.define(version: 20160810142633) do
 
   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"
@@ -102,9 +102,7 @@ ActiveRecord::Schema.define(version: 20160810102349) do
     t.datetime "updated_at"
   end
 
-  add_index "audit_events", ["author_id"], name: "index_audit_events_on_author_id", using: :btree
   add_index "audit_events", ["entity_id", "entity_type"], name: "index_audit_events_on_entity_id_and_entity_type", using: :btree
-  add_index "audit_events", ["type"], name: "index_audit_events_on_type", using: :btree
 
   create_table "award_emoji", force: :cascade do |t|
     t.string   "name"
@@ -179,13 +177,10 @@ ActiveRecord::Schema.define(version: 20160810102349) do
   add_index "ci_builds", ["commit_id", "type", "name", "ref"], name: "index_ci_builds_on_commit_id_and_type_and_name_and_ref", using: :btree
   add_index "ci_builds", ["commit_id", "type", "ref"], name: "index_ci_builds_on_commit_id_and_type_and_ref", using: :btree
   add_index "ci_builds", ["commit_id"], name: "index_ci_builds_on_commit_id", using: :btree
-  add_index "ci_builds", ["erased_by_id"], name: "index_ci_builds_on_erased_by_id", using: :btree
   add_index "ci_builds", ["gl_project_id"], name: "index_ci_builds_on_gl_project_id", using: :btree
-  add_index "ci_builds", ["project_id", "commit_id"], name: "index_ci_builds_on_project_id_and_commit_id", using: :btree
   add_index "ci_builds", ["project_id"], name: "index_ci_builds_on_project_id", using: :btree
   add_index "ci_builds", ["runner_id"], name: "index_ci_builds_on_runner_id", using: :btree
   add_index "ci_builds", ["status"], name: "index_ci_builds_on_status", using: :btree
-  add_index "ci_builds", ["type"], name: "index_ci_builds_on_type", using: :btree
 
   create_table "ci_commits", force: :cascade do |t|
     t.integer  "project_id"
@@ -209,11 +204,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
   add_index "ci_commits", ["gl_project_id", "sha"], name: "index_ci_commits_on_gl_project_id_and_sha", using: :btree
   add_index "ci_commits", ["gl_project_id", "status"], name: "index_ci_commits_on_gl_project_id_and_status", using: :btree
   add_index "ci_commits", ["gl_project_id"], name: "index_ci_commits_on_gl_project_id", using: :btree
-  add_index "ci_commits", ["project_id", "committed_at", "id"], name: "index_ci_commits_on_project_id_and_committed_at_and_id", using: :btree
-  add_index "ci_commits", ["project_id", "committed_at"], name: "index_ci_commits_on_project_id_and_committed_at", using: :btree
-  add_index "ci_commits", ["project_id", "sha"], name: "index_ci_commits_on_project_id_and_sha", using: :btree
-  add_index "ci_commits", ["project_id"], name: "index_ci_commits_on_project_id", using: :btree
-  add_index "ci_commits", ["sha"], name: "index_ci_commits_on_sha", using: :btree
   add_index "ci_commits", ["status"], name: "index_ci_commits_on_status", using: :btree
   add_index "ci_commits", ["user_id"], name: "index_ci_commits_on_user_id", using: :btree
 
@@ -226,10 +216,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
     t.datetime "updated_at"
   end
 
-  add_index "ci_events", ["created_at"], name: "index_ci_events_on_created_at", using: :btree
-  add_index "ci_events", ["is_admin"], name: "index_ci_events_on_is_admin", using: :btree
-  add_index "ci_events", ["project_id"], name: "index_ci_events_on_project_id", using: :btree
-
   create_table "ci_jobs", force: :cascade do |t|
     t.integer  "project_id",                          null: false
     t.text     "commands"
@@ -244,9 +230,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
     t.datetime "deleted_at"
   end
 
-  add_index "ci_jobs", ["deleted_at"], name: "index_ci_jobs_on_deleted_at", using: :btree
-  add_index "ci_jobs", ["project_id"], name: "index_ci_jobs_on_project_id", using: :btree
-
   create_table "ci_projects", force: :cascade do |t|
     t.string   "name"
     t.integer  "timeout",                  default: 3600,  null: false
@@ -270,9 +253,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
     t.text     "generated_yaml_config"
   end
 
-  add_index "ci_projects", ["gitlab_id"], name: "index_ci_projects_on_gitlab_id", using: :btree
-  add_index "ci_projects", ["shared_runners_enabled"], name: "index_ci_projects_on_shared_runners_enabled", using: :btree
-
   create_table "ci_runner_projects", force: :cascade do |t|
     t.integer  "runner_id",     null: false
     t.integer  "project_id"
@@ -314,8 +294,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
     t.text     "properties"
   end
 
-  add_index "ci_services", ["project_id"], name: "index_ci_services_on_project_id", using: :btree
-
   create_table "ci_sessions", force: :cascade do |t|
     t.string   "session_id", null: false
     t.text     "data"
@@ -323,9 +301,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
     t.datetime "updated_at"
   end
 
-  add_index "ci_sessions", ["session_id"], name: "index_ci_sessions_on_session_id", using: :btree
-  add_index "ci_sessions", ["updated_at"], name: "index_ci_sessions_on_updated_at", using: :btree
-
   create_table "ci_taggings", force: :cascade do |t|
     t.integer  "tag_id"
     t.integer  "taggable_id"
@@ -336,7 +311,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
     t.datetime "created_at"
   end
 
-  add_index "ci_taggings", ["tag_id", "taggable_id", "taggable_type", "context", "tagger_id", "tagger_type"], name: "ci_taggings_idx", unique: true, using: :btree
   add_index "ci_taggings", ["taggable_id", "taggable_type", "context"], name: "index_ci_taggings_on_taggable_id_and_taggable_type_and_context", using: :btree
 
   create_table "ci_tags", force: :cascade do |t|
@@ -344,8 +318,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
     t.integer "taggings_count", default: 0
   end
 
-  add_index "ci_tags", ["name"], name: "index_ci_tags_on_name", unique: true, using: :btree
-
   create_table "ci_trigger_requests", force: :cascade do |t|
     t.integer  "trigger_id", null: false
     t.text     "variables"
@@ -363,7 +335,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
     t.integer  "gl_project_id"
   end
 
-  add_index "ci_triggers", ["deleted_at"], name: "index_ci_triggers_on_deleted_at", using: :btree
   add_index "ci_triggers", ["gl_project_id"], name: "index_ci_triggers_on_gl_project_id", using: :btree
 
   create_table "ci_variables", force: :cascade do |t|
@@ -469,7 +440,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
     t.datetime "updated_at"
   end
 
-  add_index "identities", ["created_at", "id"], name: "index_identities_on_created_at_and_id", using: :btree
   add_index "identities", ["user_id"], name: "index_identities_on_user_id", using: :btree
 
   create_table "issues", force: :cascade do |t|
@@ -495,16 +465,13 @@ ActiveRecord::Schema.define(version: 20160810102349) 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", "id"], name: "index_issues_on_created_at_and_id", 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", "iid"], name: "index_issues_on_project_id_and_iid", unique: true, using: :btree
-  add_index "issues", ["project_id"], name: "index_issues_on_project_id", using: :btree
   add_index "issues", ["state"], name: "index_issues_on_state", using: :btree
-  add_index "issues", ["title"], name: "index_issues_on_title", using: :btree
   add_index "issues", ["title"], name: "index_issues_on_title_trigram", using: :gin, opclasses: {"title"=>"gin_trgm_ops"}
 
   create_table "keys", force: :cascade do |t|
@@ -518,7 +485,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
     t.boolean  "public",      default: false, null: false
   end
 
-  add_index "keys", ["created_at", "id"], name: "index_keys_on_created_at_and_id", using: :btree
   add_index "keys", ["fingerprint"], name: "index_keys_on_fingerprint", unique: true, using: :btree
   add_index "keys", ["user_id"], name: "index_keys_on_user_id", using: :btree
 
@@ -583,11 +549,9 @@ ActiveRecord::Schema.define(version: 20160810102349) do
   end
 
   add_index "members", ["access_level"], name: "index_members_on_access_level", using: :btree
-  add_index "members", ["created_at", "id"], name: "index_members_on_created_at_and_id", using: :btree
   add_index "members", ["invite_token"], name: "index_members_on_invite_token", unique: true, using: :btree
   add_index "members", ["requested_at"], name: "index_members_on_requested_at", using: :btree
   add_index "members", ["source_id", "source_type"], name: "index_members_on_source_id_and_source_type", using: :btree
-  add_index "members", ["type"], name: "index_members_on_type", using: :btree
   add_index "members", ["user_id"], name: "index_members_on_user_id", using: :btree
 
   create_table "merge_request_diffs", force: :cascade do |t|
@@ -634,7 +598,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
 
   add_index "merge_requests", ["assignee_id"], name: "index_merge_requests_on_assignee_id", using: :btree
   add_index "merge_requests", ["author_id"], name: "index_merge_requests_on_author_id", using: :btree
-  add_index "merge_requests", ["created_at", "id"], name: "index_merge_requests_on_created_at_and_id", using: :btree
   add_index "merge_requests", ["created_at"], name: "index_merge_requests_on_created_at", using: :btree
   add_index "merge_requests", ["deleted_at"], name: "index_merge_requests_on_deleted_at", using: :btree
   add_index "merge_requests", ["description"], name: "index_merge_requests_on_description_trigram", using: :gin, opclasses: {"description"=>"gin_trgm_ops"}
@@ -657,7 +620,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
     t.integer  "iid"
   end
 
-  add_index "milestones", ["created_at", "id"], name: "index_milestones_on_created_at_and_id", using: :btree
   add_index "milestones", ["description"], name: "index_milestones_on_description_trigram", using: :gin, opclasses: {"description"=>"gin_trgm_ops"}
   add_index "milestones", ["due_date"], name: "index_milestones_on_due_date", using: :btree
   add_index "milestones", ["project_id", "iid"], name: "index_milestones_on_project_id_and_iid", unique: true, using: :btree
@@ -679,14 +641,13 @@ ActiveRecord::Schema.define(version: 20160810102349) do
     t.boolean  "request_access_enabled", default: true,  null: false
   end
 
-  add_index "namespaces", ["created_at", "id"], name: "index_namespaces_on_created_at_and_id", using: :btree
+  add_index "namespaces", ["created_at"], name: "index_namespaces_on_created_at", using: :btree
   add_index "namespaces", ["name"], name: "index_namespaces_on_name", unique: true, using: :btree
   add_index "namespaces", ["name"], name: "index_namespaces_on_name_trigram", using: :gin, opclasses: {"name"=>"gin_trgm_ops"}
   add_index "namespaces", ["owner_id"], name: "index_namespaces_on_owner_id", using: :btree
   add_index "namespaces", ["path"], name: "index_namespaces_on_path", unique: true, using: :btree
   add_index "namespaces", ["path"], name: "index_namespaces_on_path_trigram", using: :gin, opclasses: {"path"=>"gin_trgm_ops"}
   add_index "namespaces", ["type"], name: "index_namespaces_on_type", using: :btree
-  add_index "namespaces", ["visibility_level"], name: "index_namespaces_on_visibility_level", using: :btree
 
   create_table "notes", force: :cascade do |t|
     t.text     "note"
@@ -709,7 +670,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
 
   add_index "notes", ["author_id"], name: "index_notes_on_author_id", using: :btree
   add_index "notes", ["commit_id"], name: "index_notes_on_commit_id", using: :btree
-  add_index "notes", ["created_at", "id"], name: "index_notes_on_created_at_and_id", using: :btree
   add_index "notes", ["created_at"], name: "index_notes_on_created_at", using: :btree
   add_index "notes", ["line_code"], name: "index_notes_on_line_code", using: :btree
   add_index "notes", ["note"], name: "index_notes_on_note_trigram", using: :gin, opclasses: {"note"=>"gin_trgm_ops"}
@@ -849,9 +809,8 @@ ActiveRecord::Schema.define(version: 20160810102349) do
     t.boolean  "request_access_enabled",             default: true,      null: false
   end
 
-  add_index "projects", ["builds_enabled", "shared_runners_enabled"], name: "index_projects_on_builds_enabled_and_shared_runners_enabled", using: :btree
   add_index "projects", ["ci_id"], name: "index_projects_on_ci_id", using: :btree
-  add_index "projects", ["created_at", "id"], name: "index_projects_on_created_at_and_id", using: :btree
+  add_index "projects", ["created_at"], name: "index_projects_on_created_at", using: :btree
   add_index "projects", ["creator_id"], name: "index_projects_on_creator_id", using: :btree
   add_index "projects", ["description"], name: "index_projects_on_description_trigram", using: :gin, opclasses: {"description"=>"gin_trgm_ops"}
   add_index "projects", ["last_activity_at"], name: "index_projects_on_last_activity_at", using: :btree
@@ -937,9 +896,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
     t.boolean  "wiki_page_events",      default: true
   end
 
-  add_index "services", ["category"], name: "index_services_on_category", using: :btree
-  add_index "services", ["created_at", "id"], name: "index_services_on_created_at_and_id", using: :btree
-  add_index "services", ["default"], name: "index_services_on_default", using: :btree
   add_index "services", ["project_id"], name: "index_services_on_project_id", using: :btree
   add_index "services", ["template"], name: "index_services_on_template", using: :btree
 
@@ -956,8 +912,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
   end
 
   add_index "snippets", ["author_id"], name: "index_snippets_on_author_id", using: :btree
-  add_index "snippets", ["created_at", "id"], name: "index_snippets_on_created_at_and_id", using: :btree
-  add_index "snippets", ["created_at"], name: "index_snippets_on_created_at", using: :btree
   add_index "snippets", ["file_name"], name: "index_snippets_on_file_name_trigram", using: :gin, opclasses: {"file_name"=>"gin_trgm_ops"}
   add_index "snippets", ["project_id"], name: "index_snippets_on_project_id", using: :btree
   add_index "snippets", ["title"], name: "index_snippets_on_title_trigram", using: :gin, opclasses: {"title"=>"gin_trgm_ops"}
@@ -1026,7 +980,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
   add_index "todos", ["commit_id"], name: "index_todos_on_commit_id", using: :btree
   add_index "todos", ["note_id"], name: "index_todos_on_note_id", using: :btree
   add_index "todos", ["project_id"], name: "index_todos_on_project_id", using: :btree
-  add_index "todos", ["state"], name: "index_todos_on_state", using: :btree
   add_index "todos", ["target_type", "target_id"], name: "index_todos_on_target_type_and_target_id", using: :btree
   add_index "todos", ["user_id"], name: "index_todos_on_user_id", using: :btree
 
@@ -1106,7 +1059,7 @@ ActiveRecord::Schema.define(version: 20160810102349) do
   add_index "users", ["admin"], name: "index_users_on_admin", using: :btree
   add_index "users", ["authentication_token"], name: "index_users_on_authentication_token", unique: true, using: :btree
   add_index "users", ["confirmation_token"], name: "index_users_on_confirmation_token", unique: true, using: :btree
-  add_index "users", ["created_at", "id"], name: "index_users_on_created_at_and_id", using: :btree
+  add_index "users", ["created_at"], name: "index_users_on_created_at", using: :btree
   add_index "users", ["current_sign_in_at"], name: "index_users_on_current_sign_in_at", using: :btree
   add_index "users", ["email"], name: "index_users_on_email", unique: true, using: :btree
   add_index "users", ["email"], name: "index_users_on_email_trigram", using: :gin, opclasses: {"email"=>"gin_trgm_ops"}
@@ -1146,7 +1099,6 @@ ActiveRecord::Schema.define(version: 20160810102349) do
     t.string   "token"
   end
 
-  add_index "web_hooks", ["created_at", "id"], name: "index_web_hooks_on_created_at_and_id", using: :btree
   add_index "web_hooks", ["project_id"], name: "index_web_hooks_on_project_id", using: :btree
 
   add_foreign_key "personal_access_tokens", "users"