Update composite pipelines index to include "id"
This updates the index on ci_pipelines (project_id, ref, status)
to include the id
column. This reduces the time to get the latest pipeline for a ref (e.g. "master") from up to 25-30 ms down to 1 millisecond.
See the commit message for more details.
Migration Output
== 20171121144800 CiPipelinesIndexOnProjectIdRefStatusId: migrating ===========
-- transaction_open?()
-> 0.0000s
-- execute("SET statement_timeout TO 0")
-> 0.0004s
-- add_index(:ci_pipelines, [:project_id, :ref, :status, :id], {:algorithm=>:concurrently})
-> 0.0183s
-- transaction_open?()
-> 0.0000s
-- select_one("SELECT current_setting('server_version_num') AS v")
-> 0.0004s
-- execute("SET statement_timeout TO 0")
-> 0.0002s
-- remove_index(:ci_pipelines, {:algorithm=>:concurrently, :column=>[:project_id, :ref, :status]})
-> 0.0109s
== 20171121144800 CiPipelinesIndexOnProjectIdRefStatusId: migrated (0.0305s) ==
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 the execution time of the migration(s) to the MR body -
Made sure the migration won't interfere with a running GitLab cluster, for example by disabling transactions for long running migrations
When adding indexes:
-
Described the need for these indexes in the MR body -
Made sure existing indexes can not be reused instead
General Checklist
-
Changelog entry added, if necessary - Review
-
Has been reviewed by Backend -
Has been reviewed by Database
-
-
Conform by the merge request performance guides -
Conform by the style guides -
Squashed related commits together
Edited by Yorick Peterse