Optimize queries in FrameworkEvaluationSchedulerWorker

What does this MR do and why?

We were facing query timeouts for the FrameworkEvaluationSchedulerWorker as mentioned in the issue #584912 (closed).

Key changes:

• New optimized query path: Adds perform_optimized method that fetches framework IDs first, then batch-processes framework-to-project mappings to reduce database queries
• Framework model enhancement: Adds active_framework_ids class method and with_active_controls_optimized scope to efficiently retrieve active frameworks
• Project settings helper: Adds framework_project_mappings method to batch-fetch framework-project relationships in a single query
• Feature flag: Introduces optimize_framework_worker_query (disabled by default) to toggle between legacy and optimized implementations
• Preserved behavior: The legacy perform_legacy method maintains the original logic for safe rollback

The optimization reduces N+1 queries by pre-loading framework-project mappings instead of querying them individually for each framework. Tests have been updated to verify both code paths produce identical results.

References

#584912 (closed)

Performance comparison

Old query

Click to expand query

SELECT 
  DISTINCT "compliance_management_frameworks"."id" AS t0_r0, 
  "compliance_management_frameworks"."name" AS t0_r1, 
  "compliance_management_frameworks"."description" AS t0_r2, 
  "compliance_management_frameworks"."color" AS t0_r3, 
  "compliance_management_frameworks"."namespace_id" AS t0_r4, 
  "compliance_management_frameworks"."pipeline_configuration_full_path" AS t0_r5, 
  "compliance_management_frameworks"."created_at" AS t0_r6, 
  "compliance_management_frameworks"."updated_at" AS t0_r7, 
  "compliance_management_frameworks"."source_id" AS t0_r8, 
  "compliance_requirements"."id" AS t1_r0, 
  "compliance_requirements"."created_at" AS t1_r1, 
  "compliance_requirements"."updated_at" AS t1_r2, 
  "compliance_requirements"."framework_id" AS t1_r3, 
  "compliance_requirements"."namespace_id" AS t1_r4, 
  "compliance_requirements"."name" AS t1_r5, 
  "compliance_requirements"."description" AS t1_r6, 
  "compliance_requirements_controls"."id" AS t2_r0, 
  "compliance_requirements_controls"."created_at" AS t2_r1, 
  "compliance_requirements_controls"."updated_at" AS t2_r2, 
  "compliance_requirements_controls"."compliance_requirement_id" AS t2_r3, 
  "compliance_requirements_controls"."namespace_id" AS t2_r4, 
  "compliance_requirements_controls"."name" AS t2_r5, 
  "compliance_requirements_controls"."control_type" AS t2_r6, 
  "compliance_requirements_controls"."expression" AS t2_r7, 
  "compliance_requirements_controls"."encrypted_secret_token" AS t2_r8, 
  "compliance_requirements_controls"."encrypted_secret_token_iv" AS t2_r9, 
  "compliance_requirements_controls"."external_url" AS t2_r10, 
  "compliance_requirements_controls"."external_control_name" AS t2_r11, 
  "compliance_requirements_controls"."ping_enabled" AS t2_r12, 
  "project_compliance_framework_settings"."project_id" AS t3_r0, 
  "project_compliance_framework_settings"."framework_id" AS t3_r1, 
  "project_compliance_framework_settings"."id" AS t3_r2, 
  "project_compliance_framework_settings"."created_at" AS t3_r3, 
  "projects"."id" AS t4_r0, 
  "projects"."name" AS t4_r1, 
  "projects"."path" AS t4_r2, 
  "projects"."description" AS t4_r3, 
  "projects"."created_at" AS t4_r4, 
  "projects"."updated_at" AS t4_r5, 
  "projects"."creator_id" AS t4_r6, 
  "projects"."namespace_id" AS t4_r7, 
  "projects"."last_activity_at" AS t4_r8, 
  "projects"."import_url" AS t4_r9, 
  "projects"."visibility_level" AS t4_r10, 
  "projects"."archived" AS t4_r11, 
  "projects"."avatar" AS t4_r12, 
  "projects"."merge_requests_template" AS t4_r13, 
  "projects"."star_count" AS t4_r14, 
  "projects"."merge_requests_rebase_enabled" AS t4_r15, 
  "projects"."import_type" AS t4_r16, 
  "projects"."import_source" AS t4_r17, 
  "projects"."approvals_before_merge" AS t4_r18, 
  "projects"."reset_approvals_on_push" AS t4_r19, 
  "projects"."merge_requests_ff_only_enabled" AS t4_r20, 
  "projects"."issues_template" AS t4_r21, 
  "projects"."mirror" AS t4_r22, 
  "projects"."mirror_last_update_at" AS t4_r23, 
  "projects"."mirror_last_successful_update_at" AS t4_r24, 
  "projects"."mirror_user_id" AS t4_r25, 
  "projects"."shared_runners_enabled" AS t4_r26, 
  "projects"."runners_token" AS t4_r27, 
  "projects"."build_allow_git_fetch" AS t4_r28, 
  "projects"."build_timeout" AS t4_r29, 
  "projects"."mirror_trigger_builds" AS t4_r30, 
  "projects"."pending_delete" AS t4_r31, 
  "projects"."public_builds" AS t4_r32, 
  "projects"."last_repository_check_failed" AS t4_r33, 
  "projects"."last_repository_check_at" AS t4_r34, 
  "projects"."only_allow_merge_if_pipeline_succeeds" AS t4_r35, 
  "projects"."has_external_issue_tracker" AS t4_r36, 
  "projects"."repository_storage" AS t4_r37, 
  "projects"."repository_read_only" AS t4_r38, 
  "projects"."request_access_enabled" AS t4_r39, 
  "projects"."has_external_wiki" AS t4_r40, 
  "projects"."ci_config_path" AS t4_r41, 
  "projects"."lfs_enabled" AS t4_r42, 
  "projects"."description_html" AS t4_r43, 
  "projects"."only_allow_merge_if_all_discussions_are_resolved" AS t4_r44, 
  "projects"."repository_size_limit" AS t4_r45, 
  "projects"."printing_merge_request_link_enabled" AS t4_r46, 
  "projects"."auto_cancel_pending_pipelines" AS t4_r47, 
  "projects"."service_desk_enabled" AS t4_r48, 
  "projects"."cached_markdown_version" AS t4_r49, 
  "projects"."delete_error" AS t4_r50, 
  "projects"."last_repository_updated_at" AS t4_r51, 
  "projects"."disable_overriding_approvers_per_merge_request" AS t4_r52, 
  "projects"."storage_version" AS t4_r53, 
  "projects"."resolve_outdated_diff_discussions" AS t4_r54, 
  "projects"."remote_mirror_available_overridden" AS t4_r55, 
  "projects"."only_mirror_protected_branches" AS t4_r56, 
  "projects"."pull_mirror_available_overridden" AS t4_r57, 
  "projects"."jobs_cache_index" AS t4_r58, 
  "projects"."external_authorization_classification_label" AS t4_r59, 
  "projects"."mirror_overwrites_diverged_branches" AS t4_r60, 
  "projects"."pages_https_only" AS t4_r61, 
  "projects"."external_webhook_token" AS t4_r62, 
  "projects"."packages_enabled" AS t4_r63, 
  "projects"."merge_requests_author_approval" AS t4_r64, 
  "projects"."pool_repository_id" AS t4_r65, 
  "projects"."runners_token_encrypted" AS t4_r66, 
  "projects"."bfg_object_map" AS t4_r67, 
  "projects"."detected_repository_languages" AS t4_r68, 
  "projects"."merge_requests_disable_committers_approval" AS t4_r69, 
  "projects"."require_password_to_approve" AS t4_r70, 
  "projects"."max_pages_size" AS t4_r71, 
  "projects"."max_artifacts_size" AS t4_r72, 
  "projects"."pull_mirror_branch_prefix" AS t4_r73, 
  "projects"."remove_source_branch_after_merge" AS t4_r74, 
  "projects"."marked_for_deletion_at" AS t4_r75, 
  "projects"."marked_for_deletion_by_user_id" AS t4_r76, 
  "projects"."autoclose_referenced_issues" AS t4_r77, 
  "projects"."suggestion_commit_message" AS t4_r78, 
  "projects"."project_namespace_id" AS t4_r79, 
  "projects"."hidden" AS t4_r80, 
  "projects"."organization_id" AS t4_r81 
FROM 
  "compliance_management_frameworks" 
  INNER JOIN "compliance_requirements" ON "compliance_requirements"."framework_id" = "compliance_management_frameworks"."id" 
  INNER JOIN "compliance_requirements_controls" ON "compliance_requirements_controls"."compliance_requirement_id" = "compliance_requirements"."id" 
  INNER JOIN "project_compliance_framework_settings" ON "project_compliance_framework_settings"."framework_id" = "compliance_management_frameworks"."id" 
  LEFT OUTER JOIN "projects" ON "projects"."id" = "project_compliance_framework_settings"."project_id" 
WHERE 
  "compliance_management_frameworks"."id" >= 186 
  AND "compliance_management_frameworks"."id" < 2022024;

Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/46800/commands/142288 Total time: 1.686 s

New query:

Query 1: To fetch all the framework ids, this will run in batches of 1000, so there could be multiple runs of this

Click to expand query

SELECT 
  "compliance_management_frameworks"."id" 
FROM 
  "compliance_management_frameworks" 
WHERE 
  (
    EXISTS (
      SELECT 
        1 
      FROM 
        compliance_requirements cr 
        INNER JOIN compliance_requirements_controls crc ON crc.compliance_requirement_id = cr.id 
      WHERE 
        cr.framework_id = compliance_management_frameworks.id
    ) 
    AND EXISTS (
      SELECT 
        1 
      FROM 
        project_compliance_framework_settings pcfs 
      WHERE 
        pcfs.framework_id = compliance_management_frameworks.id
    )
  );

Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/46977/commands/142617 Total time: 21 ms, currently such frameworks are less than 1000 so this applies for batch as well.

Query 2: To fetch the project ids for the frameworks list, this will run multiple times as the frameworks list size will be limited to 100.

Click to expand query

SELECT 
  "project_compliance_framework_settings"."framework_id", 
  "project_compliance_framework_settings"."project_id" 
FROM 
  "project_compliance_framework_settings" 
WHERE 
  "project_compliance_framework_settings"."framework_id" IN (
    < list of framework ids, eg : 1, 2, 45, 67....>
  );

Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/46800/commands/142292 Total time: 9.193 ms, considering 1000 such frameworks, the net time will be around 91.93 ms approximately, assuming (1000/100 = )10 runs of this.

Conclusion:

The old query is taking around 1.686 s and the new query is approximately taking 113 ms. This is much better performance to existing one.

Query plans

Query plan 1:

Location: framework.rb scope with_active_controls_optimized

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/46800/commands/142294

Query plan 2:

Location: framework.rb method active_framework_ids

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/46800/commands/142295

Query plan 3:

Location: ee/app/models/compliance_management/compliance_framework/project_settings.rb method framework_project_mappings

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/46800/commands/142292

How to set up and validate locally

Run the rspecs.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #584912 (closed)