Add indices to approval_policy_rules and approval rules tables

What does this MR do and why?

This MR updates the the query to delete approval_project_rules and approval_merge_request_rules for a security policy by adding security_orchestration_policy_configuration_id to reduce the number of rows filtered by the query. To improve the query, additional index is also created.

This MR also adds an additional index for the query introduced in !160718 (merged) and related to follow-up: #485021 (closed)

Database queries

Security::ApprovalPolicyRule.by_policy_rule_index

SELECT
    "approval_policy_rules".* 
FROM
    "approval_policy_rules" 
INNER JOIN
    "security_policies" "security_policy" 
        ON "security_policy"."id" = "approval_policy_rules"."security_policy_id" 
WHERE
    "approval_policy_rules"."rule_index" = 0 
    AND "security_policy"."security_orchestration_policy_configuration_id" = 1098273
    AND "security_policy"."policy_index" = 0

Query Plan

Before Index

 Nested Loop  (cost=0.28..15.22 rows=3 width=245) (actual time=1.674..1.680 rows=1 loops=1)
   Buffers: shared hit=13 read=2 dirtied=2
   I/O Timings: read=1.580 write=0.000
   ->  Index Scan using index_security_policies_on_unique_config_type_policy_index on public.security_policies security_policy  (cost=0.14..6.86 rows=5 width=8) (actual time=1.640..1.643 rows=2 loops=1)
         Index Cond: ((security_policy.security_orchestration_policy_configuration_id = 1098273) AND (security_policy.policy_index = 0))
         Buffers: shared hit=7 read=2 dirtied=2
         I/O Timings: read=1.580 write=0.000
   ->  Index Scan using index_approval_policy_rules_on_unique_policy_rule_index on public.approval_policy_rules  (cost=0.14..1.66 rows=1 width=245) (actual time=0.015..0.015 rows=0 loops=2)
         Index Cond: ((approval_policy_rules.security_policy_id = security_policy.id) AND (approval_policy_rules.rule_index = 0))
         Buffers: shared hit=6
         I/O Timings: read=0.000 write=0.000

After Index

 Nested Loop  (cost=0.28..13.83 rows=3 width=245) (actual time=0.079..0.082 rows=1 loops=1)
   Buffers: shared hit=10 read=1
   I/O Timings: read=0.041 write=0.000
   ->  Index Scan using idx_security_policies_config_id_policy_index on public.security_policies security_policy  (cost=0.14..5.62 rows=4 width=8) (actual time=0.071..0.072 rows=2 loops=1)
         Index Cond: ((security_policy.security_orchestration_policy_configuration_id = 1098273) AND (security_policy.policy_index = 0))
         Buffers: shared hit=7 read=1
         I/O Timings: read=0.041 write=0.000
   ->  Index Scan using idx_approval_policy_rules_rule_index_security_policy_id on public.approval_policy_rules  (cost=0.14..2.04 rows=1 width=245) (actual time=0.003..0.003 rows=0 loops=2)
         Index Cond: ((approval_policy_rules.rule_index = 0) AND (approval_policy_rules.security_policy_id = security_policy.id))
         Buffers: shared hit=3
         I/O Timings: read=0.000 write=0.000

ApprovalProjectRule

Before

SELECT
        "approval_project_rules".* 
    FROM
        "approval_project_rules" 
    WHERE
        "approval_project_rules"."approval_policy_rule_id" IN (
            SELECT
                "approval_policy_rules"."id" 
            FROM
                "approval_policy_rules" 
            WHERE
                "approval_policy_rules"."security_policy_id" = 10 
                AND "approval_policy_rules"."id" >= 9
        )

Query plan

 Merge Join  (cost=3.60..436.46 rows=23076 width=205) (actual time=1468.374..1468.376 rows=0 loops=1)
   Merge Cond: (approval_project_rules.approval_policy_rule_id = approval_policy_rules.id)
   Buffers: shared hit=322 read=2025 dirtied=84
   I/O Timings: read=1430.455 write=0.000
   ->  Index Scan using index_approval_project_rules_on_approval_policy_rule_id on public.approval_project_rules  (cost=0.43..95044.88 rows=1246113 width=205) (actual time=0.005..1465.226 rows=5218 loops=1)
         Buffers: shared hit=318 read=2024 dirtied=84
         I/O Timings: read=1429.657 write=0.000
   ->  Sort  (cost=3.17..3.17 rows=1 width=8) (actual time=0.853..0.855 rows=1 loops=1)
         Sort Key: approval_policy_rules.id
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=4 read=1
         I/O Timings: read=0.798 write=0.000
         ->  Index Scan using index_approval_policy_rules_on_unique_policy_rule_index on public.approval_policy_rules  (cost=0.14..3.16 rows=1 width=8) (actual time=0.833..0.835 rows=1 loops=1)
               Index Cond: (approval_policy_rules.security_policy_id = 10)
               Filter: (approval_policy_rules.id >= 9)
               Rows Removed by Filter: 0
               Buffers: shared hit=4 read=1
               I/O Timings: read=0.798 write=0.000

After

SELECT
        "approval_project_rules".* 
    FROM
        "approval_project_rules" 
    WHERE
        "approval_project_rules"."security_orchestration_policy_configuration_id" = 1098273
        AND "approval_project_rules"."approval_policy_rule_id" IN (
            SELECT
                "approval_policy_rules"."id" 
            FROM
                "approval_policy_rules" 
            WHERE
                "approval_policy_rules"."security_policy_id" = 10 
                AND "approval_policy_rules"."id" >= 9
        )

Query Plan

 Merge Join  (cost=0.57..5.24 rows=1 width=205) (actual time=0.136..0.137 rows=0 loops=1)
   Merge Cond: (approval_project_rules.approval_policy_rule_id = approval_policy_rules.id)
   Buffers: shared hit=3 read=3
   I/O Timings: read=0.101 write=0.000
   ->  Index Scan using idx_approval_project_rules_on_config_id_and_policy_rule_id on public.approval_project_rules  (cost=0.43..8.64 rows=5 width=205) (actual time=0.135..0.135 rows=0 loops=1)
         Index Cond: (approval_project_rules.security_orchestration_policy_configuration_id = 1098273)
         Buffers: shared hit=3 read=3
         I/O Timings: read=0.101 write=0.000
   ->  Index Only Scan using idx_approval_policy_rules_security_policy_id_id on public.approval_policy_rules  (cost=0.14..3.16 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
         Index Cond: ((approval_policy_rules.security_policy_id = 10) AND (approval_policy_rules.id >= 9))
         Heap Fetches: 0
         I/O Timings: read=0.000 write=0.000

ApprovalMergeRequestRule

Before

SELECT
    "approval_merge_request_rules"."id" 
FROM
    "approval_merge_request_rules" 
INNER JOIN
    "merge_requests" 
        ON "merge_requests"."id" = "approval_merge_request_rules"."merge_request_id" 
WHERE
    "merge_requests"."state_id" != 3 
    AND "approval_merge_request_rules"."approval_policy_rule_id" IN (
        SELECT
            "approval_policy_rules"."id" 
        FROM
            "approval_policy_rules" 
        WHERE
            "approval_policy_rules"."security_policy_id" = 10
            AND "approval_policy_rules"."id" >= 9
    )

Query Plan

 Merge Join  (cost=1004.32..3548.92 rows=274167 width=8) (actual time=354.326..374.894 rows=0 loops=1)
   Merge Cond: (approval_merge_request_rules.approval_policy_rule_id = approval_policy_rules.id)
   Buffers: shared hit=25875 read=1014 dirtied=6
   I/O Timings: read=883.402 write=0.000
   ->  Gather Merge  (cost=1001.15..42385519.58 rows=14804992 width=16) (actual time=19.914..373.898 rows=5179 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=25872 read=1012 dirtied=6
         I/O Timings: read=883.285 write=0.000
         ->  Nested Loop  (cost=1.13..40675656.13 rows=6168747 width=16) (actual time=13.679..310.402 rows=1728 loops=3)
               Buffers: shared hit=25872 read=1012 dirtied=6
               I/O Timings: read=883.285 write=0.000
               ->  Parallel Index Scan using index_approval_merge_request_rules_on_approval_policy_rule_id on public.approval_merge_request_rules  (cost=0.57..9265445.43 rows=53605540 width=20) (actual time=6.465..208.955 rows=1747 loops=3)
                     Buffers: shared hit=601 read=775 dirtied=3
                     I/O Timings: read=614.698 write=0.000
               ->  Index Only Scan using idx_merge_requests_on_unmerged_state_id on public.merge_requests  (cost=0.56..0.59 rows=1 width=4) (actual time=0.057..0.057 rows=1 loops=5241)
                     Index Cond: (merge_requests.id = approval_merge_request_rules.merge_request_id)
                     Heap Fetches: 286
                     Buffers: shared hit=25271 read=237 dirtied=3
                     I/O Timings: read=268.588 write=0.000
   ->  Sort  (cost=3.17..3.17 rows=1 width=8) (actual time=0.264..0.268 rows=1 loops=1)
         Sort Key: approval_policy_rules.id
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=3 read=2
         I/O Timings: read=0.117 write=0.000
         ->  Index Scan using index_approval_policy_rules_on_unique_policy_rule_index on public.approval_policy_rules  (cost=0.14..3.16 rows=1 width=8) (actual time=0.213..0.214 rows=1 loops=1)
               Index Cond: (approval_policy_rules.security_policy_id = 10)
               Filter: (approval_policy_rules.id >= 9)
               Rows Removed by Filter: 0
               Buffers: shared hit=3 read=2
               I/O Timings: read=0.117 write=0.000

After

SELECT
    "approval_merge_request_rules"."id" 
FROM
    "approval_merge_request_rules" 
INNER JOIN
    "merge_requests" 
        ON "merge_requests"."id" = "approval_merge_request_rules"."merge_request_id" 
WHERE
    "merge_requests"."state_id" != 3 
    AND "approval_merge_request_rules"."security_orchestration_policy_configuration_id" = 1098273
    AND "approval_merge_request_rules"."approval_policy_rule_id" IN (
        SELECT
            "approval_policy_rules"."id" 
        FROM
            "approval_policy_rules" 
        WHERE
            "approval_policy_rules"."security_policy_id" = 10
            AND "approval_policy_rules"."id" >= 9
    )

Query Plan

 Nested Loop  (cost=1.27..28.04 rows=1 width=8) (actual time=0.266..0.268 rows=0 loops=1)
   Buffers: shared hit=3 read=4
   I/O Timings: read=0.211 write=0.000
   ->  Merge Join  (cost=0.71..4.94 rows=11 width=12) (actual time=0.266..0.267 rows=0 loops=1)
         Merge Cond: (approval_merge_request_rules.approval_policy_rule_id = approval_policy_rules.id)
         Buffers: shared hit=3 read=4
         I/O Timings: read=0.211 write=0.000
         ->  Index Scan using idx_approval_mr_rules_on_config_id_and_policy_rule_id on public.approval_merge_request_rules  (cost=0.57..778.10 rows=652 width=20) (actual time=0.247..0.248 rows=0 loops=1)
               Index Cond: (approval_merge_request_rules.security_orchestration_policy_configuration_id = 1098273)
               Buffers: shared hit=3 read=4
               I/O Timings: read=0.211 write=0.000
         ->  Index Only Scan using idx_approval_policy_rules_security_policy_id_id on public.approval_policy_rules  (cost=0.14..3.16 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
               Index Cond: ((approval_policy_rules.security_policy_id = 10) AND (approval_policy_rules.id >= 9))
               Heap Fetches: 0
               I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using idx_merge_requests_on_unmerged_state_id on public.merge_requests  (cost=0.56..2.10 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
         Index Cond: (merge_requests.id = approval_merge_request_rules.merge_request_id)
         Heap Fetches: 0
         I/O Timings: read=0.000 write=0.000

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.