Group#self_and_descendants -> #all_projects in GroupPackagesFinder
What does this MR do?
We have found serious performance issues when using
#self_and_descendants in IN (...) queries. We have already updated
#all_projects so that it does not use this anymore
!56078 (merged) and as such
we want to change other parts of the code to just use #all_projects
when it makes sense to keep this performance benefit.
It also happens that this simplifies the code anyway since we actually already wanted the list of projects in the group.
See a similar MR in !56415 (merged)
Query plans
There are 3 different ways this is used so I'll leave Before and After query plans for each.
(1) ::Packages::GroupPackagesFinder.new(current_user, group, package_type: :npm).execute
Before
Query
SELECT "packages_packages".*
FROM "packages_packages"
WHERE "packages_packages"."project_id" IN
(
SELECT "projects"."id"
FROM "projects"
LEFT JOIN project_features
ON projects.id = project_features.project_id
WHERE "projects"."namespace_id" IN (WITH recursive "base_and_descendants" AS (
(
SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(
SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (
EXISTS
(
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 120073
AND (
project_authorizations.project_id = projects.id)
AND (
project_authorizations.access_level >= 20))
OR projects.visibility_level IN (10,20))
AND (
"project_features"."repository_access_level" IS NULL
OR "project_features"."repository_access_level" IN (20,30)
OR (
"project_features"."repository_access_level" = 10
AND EXISTS
(
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 120073
AND (
project_authorizations.project_id = projects.id)
AND (
project_authorizations.access_level >= 10)))))
AND (
"packages_packages"."package_type" != 4
OR "packages_packages"."name" != 'NuGet.Temporary.Package')
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."package_type" = 2
AND "packages_packages"."status" = 0
ORDER BY created_at DESCPlan
Sort (cost=30054.14..30093.18 rows=15617 width=84) (actual time=32.281..32.295 rows=40 loops=1)
Sort Key: packages_packages.created_at DESC
Sort Method: quicksort Memory: 30kB
Buffers: shared hit=21642
-> Nested Loop (cost=27528.31..28966.35 rows=15617 width=84) (actual time=29.051..32.202 rows=40 loops=1)
Buffers: shared hit=21639
-> HashAggregate (cost=27527.89..27547.39 rows=1950 width=4) (actual time=27.895..28.275 rows=1276 loops=1)
Group Key: projects.id
Buffers: shared hit=17783
-> Nested Loop Left Join (cost=1582.57..27523.01 rows=1950 width=4) (actual time=3.878..27.327 rows=1276 loops=1)
Filter: ((project_features.repository_access_level IS NULL) OR (project_features.repository_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.repository_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
Rows Removed by Filter: 22
Buffers: shared hit=17783
-> Nested Loop (cost=1582.13..19335.38 rows=1996 width=4) (actual time=3.858..13.423 rows=1298 loops=1)
Buffers: shared hit=9377
-> HashAggregate (cost=1581.69..1583.50 rows=181 width=4) (actual time=3.762..3.842 rows=234 loops=1)
Group Key: namespaces.id
Buffers: shared hit=1166
-> CTE Scan on base_and_descendants namespaces (cost=1575.81..1579.43 rows=181 width=4) (actual time=0.076..3.663 rows=234 loops=1)
Buffers: shared hit=1166
CTE base_and_descendants
-> Recursive Union (cost=0.43..1575.81 rows=181 width=344) (actual time=0.072..3.234 rows=234 loops=1)
Buffers: shared hit=1166
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=0.058..0.059 rows=1 loops=1)
Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = 9970))
Buffers: shared hit=4
-> Nested Loop (cost=0.56..156.87 rows=18 width=344) (actual time=0.032..0.417 rows=39 loops=6)
Buffers: shared hit=1162
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.005 rows=39 loops=6)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..15.65 rows=2 width=344) (actual time=0.007..0.010 rows=1 loops=234)
Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
Filter: ((namespaces_2.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=1162
-> Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.44..97.97 rows=11 width=8) (actual time=0.013..0.040 rows=6 loops=234)
Index Cond: (projects.namespace_id = namespaces.id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=8211
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1298)
Index Cond: ((project_authorizations.user_id = 120073) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 266
Buffers: shared hit=6205
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..887.19 rows=3108 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 120073) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.44..0.49 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1298)
Index Cond: (projects.id = project_features.project_id)
Buffers: shared hit=5194
SubPlan 3
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_2 (cost=0.57..3.59 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_2.user_id = 120073) AND (project_authorizations_2.project_id = projects.id) AND (project_authorizations_2.access_level >= 10))
Heap Fetches: 0
SubPlan 4
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_3 (cost=0.57..1078.82 rows=3842 width=4) (actual time=0.032..3.702 rows=5259 loops=1)
Index Cond: ((project_authorizations_3.user_id = 120073) AND (project_authorizations_3.access_level >= 10))
Heap Fetches: 1222
Buffers: shared hit=3212
-> Index Scan using index_packages_packages_on_project_id_and_package_type on public.packages_packages (cost=0.42..0.65 rows=8 width=84) (actual time=0.003..0.003 rows=0 loops=1276)
Index Cond: ((packages_packages.project_id = projects.id) AND (packages_packages.package_type = 2))
Filter: ((packages_packages.version IS NOT NULL) AND (packages_packages.status = 0))
Rows Removed by Filter: 0
Buffers: shared hit=3856After
Query
SELECT "packages_packages".*
FROM "packages_packages"
WHERE "packages_packages"."project_id" IN
(
SELECT "projects"."id"
FROM "projects"
INNER JOIN (WITH recursive "base_and_descendants" AS (
(
SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(
SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))SELECT "id"
FROM "base_and_descendants" AS "namespaces") namespaces
ON namespaces.id=projects.namespace_id
LEFT JOIN project_features
ON projects.id = project_features.project_id
WHERE (
EXISTS
(
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 120073
AND (
project_authorizations.project_id = projects.id)
AND (
project_authorizations.access_level >= 20))
OR projects.visibility_level IN (10,20))
AND (
"project_features"."repository_access_level" IS NULL
OR "project_features"."repository_access_level" IN (20,30)
OR (
"project_features"."repository_access_level" = 10
AND EXISTS
(
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 120073
AND (
project_authorizations.project_id = projects.id)
AND (
project_authorizations.access_level >= 10)))))
AND (
"packages_packages"."package_type" != 4
OR "packages_packages"."name" != 'NuGet.Temporary.Package')
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."package_type" = 2
AND "packages_packages"."status" = 0
ORDER BY created_at DESCPlan
Sort (cost=30051.88..30090.92 rows=15617 width=84) (actual time=38.581..38.600 rows=40 loops=1)
Sort Key: packages_packages.created_at DESC
Sort Method: quicksort Memory: 30kB
Buffers: shared hit=21622
-> Nested Loop (cost=27526.05..28964.09 rows=15617 width=84) (actual time=34.607..38.510 rows=40 loops=1)
Buffers: shared hit=21619
-> HashAggregate (cost=27525.63..27545.13 rows=1950 width=4) (actual time=33.186..33.654 rows=1276 loops=1)
Group Key: projects.id
Buffers: shared hit=17763
-> Nested Loop Left Join (cost=1576.69..27520.75 rows=1950 width=4) (actual time=0.233..32.428 rows=1276 loops=1)
Filter: ((project_features.repository_access_level IS NULL) OR (project_features.repository_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.repository_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
Rows Removed by Filter: 22
Buffers: shared hit=17763
-> Nested Loop (cost=1576.25..19333.12 rows=1996 width=4) (actual time=0.204..16.202 rows=1298 loops=1)
Buffers: shared hit=9357
-> CTE Scan on base_and_descendants namespaces (cost=1575.81..1579.43 rows=181 width=4) (actual time=0.065..4.454 rows=234 loops=1)
Buffers: shared hit=1166
CTE base_and_descendants
-> Recursive Union (cost=0.43..1575.81 rows=181 width=344) (actual time=0.059..3.916 rows=234 loops=1)
Buffers: shared hit=1166
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=0.051..0.053 rows=1 loops=1)
Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = 9970))
Buffers: shared hit=4
-> Nested Loop (cost=0.56..156.87 rows=18 width=344) (actual time=0.038..0.488 rows=39 loops=6)
Buffers: shared hit=1162
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.008 rows=39 loops=6)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..15.65 rows=2 width=344) (actual time=0.009..0.011 rows=1 loops=234)
Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
Filter: ((namespaces_2.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=1162
-> Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.44..97.97 rows=11 width=8) (actual time=0.016..0.048 rows=6 loops=234)
Index Cond: (projects.namespace_id = namespaces.id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=8191
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1298)
Index Cond: ((project_authorizations.user_id = 120073) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 266
Buffers: shared hit=6185
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..887.19 rows=3108 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 120073) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.44..0.49 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1298)
Index Cond: (projects.id = project_features.project_id)
Buffers: shared hit=5194
SubPlan 3
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_2 (cost=0.57..3.59 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_2.user_id = 120073) AND (project_authorizations_2.project_id = projects.id) AND (project_authorizations_2.access_level >= 10))
Heap Fetches: 0
SubPlan 4
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_3 (cost=0.57..1078.82 rows=3842 width=4) (actual time=0.049..4.167 rows=5259 loops=1)
Index Cond: ((project_authorizations_3.user_id = 120073) AND (project_authorizations_3.access_level >= 10))
Heap Fetches: 1222
Buffers: shared hit=3212
-> Index Scan using index_packages_packages_on_project_id_and_package_type on public.packages_packages (cost=0.42..0.65 rows=8 width=84) (actual time=0.003..0.003 rows=0 loops=1276)
Index Cond: ((packages_packages.project_id = projects.id) AND (packages_packages.package_type = 2))
Filter: ((packages_packages.version IS NOT NULL) AND (packages_packages.status = 0))
Rows Removed by Filter: 0
Buffers: shared hit=3856
(2) ::Packages::GroupPackagesFinder.new(current_user, group, package_type: :npm).execute.exists?
- https://gitlab.com/gitlab-org/gitlab/-/blob/1e7ea614ef5b67e071c5845b05612bdf34a4636c/app/services/groups/update_service.rb#L66
- https://gitlab.com/gitlab-org/gitlab/-/blob/1e7ea614ef5b67e071c5845b05612bdf34a4636c/app/services/groups/transfer_service.rb#L61
Before
Query
SELECT 1 AS one
FROM "packages_packages"
WHERE "packages_packages"."project_id" IN
(
SELECT "projects"."id"
FROM "projects"
LEFT JOIN project_features
ON projects.id = project_features.project_id
WHERE "projects"."namespace_id" IN (WITH recursive "base_and_descendants" AS (
(
SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(
SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (
EXISTS
(
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 120073
AND (
project_authorizations.project_id = projects.id)
AND (
project_authorizations.access_level >= 20))
OR projects.visibility_level IN (10,20))
AND (
"project_features"."repository_access_level" IS NULL
OR "project_features"."repository_access_level" IN (20,30)
OR (
"project_features"."repository_access_level" = 10
AND EXISTS
(
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 120073
AND (
project_authorizations.project_id = projects.id)
AND (
project_authorizations.access_level >= 10)))))
AND (
"packages_packages"."package_type" != 4
OR "packages_packages"."name" != 'NuGet.Temporary.Package')
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."package_type" = 2
AND "packages_packages"."status" = 0 limit 1Plan
Limit (cost=1582.99..1931.50 rows=1 width=4) (actual time=12199.419..12199.434 rows=1 loops=1)
Buffers: shared hit=55669
-> Nested Loop Semi Join (cost=1582.99..5444230.69 rows=15617 width=4) (actual time=12199.418..12199.431 rows=1 loops=1)
Buffers: shared hit=55669
-> Index Scan using index_packages_packages_on_project_id_and_package_type on public.packages_packages (cost=0.42..36662.55 rows=183933 width=4) (actual time=0.214..150.399 rows=43595 loops=1)
Index Cond: (packages_packages.package_type = 2)
Filter: ((packages_packages.version IS NOT NULL) AND (packages_packages.status = 0))
Rows Removed by Filter: 0
Buffers: shared hit=37886
-> Materialize (cost=1582.57..27532.76 rows=1950 width=4) (actual time=0.000..0.108 rows=1276 loops=43595)
Buffers: shared hit=17783
-> Nested Loop Left Join (cost=1582.57..27523.01 rows=1950 width=4) (actual time=4.378..39.661 rows=1276 loops=1)
Filter: ((project_features.repository_access_level IS NULL) OR (project_features.repository_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.repository_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
Rows Removed by Filter: 22
Buffers: shared hit=17783
-> Nested Loop (cost=1582.13..19335.38 rows=1996 width=4) (actual time=4.358..19.722 rows=1298 loops=1)
Buffers: shared hit=9377
-> HashAggregate (cost=1581.69..1583.50 rows=181 width=4) (actual time=4.241..4.360 rows=234 loops=1)
Group Key: namespaces.id
Buffers: shared hit=1166
-> CTE Scan on base_and_descendants namespaces (cost=1575.81..1579.43 rows=181 width=4) (actual time=0.063..4.121 rows=234 loops=1)
Buffers: shared hit=1166
CTE base_and_descendants
-> Recursive Union (cost=0.43..1575.81 rows=181 width=344) (actual time=0.057..3.661 rows=234 loops=1)
Buffers: shared hit=1166
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=0.049..0.050 rows=1 loops=1)
Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = 9970))
Buffers: shared hit=4
-> Nested Loop (cost=0.56..156.87 rows=18 width=344) (actual time=0.036..0.474 rows=39 loops=6)
Buffers: shared hit=1162
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.006 rows=39 loops=6)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..15.65 rows=2 width=344) (actual time=0.008..0.011 rows=1 loops=234)
Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
Filter: ((namespaces_2.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=1162
-> Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.44..97.97 rows=11 width=8) (actual time=0.018..0.064 rows=6 loops=234)
Index Cond: (projects.namespace_id = namespaces.id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=8211
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1298)
Index Cond: ((project_authorizations.user_id = 120073) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 266
Buffers: shared hit=6205
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..887.19 rows=3108 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 120073) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.44..0.49 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1298)
Index Cond: (projects.id = project_features.project_id)
Buffers: shared hit=5194
SubPlan 3
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_2 (cost=0.57..3.59 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_2.user_id = 120073) AND (project_authorizations_2.project_id = projects.id) AND (project_authorizations_2.access_level >= 10))
Heap Fetches: 0
SubPlan 4
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_3 (cost=0.57..1078.82 rows=3842 width=4) (actual time=0.049..4.359 rows=5259 loops=1)
Index Cond: ((project_authorizations_3.user_id = 120073) AND (project_authorizations_3.access_level >= 10))
Heap Fetches: 1222
Buffers: shared hit=3212
After
Query
SELECT 1 AS one
FROM "packages_packages"
WHERE "packages_packages"."project_id" IN
(
SELECT "projects"."id"
FROM "projects"
INNER JOIN (WITH recursive "base_and_descendants" AS (
(
SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(
SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))SELECT "id"
FROM "base_and_descendants" AS "namespaces") namespaces
ON namespaces.id=projects.namespace_id
LEFT JOIN project_features
ON projects.id = project_features.project_id
WHERE (
EXISTS
(
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 120073
AND (
project_authorizations.project_id = projects.id)
AND (
project_authorizations.access_level >= 20))
OR projects.visibility_level IN (10,20))
AND (
"project_features"."repository_access_level" IS NULL
OR "project_features"."repository_access_level" IN (20,30)
OR (
"project_features"."repository_access_level" = 10
AND EXISTS
(
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 120073
AND (
project_authorizations.project_id = projects.id)
AND (
project_authorizations.access_level >= 10)))))
AND (
"packages_packages"."package_type" != 4
OR "packages_packages"."name" != 'NuGet.Temporary.Package')
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."package_type" = 2
AND "packages_packages"."status" = 0 limit 1Plan
Limit (cost=1577.11..1925.62 rows=1 width=4) (actual time=11996.027..11996.044 rows=1 loops=1)
Buffers: shared hit=55649
-> Nested Loop Semi Join (cost=1577.11..5444228.42 rows=15617 width=4) (actual time=11996.025..11996.041 rows=1 loops=1)
Buffers: shared hit=55649
-> Index Scan using index_packages_packages_on_project_id_and_package_type on public.packages_packages (cost=0.42..36662.55 rows=183933 width=4) (actual time=0.302..143.768 rows=43595 loops=1)
Index Cond: (packages_packages.package_type = 2)
Filter: ((packages_packages.version IS NOT NULL) AND (packages_packages.status = 0))
Rows Removed by Filter: 0
Buffers: shared hit=37886
-> Materialize (cost=1576.69..27530.50 rows=1950 width=4) (actual time=0.000..0.106 rows=1276 loops=43595)
Buffers: shared hit=17763
-> Nested Loop Left Join (cost=1576.69..27520.75 rows=1950 width=4) (actual time=0.314..49.236 rows=1276 loops=1)
Filter: ((project_features.repository_access_level IS NULL) OR (project_features.repository_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.repository_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
Rows Removed by Filter: 22
Buffers: shared hit=17763
-> Nested Loop (cost=1576.25..19333.12 rows=1996 width=4) (actual time=0.217..25.520 rows=1298 loops=1)
Buffers: shared hit=9357
-> CTE Scan on base_and_descendants namespaces (cost=1575.81..1579.43 rows=181 width=4) (actual time=0.073..7.685 rows=234 loops=1)
Buffers: shared hit=1166
CTE base_and_descendants
-> Recursive Union (cost=0.43..1575.81 rows=181 width=344) (actual time=0.070..6.567 rows=234 loops=1)
Buffers: shared hit=1166
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=0.060..0.063 rows=1 loops=1)
Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = 9970))
Buffers: shared hit=4
-> Nested Loop (cost=0.56..156.87 rows=18 width=344) (actual time=0.047..0.801 rows=39 loops=6)
Buffers: shared hit=1162
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.015 rows=39 loops=6)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..15.65 rows=2 width=344) (actual time=0.014..0.018 rows=1 loops=234)
Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
Filter: ((namespaces_2.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=1162
-> Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.44..97.97 rows=11 width=8) (actual time=0.026..0.074 rows=6 loops=234)
Index Cond: (projects.namespace_id = namespaces.id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=8191
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=1298)
Index Cond: ((project_authorizations.user_id = 120073) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 266
Buffers: shared hit=6185
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..887.19 rows=3108 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 120073) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.44..0.49 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1298)
Index Cond: (projects.id = project_features.project_id)
Buffers: shared hit=5194
SubPlan 3
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_2 (cost=0.57..3.59 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_2.user_id = 120073) AND (project_authorizations_2.project_id = projects.id) AND (project_authorizations_2.access_level >= 10))
Heap Fetches: 0
SubPlan 4
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_3 (cost=0.57..1078.82 rows=3842 width=4) (actual time=0.094..5.762 rows=5259 loops=1)
Index Cond: ((project_authorizations_3.user_id = 120073) AND (project_authorizations_3.access_level >= 10))
Heap Fetches: 1222
Buffers: shared hit=3212
(3) packages_for_group_projects.composer.preload_composer
Before
Query
SELECT "packages_packages".*
FROM "packages_packages"
WHERE "packages_packages"."project_id" IN
(
SELECT "projects"."id"
FROM "projects"
LEFT JOIN project_features
ON projects.id = project_features.project_id
WHERE "projects"."namespace_id" IN (WITH recursive "base_and_descendants" AS (
(
SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(
SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (
EXISTS
(
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 120073
AND (
project_authorizations.project_id = projects.id)
AND (
project_authorizations.access_level >= 20))
OR projects.visibility_level IN (10,20))
AND (
"project_features"."repository_access_level" IS NULL
OR "project_features"."repository_access_level" IN (20,30)
OR (
"project_features"."repository_access_level" = 10
AND EXISTS
(
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 120073
AND (
project_authorizations.project_id = projects.id)
AND (
project_authorizations.access_level >= 10)))))
AND (
"packages_packages"."package_type" != 4
OR "packages_packages"."name" != 'NuGet.Temporary.Package')
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."status" = 0
AND "packages_packages"."package_type" = 6
ORDER BY created_at DESC limit 11Plan
Sort (cost=28526.51..28529.96 rows=1381 width=84) (actual time=38.251..38.263 rows=2 loops=1)
Sort Key: packages_packages.created_at DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=21625
-> Nested Loop (cost=27528.31..28454.48 rows=1381 width=84) (actual time=33.233..38.207 rows=2 loops=1)
Buffers: shared hit=21622
-> HashAggregate (cost=27527.89..27547.39 rows=1950 width=4) (actual time=32.239..32.701 rows=1276 loops=1)
Group Key: projects.id
Buffers: shared hit=17783
-> Nested Loop Left Join (cost=1582.57..27523.01 rows=1950 width=4) (actual time=4.443..31.502 rows=1276 loops=1)
Filter: ((project_features.repository_access_level IS NULL) OR (project_features.repository_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.repository_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
Rows Removed by Filter: 22
Buffers: shared hit=17783
-> Nested Loop (cost=1582.13..19335.38 rows=1996 width=4) (actual time=4.425..16.014 rows=1298 loops=1)
Buffers: shared hit=9377
-> HashAggregate (cost=1581.69..1583.50 rows=181 width=4) (actual time=4.317..4.420 rows=234 loops=1)
Group Key: namespaces.id
Buffers: shared hit=1166
-> CTE Scan on base_and_descendants namespaces (cost=1575.81..1579.43 rows=181 width=4) (actual time=0.063..4.200 rows=234 loops=1)
Buffers: shared hit=1166
CTE base_and_descendants
-> Recursive Union (cost=0.43..1575.81 rows=181 width=344) (actual time=0.060..3.687 rows=234 loops=1)
Buffers: shared hit=1166
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=0.050..0.051 rows=1 loops=1)
Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = 9970))
Buffers: shared hit=4
-> Nested Loop (cost=0.56..156.87 rows=18 width=344) (actual time=0.039..0.469 rows=39 loops=6)
Buffers: shared hit=1162
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.006 rows=39 loops=6)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..15.65 rows=2 width=344) (actual time=0.008..0.011 rows=1 loops=234)
Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
Filter: ((namespaces_2.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=1162
-> Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.44..97.97 rows=11 width=8) (actual time=0.015..0.048 rows=6 loops=234)
Index Cond: (projects.namespace_id = namespaces.id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=8211
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1298)
Index Cond: ((project_authorizations.user_id = 120073) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 266
Buffers: shared hit=6205
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..887.19 rows=3108 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 120073) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.44..0.49 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1298)
Index Cond: (projects.id = project_features.project_id)
Buffers: shared hit=5194
SubPlan 3
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_2 (cost=0.57..3.59 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_2.user_id = 120073) AND (project_authorizations_2.project_id = projects.id) AND (project_authorizations_2.access_level >= 10))
Heap Fetches: 0
SubPlan 4
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_3 (cost=0.57..1078.82 rows=3842 width=4) (actual time=0.031..3.720 rows=5259 loops=1)
Index Cond: ((project_authorizations_3.user_id = 120073) AND (project_authorizations_3.access_level >= 10))
Heap Fetches: 1222
Buffers: shared hit=3212
-> Index Scan using index_packages_packages_on_project_id_and_package_type on public.packages_packages (cost=0.42..0.46 rows=1 width=84) (actual time=0.004..0.004 rows=0 loops=1276)
Index Cond: ((packages_packages.project_id = projects.id) AND (packages_packages.package_type = 6))
Filter: ((packages_packages.version IS NOT NULL) AND (packages_packages.status = 0))
Rows Removed by Filter: 0
Buffers: shared hit=3839
After
Query
SELECT "packages_packages".*
FROM "packages_packages"
WHERE "packages_packages"."project_id" IN
(
SELECT "projects"."id"
FROM "projects"
INNER JOIN (WITH recursive "base_and_descendants" AS (
(
SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(
SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))SELECT "id"
FROM "base_and_descendants" AS "namespaces") namespaces
ON namespaces.id=projects.namespace_id
LEFT JOIN project_features
ON projects.id = project_features.project_id
WHERE (
EXISTS
(
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 120073
AND (
project_authorizations.project_id = projects.id)
AND (
project_authorizations.access_level >= 20))
OR projects.visibility_level IN (10,20))
AND (
"project_features"."repository_access_level" IS NULL
OR "project_features"."repository_access_level" IN (20,30)
OR (
"project_features"."repository_access_level" = 10
AND EXISTS
(
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 120073
AND (
project_authorizations.project_id = projects.id)
AND (
project_authorizations.access_level >= 10)))))
AND (
"packages_packages"."package_type" != 4
OR "packages_packages"."name" != 'NuGet.Temporary.Package')
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."status" = 0
AND "packages_packages"."package_type" = 6
ORDER BY created_at DESCPlan
Sort (cost=28524.24..28527.70 rows=1381 width=84) (actual time=32.647..32.655 rows=2 loops=1)
Sort Key: packages_packages.created_at DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=21605
-> Nested Loop (cost=27526.05..28452.21 rows=1381 width=84) (actual time=28.988..32.607 rows=2 loops=1)
Buffers: shared hit=21602
-> HashAggregate (cost=27525.63..27545.13 rows=1950 width=4) (actual time=28.276..28.654 rows=1276 loops=1)
Group Key: projects.id
Buffers: shared hit=17763
-> Nested Loop Left Join (cost=1576.69..27520.75 rows=1950 width=4) (actual time=0.196..27.682 rows=1276 loops=1)
Filter: ((project_features.repository_access_level IS NULL) OR (project_features.repository_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.repository_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
Rows Removed by Filter: 22
Buffers: shared hit=17763
-> Nested Loop (cost=1576.25..19333.12 rows=1996 width=4) (actual time=0.179..13.580 rows=1298 loops=1)
Buffers: shared hit=9357
-> CTE Scan on base_and_descendants namespaces (cost=1575.81..1579.43 rows=181 width=4) (actual time=0.058..3.770 rows=234 loops=1)
Buffers: shared hit=1166
CTE base_and_descendants
-> Recursive Union (cost=0.43..1575.81 rows=181 width=344) (actual time=0.055..3.313 rows=234 loops=1)
Buffers: shared hit=1166
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=0.047..0.049 rows=1 loops=1)
Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = 9970))
Buffers: shared hit=4
-> Nested Loop (cost=0.56..156.87 rows=18 width=344) (actual time=0.034..0.417 rows=39 loops=6)
Buffers: shared hit=1162
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.007 rows=39 loops=6)
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..15.65 rows=2 width=344) (actual time=0.007..0.010 rows=1 loops=234)
Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
Filter: ((namespaces_2.type)::text = 'Group'::text)
Rows Removed by Filter: 0
Buffers: shared hit=1162
-> Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.44..97.97 rows=11 width=8) (actual time=0.013..0.040 rows=6 loops=234)
Index Cond: (projects.namespace_id = namespaces.id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=8191
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1298)
Index Cond: ((project_authorizations.user_id = 120073) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
Heap Fetches: 266
Buffers: shared hit=6185
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_1 (cost=0.57..887.19 rows=3108 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_1.user_id = 120073) AND (project_authorizations_1.access_level >= 20))
Heap Fetches: 0
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.44..0.49 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1298)
Index Cond: (projects.id = project_features.project_id)
Buffers: shared hit=5194
SubPlan 3
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_2 (cost=0.57..3.59 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations_2.user_id = 120073) AND (project_authorizations_2.project_id = projects.id) AND (project_authorizations_2.access_level >= 10))
Heap Fetches: 0
SubPlan 4
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations project_authorizations_3 (cost=0.57..1078.82 rows=3842 width=4) (actual time=0.040..3.798 rows=5259 loops=1)
Index Cond: ((project_authorizations_3.user_id = 120073) AND (project_authorizations_3.access_level >= 10))
Heap Fetches: 1222
Buffers: shared hit=3212
-> Index Scan using index_packages_packages_on_project_id_and_package_type on public.packages_packages (cost=0.42..0.46 rows=1 width=84) (actual time=0.003..0.003 rows=0 loops=1276)
Index Cond: ((packages_packages.project_id = projects.id) AND (packages_packages.package_type = 6))
Filter: ((packages_packages.version IS NOT NULL) AND (packages_packages.status = 0))
Rows Removed by Filter: 0
Buffers: shared hit=3839
Does this MR meet the acceptance criteria?
Conformity
-
📋 Does this MR need a changelog?-
I have included a changelog entry. -
I have not included a changelog entry because _____.
-
- [-] Documentation (if required)
-
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Tested in all supported browsers -
Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
-
Label as security and @ mention @gitlab-com/gl-security/appsec -
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods -
Security reports checked/validated by a reviewer from the AppSec team
Related to #324220 (closed)
Edited by Dylan Griffith