Draft: Remove use of `missing` for ProtectedRef::for_role

What does this MR do and why?

Remove use of missing for ProtectedRef::for_role

ActiveRecord::missing uses LEFT OUTER JOIN to check for missing associations.

In this case we are checking for belongs_to associations so we already have the foreign keys in the table being queried. It is much more performant to use the foreign key fields instead.

This also removes the need to mark this query as a cross table join.

This module is included in ProtectedTag::CreateAccessLevel, ProtectedBranch::PushAccessLevel, ProtectedBranch::MergeAccessLevel, and ProtectedBranch::UnprotectAccessLevel. We index all the foreign_key fields for these tables so these plans will be similar for all.

Before change

Database Query

SELECT
  "protected_branch_push_access_levels".*
FROM
  "protected_branch_push_access_levels"
  LEFT OUTER JOIN "users" ON "users"."id" = "protected_branch_push_access_levels"."user_id"
  LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "protected_branch_push_access_levels"."group_id"
  AND "namespaces"."type" = 'Group'
  LEFT OUTER JOIN "keys" ON "keys"."id" = "protected_branch_push_access_levels"."deploy_key_id"
  AND "keys"."type" = 'DeployKey'
WHERE
  "users"."id" IS NULL
  AND "namespaces"."id" IS NULL
  AND "keys"."id" IS NULL

Database Plan

 Nested Loop Anti Join  (cost=1204.97..1395717.73 rows=1 width=40) (actual time=153617.869..211185.517 rows=31324549 loops=1)
   Buffers: shared hit=37127776 read=523841 dirtied=69152 written=27827
   I/O Timings: read=292383.091 write=10301.580
   ->  Nested Loop Anti Join  (cost=1204.54..1395717.19 rows=1 width=40) (actual time=153617.783..183832.212 rows=31331500 loops=1)
         Buffers: shared hit=37104703 read=519106 dirtied=68881 written=27643
         I/O Timings: read=281489.585 write=10222.525
         ->  Gather  (cost=1203.97..1395716.56 rows=1 width=40) (actual time=153617.612..163385.322 rows=31350012 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               Buffers: shared hit=37021415 read=518005 dirtied=68859 written=27553
               I/O Timings: read=279267.747 write=10194.935
               ->  Merge Anti Join  (cost=203.97..1394716.46 rows=1 width=40) (actual time=153722.743..169204.273 rows=10450004 loops=3)
                     Merge Cond: (protected_branch_push_access_levels.user_id = users.id)
                     Buffers: shared hit=37021415 read=518005 dirtied=68859 written=27553
                     I/O Timings: read=279267.747 write=10194.935
                     ->  Parallel Index Scan using index_protected_branch_push_access_levels_on_user_id on public.protected_branch_push_access_levels  (cost=0.44..954847.14 rows=13156035 width=40) (actual time=2.905..64413.137 rows=10525872 loops=3)
                           Buffers: shared hit=96965 read=393987 dirtied=10369 written=26288
                           I/O Timings: read=166221.786 write=9851.618
                     ->  Index Only Scan using users_pkey on public.users  (cost=0.43..366963.72 rows=15627484 width=4) (actual time=0.061..100022.922 rows=15560638 loops=3)
                           Heap Fetches: 3182068
                           Buffers: shared hit=36924450 read=124018 dirtied=58490 written=1265
                           I/O Timings: read=113045.961 write=343.317
         ->  Index Only Scan using index_namespaces_on_type_and_id on public.namespaces  (cost=0.56..0.60 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=31350012)
               Index Cond: ((namespaces.type = 'Group'::text) AND (namespaces.id = protected_branch_push_access_levels.group_id))
               Heap Fetches: 1712
               Buffers: shared hit=83288 read=1101 dirtied=21 written=90
               I/O Timings: read=2221.838 write=27.591
   ->  Index Scan using keys_pkey on public.keys  (cost=0.43..0.48 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=31331500)
         Index Cond: (keys.id = protected_branch_push_access_levels.deploy_key_id)
         Filter: ((keys.type)::text = 'DeployKey'::text)
         Rows Removed by Filter: 0
         Buffers: shared hit=23069 read=4735 dirtied=267 written=184
         I/O Timings: read=10893.506 write=79.055

After Change

Database Query

SELECT
  "protected_branch_push_access_levels".*
FROM
  "protected_branch_push_access_levels"
WHERE
  "protected_branch_push_access_levels"."user_id" IS NULL
  AND "protected_branch_push_access_levels"."group_id" IS NULL
  AND "protected_branch_push_access_levels"."deploy_key_id" IS NULL

Database Plan

 Index Scan using index_protected_branch_push_access_levels_on_group_id on public.protected_branch_push_access_levels  (cost=0.44..983262.58 rows=31320926 width=40) (actual time=1.776..94530.046 rows=31324311 loops=1)
   Index Cond: (protected_branch_push_access_levels.group_id IS NULL)
   Filter: ((protected_branch_push_access_levels.user_id IS NULL) AND (protected_branch_push_access_levels.deploy_key_id IS NULL))
   Rows Removed by Filter: 234552
   Buffers: shared hit=25718 read=252363 dirtied=20821 written=22125
   I/O Timings: read=76042.050 write=7076.600

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.