Add displayable and installable scopes to package finders
🔎 What does this MR do?
We recently added a status
column to the packages model. This allows packages to be labelled as being in various states such as "error", "processing", and "hidden".
When working with package managers such as NPM or Maven, we do not want any invalid packages to be included in results. For example, if you were using the NuGet search service, we need to be sure "error" and "processing" packages are not returned as possible packages for the user to consume.
This MR updates the various package finders to only return valid packages.
Alternatively, when interacting with the GitLab UI, we do want some invalid packages to be displayed. For example, it will be helpful for users to see packages with "error" statuses so they realize something when wrong when publishing them and they can delete it and attempt to republish them.
The existing scope .displayable
includes some uninstallable packages that should be displayed in the UI.
A new scope .installable
is added to specify only packages that should be allowed to be returned when package managers are searching for packages to install.
🐘 Database
The changes being added to all of these finders revolve around adding either the .installable
, or .displayable
scope to existing queries. The good news is, both of these scopes will result in essentially the same query addition:
-- .displayable
WHERE packages_packages.status IN (0,3)
-- .installable
WHERE packages_packages.status = 0
You will see in the queries below, the additional WHERE/AND
clause always results in one additional index filter on one of the indexes used by the query.
Many of these finders have the ability to take a wide variety of options. I've included just one example for each finder since the overall impact of adding the new scope effects all variations of the finder queries in the same way (and also to keep this section from getting too long).
The Before
and After
links link to a formatted explain plan.
Composer Finder (installable scope)
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" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0, 10, 20)) AND ("project_features"."repository_access_level" > 0 OR "project_features"."repository_access_level" IS NULL)) 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" = 6 ORDER BY "packages_packages"."created_at" DESC
QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=24624.12..24632.87 rows=3502 width=83) (actual time=39.757..39.758 rows=2 loops=1) Sort Key: packages_packages.created_at DESC Sort Method: quicksort Memory: 25kB Buffers: shared hit=20656 -> Nested Loop (cost=22653.09..24417.95 rows=3502 width=83) (actual time=36.501..39.749 rows=2 loops=1) Buffers: shared hit=20656 -> HashAggregate (cost=22652.67..22689.88 rows=3721 width=4) (actual time=32.074..32.556 rows=1349 loops=1) Group Key: projects.id Buffers: shared hit=16600 -> Nested Loop Left Join (cost=1591.32..22643.36 rows=3721 width=4) (actual time=3.768..31.233 rows=1349 loops=1) Filter: ((project_features.repository_access_level > 0) OR (project_features.repository_access_level IS NULL)) Rows Removed by Filter: 13 Buffers: shared hit=16600 -> Nested Loop (cost=1590.76..20313.13 rows=3724 width=4) (actual time=3.744..18.428 rows=1362 loops=1) Buffers: shared hit=9789 -> HashAggregate (cost=1590.32..1592.23 rows=191 width=4) (actual time=3.660..3.744 rows=228 loops=1) Group Key: namespaces.id Buffers: shared hit=1144 -> CTE Scan on base_and_descendants namespaces (cost=1584.11..1587.93 rows=191 width=4) (actual time=0.036..3.546 rows=228 loops=1) Buffers: shared hit=1144 CTE base_and_descendants -> Recursive Union (cost=0.43..1584.11 rows=191 width=344) (actual time=0.033..3.071 rows=228 loops=1) Buffers: shared hit=1144 -> Index Scan using index_namespaces_on_type_and_id_partial on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=0.024..0.025 rows=1 loops=1) Index Cond: (((type)::text = 'Group'::text) AND (id = 9970)) Buffers: shared hit=4 -> Nested Loop (cost=0.56..157.68 rows=19 width=344) (actual time=0.027..0.388 rows=38 loops=6) Buffers: shared hit=1140 -> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.007 rows=38 loops=6) -> Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.56..15.73 rows=2 width=344) (actual time=0.006..0.009 rows=1 loops=228) Index Cond: (parent_id = base_and_descendants.id) Filter: ((type)::text = 'Group'::text) Buffers: shared hit=1140 -> Index Scan using index_projects_on_namespace_id_and_id on projects (cost=0.44..97.83 rows=19 width=8) (actual time=0.015..0.062 rows=6 loops=228) Index Cond: (namespace_id = namespaces.id) Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{0,10,20}'::integer[]))) Buffers: shared hit=8645 SubPlan 1 -> Index Only Scan using project_authorizations_pkey on project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1362) Index Cond: ((user_id = 1) AND (project_id = projects.id)) Heap Fetches: 170 Buffers: shared hit=6422 SubPlan 2 -> Index Only Scan using project_authorizations_pkey on project_authorizations project_authorizations_1 (cost=0.57..945.26 rows=4837 width=4) (never executed) Index Cond: (user_id = 1) Heap Fetches: 0 -> Index Scan using index_project_features_on_project_id on project_features (cost=0.56..0.61 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1362) Index Cond: (projects.id = project_id) Buffers: shared hit=6811 -> Index Scan using index_packages_packages_on_project_id_and_package_type on packages_packages (cost=0.43..0.45 rows=1 width=83) (actual time=0.005..0.005 rows=0 loops=1349) Index Cond: ((project_id = projects.id) AND (package_type = 6)) Filter: (version IS NOT NULL) Buffers: shared hit=4056 Planning Time: 3.297 ms Execution Time: 40.104 ms (55 rows)
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" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0, 10, 20)) AND ("project_features"."repository_access_level" > 0 OR "project_features"."repository_access_level" IS NULL)) 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 -- this is the added line to the query AND "packages_packages"."package_type" = 6 ORDER BY "packages_packages"."created_at" DESC
QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=24633.15..24641.90 rows=3498 width=83) (actual time=37.054..37.055 rows=2 loops=1) Sort Key: packages_packages.created_at DESC Sort Method: quicksort Memory: 25kB Buffers: shared hit=20656 -> Nested Loop (cost=22653.09..24427.26 rows=3498 width=83) (actual time=34.581..37.047 rows=2 loops=1) Buffers: shared hit=20656 -> HashAggregate (cost=22652.67..22689.88 rows=3721 width=4) (actual time=29.946..30.401 rows=1349 loops=1) Group Key: projects.id Buffers: shared hit=16600 -> Nested Loop Left Join (cost=1591.32..22643.36 rows=3721 width=4) (actual time=2.748..29.119 rows=1349 loops=1) Filter: ((project_features.repository_access_level > 0) OR (project_features.repository_access_level IS NULL)) Rows Removed by Filter: 13 Buffers: shared hit=16600 -> Nested Loop (cost=1590.76..20313.13 rows=3724 width=4) (actual time=2.726..16.565 rows=1362 loops=1) Buffers: shared hit=9789 -> HashAggregate (cost=1590.32..1592.23 rows=191 width=4) (actual time=2.681..2.765 rows=228 loops=1) Group Key: namespaces.id Buffers: shared hit=1144 -> CTE Scan on base_and_descendants namespaces (cost=1584.11..1587.93 rows=191 width=4) (actual time=0.029..2.599 rows=228 loops=1) Buffers: shared hit=1144 CTE base_and_descendants -> Recursive Union (cost=0.43..1584.11 rows=191 width=344) (actual time=0.026..2.259 rows=228 loops=1) Buffers: shared hit=1144 -> Index Scan using index_namespaces_on_type_and_id_partial on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=0.019..0.020 rows=1 loops=1) Index Cond: (((type)::text = 'Group'::text) AND (id = 9970)) Buffers: shared hit=4 -> Nested Loop (cost=0.56..157.68 rows=19 width=344) (actual time=0.019..0.288 rows=38 loops=6) Buffers: shared hit=1140 -> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.003 rows=38 loops=6) -> Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.56..15.73 rows=2 width=344) (actual time=0.005..0.007 rows=1 loops=228) Index Cond: (parent_id = base_and_descendants.id) Filter: ((type)::text = 'Group'::text) Buffers: shared hit=1140 -> Index Scan using index_projects_on_namespace_id_and_id on projects (cost=0.44..97.83 rows=19 width=8) (actual time=0.014..0.059 rows=6 loops=228) Index Cond: (namespace_id = namespaces.id) Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{0,10,20}'::integer[]))) Buffers: shared hit=8645 SubPlan 1 -> Index Only Scan using project_authorizations_pkey on project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1362) Index Cond: ((user_id = 1) AND (project_id = projects.id)) Heap Fetches: 170 Buffers: shared hit=6422 SubPlan 2 -> Index Only Scan using project_authorizations_pkey on project_authorizations project_authorizations_1 (cost=0.57..945.26 rows=4837 width=4) (never executed) Index Cond: (user_id = 1) Heap Fetches: 0 -> Index Scan using index_project_features_on_project_id on project_features (cost=0.56..0.61 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1362) Index Cond: (projects.id = project_id) Buffers: shared hit=6811 -> Index Scan using index_packages_packages_on_project_id_and_package_type on packages_packages (cost=0.43..0.46 rows=1 width=83) (actual time=0.005..0.005 rows=0 loops=1349) Index Cond: ((project_id = projects.id) AND (package_type = 6)) Filter: ((version IS NOT NULL) AND (status = 0)) Buffers: shared hit=4056 Planning Time: 2.370 ms Execution Time: 37.339 ms (55 rows)
Conan Finder (installable scope)
SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."package_type" = 3 AND "packages_packages"."project_id" IN ( SELECT "projects"."id" FROM "projects" WHERE (EXISTS ( SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 4059254 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0, 10, 20))) AND "packages_packages"."name" ILIKE 'Hello%' ORDER BY LOWER("packages_packages"."name") ASC;
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=562.04..562.05 rows=1 width=115) (actual time=45.305..45.309 rows=51 loops=1) Sort Key: (lower((packages_packages.name)::text)) Sort Method: quicksort Memory: 32kB Buffers: shared hit=2934 read=40 I/O Timings: read=7.561 -> Nested Loop (cost=410.71..562.03 rows=1 width=115) (actual time=13.018..45.225 rows=51 loops=1) Buffers: shared hit=2934 read=40 I/O Timings: read=7.561 -> Bitmap Heap Scan on packages_packages (cost=410.15..554.85 rows=1 width=83) (actual time=12.884..35.531 rows=51 loops=1) Recheck Cond: ((name)::text ~~* 'Hello%'::text) Rows Removed by Index Recheck: 4204 Filter: (package_type = 3) Rows Removed by Filter: 156 Heap Blocks: exact=2136 Buffers: shared hit=2499 read=2 I/O Timings: read=0.650 -> Bitmap Index Scan on index_packages_packages_on_name_trigram (cost=0.00..410.15 rows=86 width=0) (actual time=6.944..6.944 rows=4411 loops=1) Index Cond: ((name)::text ~~* 'Hello%'::text) Buffers: shared hit=365 -> Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on projects (cost=0.56..7.18 rows=1 width=4) (actual time=0.186..0.186 rows=1 loops=51) Index Cond: (id = packages_packages.project_id) Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{0,10,20}'::integer[]))) Buffers: shared hit=435 read=38 I/O Timings: read=6.911 SubPlan 1 -> Index Only Scan using project_authorizations_pkey on project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.052..0.052 rows=0 loops=51) Index Cond: ((user_id = 4059254) AND (project_id = projects.id)) Heap Fetches: 1 Buffers: shared hit=198 read=16 I/O Timings: read=2.169 SubPlan 2 -> Index Only Scan using project_authorizations_pkey on project_authorizations project_authorizations_1 (cost=0.57..945.28 rows=4838 width=4) (never executed) Index Cond: (user_id = 4059254) Heap Fetches: 0 Planning Time: 3.204 ms Execution Time: 45.384 ms (36 rows)
SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."package_type" = 3 AND "packages_packages"."project_id" IN ( SELECT "projects"."id" FROM "projects" WHERE (EXISTS ( SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 4059254 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0, 10, 20))) AND "packages_packages"."status" = 0 -- this is the line added to the query AND "packages_packages"."name" ILIKE 'Hello%' ORDER BY LOWER("packages_packages"."name") ASC;
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=563.76..563.76 rows=1 width=115) (actual time=23.128..23.134 rows=51 loops=1) Sort Key: (lower((packages_packages.name)::text)) Sort Method: quicksort Memory: 32kB Buffers: shared hit=2975 -> Nested Loop (cost=412.21..563.75 rows=1 width=115) (actual time=9.109..23.059 rows=51 loops=1) Buffers: shared hit=2975 -> Bitmap Heap Scan on packages_packages (cost=411.65..556.57 rows=1 width=83) (actual time=9.066..21.866 rows=51 loops=1) Recheck Cond: ((name)::text ~~* 'Hello%'::text) Rows Removed by Index Recheck: 4204 Filter: ((package_type = 3) AND (status = 0)) Rows Removed by Filter: 156 Heap Blocks: exact=2136 Buffers: shared hit=2502 -> Bitmap Index Scan on index_packages_packages_on_name_trigram (cost=0.00..411.65 rows=86 width=0) (actual time=7.031..7.031 rows=4411 loops=1) Index Cond: ((name)::text ~~* 'Hello%'::text) Buffers: shared hit=366 -> Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on projects (cost=0.56..7.18 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=51) Index Cond: (id = packages_packages.project_id) Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{0,10,20}'::integer[]))) Buffers: shared hit=473 SubPlan 1 -> Index Only Scan using project_authorizations_pkey on project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=51) Index Cond: ((user_id = 4059254) AND (project_id = projects.id)) Heap Fetches: 1 Buffers: shared hit=214 SubPlan 2 -> Index Only Scan using project_authorizations_pkey on project_authorizations project_authorizations_1 (cost=0.57..945.28 rows=4838 width=4) (never executed) Index Cond: (user_id = 4059254) Heap Fetches: 0 Planning Time: 3.133 ms Execution Time: 23.223 ms (31 rows)
Generic Package Finder (installable scope)
SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 25155009 AND "packages_packages"."package_type" = 7 AND "packages_packages"."name" = 'Stim' AND "packages_packages"."version" = '6.5.9' LIMIT 1;
QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.41..3.44 rows=1 width=83) (actual time=1.798..1.798 rows=1 loops=1) Buffers: shared hit=3 read=1 I/O Timings: read=1.716 -> Index Scan using index_packages_on_project_id_name_version_unique_when_generic on packages_packages (cost=0.41..3.44 rows=1 width=83) (actual time=1.797..1.797 rows=1 loops=1) Index Cond: ((project_id = 25155009) AND ((name)::text = 'Stim'::text) AND ((version)::text = '6.5.9'::text)) Buffers: shared hit=3 read=1 I/O Timings: read=1.716 Planning Time: 0.445 ms Execution Time: 1.817 ms (9 rows)
SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 25155009 AND "packages_packages"."package_type" = 7 AND "packages_packages"."status" = 0 -- this is the line added to the query AND "packages_packages"."name" = 'Stim' AND "packages_packages"."version" = '6.5.9' LIMIT 1;
QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.41..3.44 rows=1 width=83) (actual time=0.020..0.021 rows=1 loops=1) Buffers: shared hit=4 -> Index Scan using index_packages_on_project_id_name_version_unique_when_generic on packages_packages (cost=0.41..3.44 rows=1 width=83) (actual time=0.020..0.020 rows=1 loops=1) Index Cond: ((project_id = 25155009) AND ((name)::text = 'Stim'::text) AND ((version)::text = '6.5.9'::text)) Filter: (status = 0) Buffers: shared hit=4 Planning Time: 0.410 ms Execution Time: 0.037 ms (8 rows)
Go Finder (installable scope)
Note: Go packages are behind a feature flag, there are no go packages currently present on production.SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 25155009 AND "packages_packages"."package_type" = 8 AND "packages_packages"."name" = 'foo' AND "packages_packages"."version" = '1.0.0' ORDER BY "packages_packages"."id" ASC LIMIT 1;
QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=3.16..3.16 rows=1 width=83) (actual time=0.012..0.012 rows=0 loops=1) Buffers: shared hit=1 -> Sort (cost=3.16..3.16 rows=1 width=83) (actual time=0.011..0.012 rows=0 loops=1) Sort Key: id Sort Method: quicksort Memory: 25kB Buffers: shared hit=1 -> Index Scan using index_packages_on_project_id_name_version_unique_when_golang on packages_packages (cost=0.12..3.15 rows=1 width=83) (actual time=0.007..0.007 rows=0 loops=1) Index Cond: ((project_id = 25155009) AND ((name)::text = 'foo'::text) AND ((version)::text = '1.0.0'::text)) Buffers: shared hit=1 Planning Time: 0.589 ms Execution Time: 0.041 ms (11 rows)
SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 25155009 AND "packages_packages"."package_type" = 8 AND "packages_packages"."status" = 0 -- this is the line added to the query AND "packages_packages"."name" = 'foo' AND "packages_packages"."version" = '1.0.0' ORDER BY "packages_packages"."id" ASC LIMIT 1;
QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=3.16..3.17 rows=1 width=83) (actual time=0.019..0.019 rows=0 loops=1) Buffers: shared hit=4 -> Sort (cost=3.16..3.17 rows=1 width=83) (actual time=0.019..0.019 rows=0 loops=1) Sort Key: id Sort Method: quicksort Memory: 25kB Buffers: shared hit=4 -> Index Scan using index_packages_on_project_id_name_version_unique_when_golang on packages_packages (cost=0.12..3.15 rows=1 width=83) (actual time=0.003..0.003 rows=0 loops=1) Index Cond: ((project_id = 25155009) AND ((name)::text = 'foo'::text) AND ((version)::text = '1.0.0'::text)) Filter: (status = 0) Buffers: shared hit=1 Planning Time: 0.446 ms Execution Time: 0.038 ms (12 rows)
Maven Finder (installable scope)
SELECT "packages_packages".* FROM "packages_packages" INNER JOIN "packages_maven_metadata" ON "packages_maven_metadata"."package_id" = "packages_packages"."id" WHERE "packages_packages"."project_id" = 15 AND "packages_maven_metadata"."path" = 'my/company/app/maven-app-4/1.0.4-SNAPSHOT' ORDER BY "packages_packages"."id" DESC LIMIT 1;
QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=7.05..7.05 rows=1 width=83) (actual time=0.949..0.949 rows=1 loops=1) Buffers: shared hit=7 read=2 I/O Timings: read=0.735 -> Sort (cost=7.05..7.05 rows=1 width=83) (actual time=0.948..0.948 rows=1 loops=1) Sort Key: packages_packages.id DESC Sort Method: quicksort Memory: 25kB Buffers: shared hit=7 read=2 I/O Timings: read=0.735 -> Nested Loop (cost=0.98..7.04 rows=1 width=83) (actual time=0.930..0.940 rows=1 loops=1) Buffers: shared hit=7 read=2 I/O Timings: read=0.735 -> Index Scan using index_packages_maven_metadata_on_path on packages_maven_metadata (cost=0.55..3.57 rows=1 width=8) (actual time=0.854..0.864 rows=1 loops=1) Index Cond: ((path)::text = 'my/group/id/Stim/1.4.7'::text) Buffers: shared hit=4 read=1 I/O Timings: read=0.722 -> Index Scan using index_packages_packages_on_id_and_created_at on packages_packages (cost=0.43..3.45 rows=1 width=83) (actual time=0.071..0.071 rows=1 loops=1) Index Cond: (id = packages_maven_metadata.package_id) Filter: (project_id = 17012483) Buffers: shared hit=3 read=1 I/O Timings: read=0.013 Planning Time: 1.697 ms Execution Time: 0.991 ms (22 rows)
SELECT "packages_packages".* FROM "packages_packages" INNER JOIN "packages_maven_metadata" ON "packages_maven_metadata"."package_id" = "packages_packages"."id" WHERE "packages_packages"."project_id" = 17012483 AND "packages_packages"."status" = 0 -- this is the line added to the query AND "packages_maven_metadata"."path" = 'my/group/id/Stim/1.4.7' ORDER BY "packages_packages"."id" DESC LIMIT 1;
QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=7.05..7.05 rows=1 width=83) (actual time=0.041..0.041 rows=1 loops=1) Buffers: shared hit=9 -> Sort (cost=7.05..7.05 rows=1 width=83) (actual time=0.041..0.041 rows=1 loops=1) Sort Key: packages_packages.id DESC Sort Method: quicksort Memory: 25kB Buffers: shared hit=9 -> Nested Loop (cost=0.98..7.04 rows=1 width=83) (actual time=0.035..0.035 rows=1 loops=1) Buffers: shared hit=9 -> Index Scan using index_packages_maven_metadata_on_path on packages_maven_metadata (cost=0.55..3.57 rows=1 width=8) (actual time=0.019..0.020 rows=1 loops=1) Index Cond: ((path)::text = 'my/group/id/Stim/1.4.7'::text) Buffers: shared hit=5 -> Index Scan using index_packages_packages_on_id_and_created_at on packages_packages (cost=0.43..3.45 rows=1 width=83) (actual time=0.013..0.013 rows=1 loops=1) Index Cond: (id = packages_maven_metadata.package_id) Filter: ((project_id = 17012483) AND (status = 0)) Buffers: shared hit=4 Planning Time: 0.448 ms Execution Time: 0.070 ms (17 rows)
NPM Finder (installable scope)
SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 15833924 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@issue-reproduce/mono-repo' AND "packages_packages"."id" IN ( SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 15833924 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@issue-reproduce/mono-repo' GROUP BY "packages_packages"."version");
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Nested Loop Semi Join (cost=1.10..7.19 rows=1 width=83) (actual time=0.148..0.155 rows=2 loops=1) Join Filter: (packages_packages.id = (max(packages_packages_1.id))) Rows Removed by Join Filter: 1 Buffers: shared hit=17 read=1 I/O Timings: read=0.019 -> Index Scan using idx_packages_packages_on_project_id_name_version_package_type on packages_packages (cost=0.55..3.58 rows=1 width=83) (actual time=0.120..0.121 rows=2 loops=1) Index Cond: ((project_id = 15833924) AND ((name)::text = '@issue-reproduce/mono-repo'::text) AND (package_type = 2)) Buffers: shared hit=5 read=1 I/O Timings: read=0.019 -> GroupAggregate (cost=0.55..3.59 rows=1 width=22) (actual time=0.014..0.015 rows=2 loops=2) Group Key: packages_packages_1.version Buffers: shared hit=12 -> Index Scan using idx_packages_packages_on_project_id_name_version_package_type on packages_packages packages_packages_1 (cost=0.55..3.58 rows=1 width=22) (actual time=0.004..0.012 rows=2 loops=2) Index Cond: ((project_id = 15833924) AND ((name)::text = '@issue-reproduce/mono-repo'::text) AND (package_type = 2)) Buffers: shared hit=12 Planning Time: 0.771 ms Execution Time: 0.212 ms (17 rows)
SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 15833924 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@issue-reproduce/mono-repo' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN ( SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 15833924 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@issue-reproduce/mono-repo' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version");
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Nested Loop Semi Join (cost=1.10..7.19 rows=1 width=83) (actual time=0.038..0.045 rows=2 loops=1) Join Filter: (packages_packages.id = (max(packages_packages_1.id))) Rows Removed by Join Filter: 1 Buffers: shared hit=18 -> Index Scan using idx_packages_packages_on_project_id_name_version_package_type on packages_packages (cost=0.55..3.58 rows=1 width=83) (actual time=0.024..0.025 rows=2 loops=1) Index Cond: ((project_id = 15833924) AND ((name)::text = '@issue-reproduce/mono-repo'::text) AND (package_type = 2)) Filter: (status = 0) Buffers: shared hit=6 -> GroupAggregate (cost=0.55..3.59 rows=1 width=22) (actual time=0.008..0.008 rows=2 loops=2) Group Key: packages_packages_1.version Buffers: shared hit=12 -> Index Scan using idx_packages_packages_on_project_id_name_version_package_type on packages_packages packages_packages_1 (cost=0.55..3.58 rows=1 width=22) (actual time=0.004..0.006 rows=2 loops=2) Index Cond: ((project_id = 15833924) AND ((name)::text = '@issue-reproduce/mono-repo'::text) AND (package_type = 2)) Filter: (status = 0) Buffers: shared hit=12 Planning Time: 0.701 ms Execution Time: 0.079 ms (17 rows)
NuGet Finder (installable scope)
SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 15835259 AND "packages_packages"."package_type" = 4 AND "packages_packages"."version" IS NOT NULL AND ("packages_packages"."package_type" != 4 OR "packages_packages"."name" != 'NuGet.Temporary.Package') AND "packages_packages"."name" ILIKE 'issue-reproduce.packages.nuget.nuget-package.MyThirdPackage' ORDER BY "packages_packages"."created_at" DESC LIMIT 300;
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=132.62..132.62 rows=1 width=83) (actual time=10.615..10.628 rows=48 loops=1) Buffers: shared hit=615 read=4 I/O Timings: read=2.282 -> Sort (cost=132.62..132.62 rows=1 width=83) (actual time=10.614..10.620 rows=48 loops=1) Sort Key: created_at DESC Sort Method: quicksort Memory: 31kB Buffers: shared hit=615 read=4 I/O Timings: read=2.282 -> Index Scan using index_packages_packages_on_project_id_and_package_type on packages_packages (cost=0.43..132.61 rows=1 width=83) (actual time=4.377..10.570 rows=48 loops=1) Index Cond: ((project_id = 15835259) AND (package_type = 4)) Filter: ((version IS NOT NULL) AND ((name)::text ~~* 'issue-reproduce.packages.nuget.nuget-package.MyThirdPackage'::text) AND ((package_type <> 4) OR ((name)::text <> 'NuGet.Temporary.Package'::text))) Rows Removed by Filter: 931 Buffers: shared hit=615 read=4 I/O Timings: read=2.282 Planning Time: 4.465 ms Execution Time: 10.674 ms (16 rows)
SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 15835259 AND "packages_packages"."package_type" = 4 AND "packages_packages"."version" IS NOT NULL AND "packages_packages"."status" = 0 AND ("packages_packages"."package_type" != 4 OR "packages_packages"."name" != 'NuGet.Temporary.Package') AND "packages_packages"."name" ILIKE 'issue-reproduce.packages.nuget.nuget-package.MyThirdPackage' ORDER BY "packages_packages"."created_at" DESC LIMIT 300;
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=132.87..132.87 rows=1 width=83) (actual time=2.438..2.446 rows=48 loops=1) Buffers: shared hit=619 -> Sort (cost=132.87..132.87 rows=1 width=83) (actual time=2.437..2.441 rows=48 loops=1) Sort Key: created_at DESC Sort Method: quicksort Memory: 31kB Buffers: shared hit=619 -> Index Scan using index_packages_packages_on_project_id_and_package_type on packages_packages (cost=0.43..132.86 rows=1 width=83) (actual time=0.878..2.418 rows=48 loops=1) Index Cond: ((project_id = 15835259) AND (package_type = 4)) Filter: ((version IS NOT NULL) AND ((name)::text ~~* 'issue-reproduce.packages.nuget.nuget-package.MyThirdPackage'::text) AND (status = 0) AND ((package_type <> 4) OR ((name)::text <> 'NuGet.Temporary.Package'::text))) Rows Removed by Filter: 931 Buffers: shared hit=619 Planning Time: 3.585 ms Execution Time: 2.482 ms (13 rows)
Group Package Finder (displayable scope)
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" = 4059254 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0, 10, 20)) AND ("project_features"."repository_access_level" > 0 OR "project_features"."repository_access_level" IS NULL)) AND ("packages_packages"."package_type" != 4 OR "packages_packages"."name" != 'NuGet.Temporary.Package') AND "packages_packages"."version" IS NOT NULL ORDER BY "packages_packages"."created_at" ASC;
QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=47509.76..47969.78 rows=184010 width=83) (actual time=29.457..29.507 rows=707 loops=1) Sort Key: packages_packages.created_at Sort Method: quicksort Memory: 124kB Buffers: shared hit=21467 read=8 I/O Timings: read=0.146 -> Nested Loop (cost=23634.83..31418.61 rows=184010 width=83) (actual time=23.549..29.188 rows=707 loops=1) Buffers: shared hit=21467 read=8 I/O Timings: read=0.146 -> HashAggregate (cost=23634.40..23671.88 rows=3748 width=4) (actual time=22.765..23.111 rows=1349 loops=1) Group Key: projects.id Buffers: shared hit=16777 read=8 I/O Timings: read=0.146 -> Nested Loop Left Join (cost=1591.55..23625.03 rows=3748 width=4) (actual time=2.508..22.284 rows=1349 loops=1) Filter: ((project_features.repository_access_level > 0) OR (project_features.repository_access_level IS NULL)) Rows Removed by Filter: 13 Buffers: shared hit=16777 read=8 I/O Timings: read=0.146 -> Nested Loop (cost=1590.99..21277.98 rows=3751 width=4) (actual time=2.491..12.786 rows=1362 loops=1) Buffers: shared hit=9966 read=8 I/O Timings: read=0.146 -> HashAggregate (cost=1590.55..1592.46 rows=191 width=4) (actual time=2.456..2.513 rows=228 loops=1) Group Key: namespaces.id Buffers: shared hit=1144 -> CTE Scan on base_and_descendants namespaces (cost=1584.34..1588.16 rows=191 width=4) (actual time=0.028..2.386 rows=228 loops=1) Buffers: shared hit=1144 CTE base_and_descendants -> Recursive Union (cost=0.43..1584.34 rows=191 width=344) (actual time=0.025..2.131 rows=228 loops=1) Buffers: shared hit=1144 -> Index Scan using index_namespaces_on_type_and_id_partial on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=0.019..0.019 rows=1 loops=1) Index Cond: (((type)::text = 'Group'::text) AND (id = 9970)) Buffers: shared hit=4 -> Nested Loop (cost=0.56..157.71 rows=19 width=344) (actual time=0.020..0.274 rows=38 loops=6) Buffers: shared hit=1140 -> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.005 rows=38 loops=6) -> Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.56..15.73 rows=2 width=344) (actual time=0.005..0.007 rows=1 loops=228) Index Cond: (parent_id = base_and_descendants.id) Filter: ((type)::text = 'Group'::text) Buffers: shared hit=1140 -> Index Scan using index_projects_on_namespace_id_and_id on projects (cost=0.44..102.87 rows=20 width=8) (actual time=0.012..0.044 rows=6 loops=228) Index Cond: (namespace_id = namespaces.id) Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{0,10,20}'::integer[]))) Buffers: shared hit=8822 read=8 I/O Timings: read=0.146 SubPlan 1 -> Index Only Scan using project_authorizations_pkey on project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1362) Index Cond: ((user_id = 4059254) AND (project_id = projects.id)) Heap Fetches: 536 Buffers: shared hit=6691 read=8 I/O Timings: read=0.146 SubPlan 2 -> Index Only Scan using project_authorizations_pkey on project_authorizations project_authorizations_1 (cost=0.57..947.91 rows=4840 width=4) (never executed) Index Cond: (user_id = 4059254) Heap Fetches: 0 -> Index Scan using index_project_features_on_project_id on project_features (cost=0.56..0.61 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1362) Index Cond: (projects.id = project_id) Buffers: shared hit=6811 -> Index Scan using index_packages_packages_on_project_id_and_status on packages_packages (cost=0.43..1.58 rows=49 width=83) (actual time=0.003..0.004 rows=1 loops=1349) Index Cond: (project_id = projects.id) Filter: ((version IS NOT NULL) AND ((package_type <> 4) OR ((name)::text <> 'NuGet.Temporary.Package'::text))) Rows Removed by Filter: 0 Buffers: shared hit=4690 Planning Time: 2.180 ms Execution Time: 29.789 ms (63 rows)
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" = 4059254 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0, 10, 20)) AND ("project_features"."repository_access_level" > 0 OR "project_features"."repository_access_level" IS NULL)) AND ("packages_packages"."package_type" != 4 OR "packages_packages"."name" != 'NuGet.Temporary.Package') AND "packages_packages"."version" IS NOT NULL AND "packages_packages"."status" IN (0, 3) ORDER BY "packages_packages"."created_at" ASC;
QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=47969.17..48428.71 rows=183815 width=83) (actual time=102.362..102.418 rows=707 loops=1) Sort Key: packages_packages.created_at Sort Method: quicksort Memory: 124kB Buffers: shared hit=21173 read=293 I/O Timings: read=47.510 -> Nested Loop (cost=23634.83..31896.48 rows=183815 width=83) (actual time=53.393..101.965 rows=707 loops=1) Buffers: shared hit=21173 read=293 I/O Timings: read=47.510 -> HashAggregate (cost=23634.40..23671.88 rows=3748 width=4) (actual time=34.369..34.992 rows=1349 loops=1) Group Key: projects.id Buffers: shared hit=16605 read=171 I/O Timings: read=2.339 -> Nested Loop Left Join (cost=1591.55..23625.03 rows=3748 width=4) (actual time=2.771..33.694 rows=1349 loops=1) Filter: ((project_features.repository_access_level > 0) OR (project_features.repository_access_level IS NULL)) Rows Removed by Filter: 13 Buffers: shared hit=16605 read=171 I/O Timings: read=2.339 -> Nested Loop (cost=1590.99..21277.98 rows=3751 width=4) (actual time=2.737..22.528 rows=1362 loops=1) Buffers: shared hit=9795 read=170 I/O Timings: read=2.326 -> HashAggregate (cost=1590.55..1592.46 rows=191 width=4) (actual time=2.637..2.898 rows=228 loops=1) Group Key: namespaces.id Buffers: shared hit=1144 -> CTE Scan on base_and_descendants namespaces (cost=1584.34..1588.16 rows=191 width=4) (actual time=0.031..2.566 rows=228 loops=1) Buffers: shared hit=1144 CTE base_and_descendants -> Recursive Union (cost=0.43..1584.34 rows=191 width=344) (actual time=0.028..2.241 rows=228 loops=1) Buffers: shared hit=1144 -> Index Scan using index_namespaces_on_type_and_id_partial on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=0.020..0.021 rows=1 loops=1) Index Cond: (((type)::text = 'Group'::text) AND (id = 9970)) Buffers: shared hit=4 -> Nested Loop (cost=0.56..157.71 rows=19 width=344) (actual time=0.019..0.287 rows=38 loops=6) Buffers: shared hit=1140 -> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.003 rows=38 loops=6) -> Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.56..15.73 rows=2 width=344) (actual time=0.005..0.007 rows=1 loops=228) Index Cond: (parent_id = base_and_descendants.id) Filter: ((type)::text = 'Group'::text) Buffers: shared hit=1140 -> Index Scan using index_projects_on_namespace_id_and_id on projects (cost=0.44..102.87 rows=20 width=8) (actual time=0.021..0.085 rows=6 loops=228) Index Cond: (namespace_id = namespaces.id) Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{0,10,20}'::integer[]))) Buffers: shared hit=8651 read=170 I/O Timings: read=2.326 SubPlan 1 -> Index Only Scan using project_authorizations_pkey on project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1362) Index Cond: ((user_id = 4059254) AND (project_id = projects.id)) Heap Fetches: 536 Buffers: shared hit=6529 read=170 I/O Timings: read=2.326 SubPlan 2 -> Index Only Scan using project_authorizations_pkey on project_authorizations project_authorizations_1 (cost=0.57..947.91 rows=4840 width=4) (never executed) Index Cond: (user_id = 4059254) Heap Fetches: 0 -> Index Scan using index_project_features_on_project_id on project_features (cost=0.56..0.61 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=1362) Index Cond: (projects.id = project_id) Buffers: shared hit=6810 read=1 I/O Timings: read=0.013 -> Index Scan using index_packages_packages_on_project_id_and_status on packages_packages (cost=0.43..1.70 rows=49 width=83) (actual time=0.044..0.049 rows=1 loops=1349) Index Cond: (project_id = projects.id) Filter: ((version IS NOT NULL) AND (status = ANY ('{0,3}'::integer[])) AND ((package_type <> 4) OR ((name)::text <> 'NuGet.Temporary.Package'::text))) Rows Removed by Filter: 0 Buffers: shared hit=4568 read=122 I/O Timings: read=45.171 Planning Time: 6.310 ms Execution Time: 102.725 ms (65 rows)
Package Finder (displayable scope)
SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 15835259 AND ("packages_packages"."package_type" != 4 OR "packages_packages"."name" != 'NuGet.Temporary.Package') AND "packages_packages"."id" = 1132381 LIMIT 1;
QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.43..3.45 rows=1 width=83) (actual time=0.104..0.104 rows=1 loops=1) Buffers: shared hit=7 -> Index Scan using index_packages_packages_on_id_and_created_at on packages_packages (cost=0.43..3.45 rows=1 width=83) (actual time=0.103..0.103 rows=1 loops=1) Index Cond: (id = 1132381) Filter: ((project_id = 15835259) AND ((package_type <> 4) OR ((name)::text <> 'NuGet.Temporary.Package'::text))) Buffers: shared hit=7 Planning Time: 0.410 ms Execution Time: 0.130 ms (8 rows)
SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 15835259 AND "packages_packages"."status" IN (0, 3) AND ("packages_packages"."package_type" != 4 OR "packages_packages"."name" != 'NuGet.Temporary.Package') AND "packages_packages"."id" = 1132381 LIMIT 1;
QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.43..3.46 rows=1 width=83) (actual time=0.019..0.020 rows=1 loops=1) Buffers: shared hit=4 -> Index Scan using index_packages_packages_on_id_and_created_at on packages_packages (cost=0.43..3.46 rows=1 width=83) (actual time=0.019..0.019 rows=1 loops=1) Index Cond: (id = 1132381) Filter: ((status = ANY ('{0,3}'::integer[])) AND (project_id = 15835259) AND ((package_type <> 4) OR ((name)::text <> 'NuGet.Temporary.Package'::text))) Buffers: shared hit=4 Planning Time: 0.327 ms Execution Time: 0.037 ms (8 rows)
☑ 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 #326229 (closed)