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

1. Seed VSA (make sure you're on ultimate plan)

   SEED_CYCLE_ANALYTICS=true SEED_VSA=true FILTER=cycle_analytics rake db:seed_fu

2. Navigate to the generated group: group > Analytics > Value Stream
3. Create a new value stream (use default stages)
4. 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')

5. Refresh the VSA page and click any stage (top)
6. 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.