Skip to content

Add partial index for live ci_builds jobs

Gregory Stark requested to merge optimize-ci-builds-status into master

This index is for optimizing queries that look for all the live jobs for a given project. There are scopes in app/models/concerns/has_status.rb which restrict by various sets of statuses and this is intended to address the following ones:

    scope :created, -> { where(status: 'created') }
    scope :pending, -> { where(status: 'pending') }
    scope :alive, -> { where(status: [:created, :pending, :running]) }
    scope :created_or_pending, -> { where(status: [:created, :pending]) }
    scope :running_or_pending, -> { where(status: [:running, :pending]) }

The others are probably problems that need a bigger fix. No amount of indexing will fix them as the set of jobs in those states will grow indefinitely over time. However these "alive" jobs should always be a reasonable set of jobs per project so a simple index should be sufficient.

== 20180103152026 AddIndexOnCiBuildsStatus: migrating =========================
-- index_exists?(:ci_builds, [:project_id, :status], {:name=>"index_ci_builds_project_id_and_status_for_live_jobs_partial"})
   -> 0.0089s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- add_index(:ci_builds, [:project_id, :status], {:where=>"status in ('running','pending', 'created')", :name=>"index_ci_builds_project_id_and_status_for_live_jobs_partial", :algorithm=>:concurrently})
   -> 0.0421s
== 20180103152026 AddIndexOnCiBuildsStatus: migrated (0.0516s) ================

Database Checklist

When adding migrations:

  • Updated db/schema.rb
  • Added a down method so the migration can be reverted
  • Added the output of the migration(s) to the MR body
  • Added tests for the migration in spec/migrations if necessary (e.g. when migrating data)

When adding or modifying queries to improve performance:

  • Included data that shows the performance improvement, preferably in the form of a benchmark
  • Included the output of EXPLAIN (ANALYZE, BUFFERS) of the relevant queries

When adding foreign keys to existing tables:

  • Included a migration to remove orphaned rows in the source table before adding the foreign key
  • Removed any instances of dependent: ... that may no longer be necessary

When adding tables:

  • Ordered columns based on the Ordering Table Columns guidelines
  • Added foreign keys to any columns pointing to data in other tables
  • Added indexes for fields that are used in statements such as WHERE, ORDER BY, GROUP BY, and JOINs

When removing columns, tables, indexes or other structures:

  • Removed these in a post-deployment migration
  • Made sure the application no longer uses (or ignores) these structures

General Checklist

Edited by Gregory Stark

Merge request reports