Skip to content

Cleaning the approval overriden method

Francisco Javier López requested to merge 198028-fj-cleaning-overriden into master

What does this MR do?

This MR tries to increase the performance of the MergeRequestsController#index (#198028 (closed)).

In this MR we try to refactor the ApprovalRuleLike#overriden? method.

SELECT "approval_merge_request_rules_users".* FROM "approval_merge_request_rules_users" WHERE "approval_merge_request_rules_users"."approval_merge_request_rule_id" IN ($1, $2)  [["approval_merge_request_rule_id", 3], ["approval_merge_request_rule_id", 4]]

SELECT "approval_merge_request_rules_groups"."id" AS t0_r0, "approval_merge_request_rules_groups"."approval_merge_request_rule_id" AS t0_r1, "approval_merge_request_rules_groups"."group_id" AS t0_r2, "namespaces"."id" AS t1_r0, "namespaces"."name" AS t1_r1, "namespaces"."path" AS t1_r2, "namespaces"."owner_id" AS t1_r3, "namespaces"."created_at" AS t1_r4, "namespaces"."updated_at" AS t1_r5, "namespaces"."type" AS t1_r6, "namespaces"."description" AS t1_r7, "namespaces"."avatar" AS t1_r8, "namespaces"."membership_lock" AS t1_r9, "namespaces"."share_with_group_lock" AS t1_r10, "namespaces"."visibility_level" AS t1_r11, "namespaces"."request_access_enabled" AS t1_r12, "namespaces"."ldap_sync_status" AS t1_r13, "namespaces"."ldap_sync_error" AS t1_r14, "namespaces"."ldap_sync_last_update_at" AS t1_r15, "namespaces"."ldap_sync_last_successful_update_at" AS t1_r16, "namespaces"."ldap_sync_last_sync_at" AS t1_r17, "namespaces"."description_html" AS t1_r18, "namespaces"."lfs_enabled" AS t1_r19, "namespaces"."parent_id" AS t1_r20, "namespaces"."shared_runners_minutes_limit" AS t1_r21, "namespaces"."repository_size_limit" AS t1_r22, "namespaces"."require_two_factor_authentication" AS t1_r23, "namespaces"."two_factor_grace_period" AS t1_r24, "namespaces"."cached_markdown_version" AS t1_r25, "namespaces"."project_creation_level" AS t1_r26, "namespaces"."runners_token" AS t1_r27, "namespaces"."file_template_project_id" AS t1_r28, "namespaces"."saml_discovery_token" AS t1_r29, "namespaces"."runners_token_encrypted" AS t1_r30, "namespaces"."custom_project_templates_group_id" AS t1_r31, "namespaces"."auto_devops_enabled" AS t1_r32, "namespaces"."extra_shared_runners_minutes_limit" AS t1_r33, "namespaces"."last_ci_minutes_notification_at" AS t1_r34, "namespaces"."last_ci_minutes_usage_notification_level" AS t1_r35, "namespaces"."subgroup_creation_level" AS t1_r36, "namespaces"."emails_disabled" AS t1_r37, "namespaces"."max_pages_size" AS t1_r38, "namespaces"."max_artifacts_size" AS t1_r39, "namespaces"."mentions_disabled" AS t1_r40, "namespaces"."default_branch_protection" AS t1_r41, "namespaces"."unlock_membership_to_ldap" AS t1_r42, "namespaces"."max_personal_access_token_lifetime" AS t1_r43, "namespaces"."push_rule_id" AS t1_r44, "namespaces"."shared_runners_enabled" AS t1_r45, "namespaces"."allow_descendants_override_disabled_shared_runners" AS t1_r46, "namespaces"."traversal_ids" AS t1_r47, "namespaces"."delayed_project_removal" AS t1_r48 FROM "approval_merge_request_rules_groups" LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "approval_merge_request_rules_groups"."group_id" AND "namespaces"."type" = $1 WHERE "namespaces"."type" = $2 AND "approval_merge_request_rules_groups"."approval_merge_request_rule_id" IN ($3, $4)  [["type", "Group"], ["type", "Group"], ["approval_merge_request_rule_id", 3], ["approval_merge_request_rule_id", 4]]

SELECT "approval_merge_request_rule_sources".* FROM "approval_merge_request_rule_sources" WHERE "approval_merge_request_rule_sources"."approval_merge_request_rule_id" IN ($1, $2)  [["approval_merge_request_rule_id", 3], ["approval_merge_request_rule_id", 4]]

SELECT "approval_project_rules".* FROM "approval_project_rules" WHERE "approval_project_rules"."id" = $1  [["id", 2]]

SELECT "approval_project_rules_users".* FROM "approval_project_rules_users" WHERE "approval_project_rules_users"."approval_project_rule_id" = $1  [["approval_project_rule_id", 2]]

SELECT "approval_project_rules_groups"."id" AS t0_r0, "approval_project_rules_groups"."approval_project_rule_id" AS t0_r1, "approval_project_rules_groups"."group_id" AS t0_r2, "namespaces"."id" AS t1_r0, "namespaces"."name" AS t1_r1, "namespaces"."path" AS t1_r2, "namespaces"."owner_id" AS t1_r3, "namespaces"."created_at" AS t1_r4, "namespaces"."updated_at" AS t1_r5, "namespaces"."type" AS t1_r6, "namespaces"."description" AS t1_r7, "namespaces"."avatar" AS t1_r8, "namespaces"."membership_lock" AS t1_r9, "namespaces"."share_with_group_lock" AS t1_r10, "namespaces"."visibility_level" AS t1_r11, "namespaces"."request_access_enabled" AS t1_r12, "namespaces"."ldap_sync_status" AS t1_r13, "namespaces"."ldap_sync_error" AS t1_r14, "namespaces"."ldap_sync_last_update_at" AS t1_r15, "namespaces"."ldap_sync_last_successful_update_at" AS t1_r16, "namespaces"."ldap_sync_last_sync_at" AS t1_r17, "namespaces"."description_html" AS t1_r18, "namespaces"."lfs_enabled" AS t1_r19, "namespaces"."parent_id" AS t1_r20, "namespaces"."shared_runners_minutes_limit" AS t1_r21, "namespaces"."repository_size_limit" AS t1_r22, "namespaces"."require_two_factor_authentication" AS t1_r23, "namespaces"."two_factor_grace_period" AS t1_r24, "namespaces"."cached_markdown_version" AS t1_r25, "namespaces"."project_creation_level" AS t1_r26, "namespaces"."runners_token" AS t1_r27, "namespaces"."file_template_project_id" AS t1_r28, "namespaces"."saml_discovery_token" AS t1_r29, "namespaces"."runners_token_encrypted" AS t1_r30, "namespaces"."custom_project_templates_group_id" AS t1_r31, "namespaces"."auto_devops_enabled" AS t1_r32, "namespaces"."extra_shared_runners_minutes_limit" AS t1_r33, "namespaces"."last_ci_minutes_notification_at" AS t1_r34, "namespaces"."last_ci_minutes_usage_notification_level" AS t1_r35, "namespaces"."subgroup_creation_level" AS t1_r36, "namespaces"."emails_disabled" AS t1_r37, "namespaces"."max_pages_size" AS t1_r38, "namespaces"."max_artifacts_size" AS t1_r39, "namespaces"."mentions_disabled" AS t1_r40, "namespaces"."default_branch_protection" AS t1_r41, "namespaces"."unlock_membership_to_ldap" AS t1_r42, "namespaces"."max_personal_access_token_lifetime" AS t1_r43, "namespaces"."push_rule_id" AS t1_r44, "namespaces"."shared_runners_enabled" AS t1_r45, "namespaces"."allow_descendants_override_disabled_shared_runners" AS t1_r46, "namespaces"."traversal_ids" AS t1_r47, "namespaces"."delayed_project_removal" AS t1_r48 FROM "approval_project_rules_groups" LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "approval_project_rules_groups"."group_id" AND "namespaces"."type" = $1 WHERE "namespaces"."type" = $2 AND "approval_project_rules_groups"."approval_project_rule_id" = $3  [["type", "Group"], ["type", "Group"], ["approval_project_rule_id", 2]]

SELECT "approval_project_rules_protected_branches".* FROM "approval_project_rules_protected_branches" WHERE "approval_project_rules_protected_branches"."approval_project_rule_id" = $1  [["approval_project_rule_id", 2]]
SELECT "approval_merge_request_rule_sources".* FROM "approval_merge_request_rule_sources" WHERE "approval_merge_request_rule_sources"."approval_merge_request_rule_id" IN ($1, $2)  [["approval_merge_request_rule_id", 3], ["approval_merge_request_rule_id", 4]]

SELECT "approval_project_rules".* FROM "approval_project_rules" WHERE "approval_project_rules"."id" = $1  [["id", 2]]

SELECT different_elements
FROM (
        (SELECT 1 AS different_elements
         FROM approval_merge_request_rules_groups
         WHERE approval_merge_request_rules_groups.approval_merge_request_rule_id = 23059811
           AND approval_merge_request_rules_groups.group_id NOT IN
             (SELECT group_id              
              FROM approval_project_rules_groups
              WHERE approval_project_rules_groups.approval_project_rule_id = 72703)
         LIMIT 1)
      UNION
        (SELECT 1 AS different_elements         
         FROM approval_merge_request_rules_users 
         WHERE approval_merge_request_rules_users.approval_merge_request_rule_id = 23059811
           AND approval_merge_request_rules_users.user_id NOT IN
             (SELECT user_id              
              FROM approval_project_rules_users
              WHERE approval_project_rules_users.approval_project_rule_id = 72703)
         LIMIT 1)) AS tmp_table

The total execution time per merge request approval rule doesn't change much because it was quite low, it goes from 2.7ms to 0.7 approx. Nevertheless, if we load 20 merge requests with several approval rules, this reduction can be meaningful. The query plan is https://explain.depesz.com/s/svHk

Does this MR meet the acceptance criteria?

Conformity

Refs #198028 (closed)

Edited by Francisco Javier López

Merge request reports