Use in-operator optimization in VSA
What does this MR do and why?
Related issue: #345589 (closed)
This MR optimizes the aggregated Value Stream Analytics record loading queries (records
endpoint) using the in-operator optimization technique.
The change is not altering existing functionality, the aggregated VSA is currently behind a FF.
Implementation:
Originally the sorting was applied within the RecordsFetcher
class however, the in-operator optimization requires that we build the sorting earlier, in the BaseQueryBuilder
class. The built ActiveRecord
scope is only efficient when loading records, so using it as the base scope for other metrics (count, average, median) is not going to work well. For this reason, a new method was added to the BaseQueryBuilder
class: build_sorted_query
.
Queries
There are two cases:
- ORDER BY end_event_timestamp (full index coverage)
- Plan before the optimization: https://explain.depesz.com/s/cf2 (cached execution is about 20ms)
- Plan after the optimization: https://explain.depesz.com/s/5T45
- ORDER BY duration (partial index coverage)
- Plan before the optimization: https://explain.depesz.com/s/eSOE
- Plan after the optimization: https://explain.depesz.com/s/Tr2R
The duration query is a bit slower because the optimization uses generally more CPU, the number of accessed buffers are lower.
How to set up and validate locally
- Seed VSA (make sure you're on ultimate plan)
SEED_CYCLE_ANALYTICS=true SEED_VSA=true FILTER=cycle_analytics rake db:seed_fu
- Navigate to the generated group: group > Analytics > Value Stream
- Create a new value stream (use default stages)
- Start
rails c
Feature.enable(:use_vsa_aggregated_tables) Analytics::CycleAnalytics::GroupDataLoaderWorker.new.perform(Group.last, 'MergeRequest') Analytics::CycleAnalytics::GroupDataLoaderWorker.new.perform(Group.last, 'Issue')
- Refresh the VSA page and click any stage (top)
- You can verify if the new query is executed for the
records
endpoint.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.