Fix consistency worker order by clause
What does this MR do and why?
The VSA consistency worker is failing with a timeout for a while now for a few larger groups.
Problem: The index which would be used for iterating over the records is not used for the queries.
The old query had 3 ORDER BY
columns: end_event_timestamp
, issue or mr id
, start_event_timestamp
. The index for the consistency check doesn't not include the start_event_timestamp
column:
"index_mr_stage_events_for_consistency_check" btree (stage_event_hash_id, group_id, end_event_timestamp, merge_request_id)
To iterate over the data, we actually don't need the 3rd ORDER BY
column (the column was included because another feature needed it), this MR introduces a separate AR scope for the consistency worker that matches the index.
- Query plan before: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26069/commands/82086
- Query plan after: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26069/commands/82089
Note: the query uses the in-operator optimization technique
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
Before | After |
---|---|
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.