Add order_by updated_at to Pipelines API
What does this MR do?
This enhances the List Project Pipelines API endpoint to add an order_by
option for updated_at
. (This is the first timestamp option - there is no existing order_by
option for created_at
.) It includes a migration to concurrently add an index on ci_pipelines#updated_at
to ensure adequate performance.
This is motivated by a specific customer request, see #33099 (closed), and was preceded by !17911 (merged).
This MR is very similar to !19658 (merged) (which does the same thing for the Deployments API).
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
The primary risk is probably that the migration to index the ci_pipelines#updated_at
column will take quite a long time, even if it is concurrent/non-blocking.
Query
Based on customer feedback, we believe a status
parameter will usually be given. In that case the query executed by the Pipelines API endpoint looks like:
SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" = 1 AND "ci_pipelines"."status" = 'success' ORDER BY "ci_pipelines"."updated_at" DESC;
Without the status
parameter, there is no status
condition in the query. Also, note the default ORDER BY
clause (which is desirable).
Query plan BEFORE adding index:
Sort (cost=312.39..313.04 rows=261 width=296) (actual time=0.201..0.201 rows=0
loops=1)
Sort Key: id DESC
Sort Method: quicksort Memory: 25kB
-> Index Scan using index_ci_pipelines_on_project_id_and_status_and_config_s
ource on ci_pipelines (cost=0.57..301.91 rows=261 width=296) (actual time=0.170
..0.170 rows=0 loops=1)
Index Cond: ((project_id = 1) AND ((status)::text = 'success'::text))
Planning time: 4.714 ms
Execution time: 0.264 ms
(7 rows)
Adding index: "Duration: 8.350 min" in Slack#database-lab.
Query plan AFTER adding index: See comment: !19886 (comment 247053130)
2019-11-14 edited to add "Query" section. 2019-11-19 edited to add "after" query.