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.