Add partial index for live ci_builds jobsoptimize-ci-builds-status

2 files changed, 29 insertions, 0 deletions

diff --git a/db/migrate/20180103152026_add_index_on_ci_builds_status.rb b/db/migrate/20180103152026_add_index_on_ci_builds_status.rb
new file mode 100644
index 00000000000..06cda158ea8
--- /dev/null
+++ b/db/migrate/20180103152026_add_index_on_ci_builds_status.rb
@@ -0,0 +1,28 @@
+# See http://doc.gitlab.com/ce/development/migration_style_guide.html
+# for more information on how to write migrations for GitLab.
+
+class AddIndexOnCiBuildsStatus < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  # Set this constant to true if this migration requires downtime.
+  DOWNTIME = false
+
+  disable_ddl_transaction!
+
+  NEW_INDEX_NAME = 'index_ci_builds_project_id_and_status_for_live_jobs_partial'
+
+  def up
+    return if index_exists?(:ci_builds, [:project_id, :status], name: NEW_INDEX_NAME)
+
+    add_concurrent_index(
+      :ci_builds,
+      [:project_id, :status],
+      where: "status in ('running','pending', 'created')",
+      name: NEW_INDEX_NAME
+    )
+  end
+
+  def down
+    remove_concurrent_index(:ci_builds, nil, name: NEW_INDEX_NAME)
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index 6b43fc8403c..f539f4b6e18 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -300,6 +300,7 @@ ActiveRecord::Schema.define(version: 20180208183958) 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", ["project_id", "id"], name: "index_ci_builds_on_project_id_and_id", using: :btree
+  add_index "ci_builds", ["project_id", "status"], name: "index_ci_builds_project_id_and_status_for_live_jobs_partial", where: "((status)::text = ANY ((ARRAY['running'::character varying, 'pending'::character varying, 'created'::character varying])::text[]))", using: :btree
   add_index "ci_builds", ["protected"], name: "index_ci_builds_on_protected", using: :btree
   add_index "ci_builds", ["runner_id"], name: "index_ci_builds_on_runner_id", using: :btree
   add_index "ci_builds", ["stage_id"], name: "index_ci_builds_on_stage_id", using: :btree