Performance Issue - Packages::Npm::PackageFinder query when querying for all packages within a namespace results in query timeouts
Description
We're seeing a lot of query timeouts in the packages/npm
endpoint after globally enabling the npm_allow_packages_in_multiple_projects
feature flag.
This issue is blocking the feature flag rollout.
https://log.gprd.gitlab.net/goto/019b6d70-c193-11ed-a017-0d32180b1390 (filter for March 9 to March 10 data)
Exception SQL:
[
"/*application:web,correlation_id:74ea8c2c4cac84c51d7db13d7ffc2406,endpoint_id:GET /api/:version/packages/npmpackage_name,db_config_name:main_replica*/ SELECT \"packages_packages\".\"id\" FROM \"packages_packages\" WHERE \"packages_packages\".\"project_id\" IN (SELECT \"projects\".\"id\" FROM \"projects\" WHERE \"projects\".\"namespace_id\" IN (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, $1)] AS id FROM \"namespaces\" WHERE \"namespaces\".\"type\" = $2 AND (traversal_ids @> ($3)))) AND \"packages_packages\".\"package_type\" = $4 AND \"packages_packages\".\"name\" = $5 AND \"packages_packages\".\"status\" IN ($6, $7) AND \"packages_packages\".\"id\" IN (SELECT MAX(id) AS id FROM \"packages_packages\" WHERE \"packages_packages\".\"project_id\" IN (SELECT \"projects\".\"id\" FROM \"projects\" WHERE \"projects\".\"namespace_id\" IN (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, $8)] AS id FROM \"namespaces\" WHERE \"namespaces\".\"type\" = $9 AND (traversal_ids @> ($10)))) AND \"packages_packages\".\"package_type\" = $11 AND \"packages_packages\".\"name\" = $12 AND \"packages_packages\".\"status\" IN ($13, $14) GROUP BY \"packages_packages\".\"version\") ORDER BY \"packages_packages\".\"id\" ASC LIMIT $15"
]
The query that retrieves all the packages inside a namespace needs to be optimized.
Stacktrace:
- NPM Metadata endpoint
-
NPM Package Finder - when
base
ispackages_for_namespace
.
SQL Statement
EXPLAIN SELECT "packages_packages".*
FROM "packages_packages"
WHERE "packages_packages"."project_id" IN
(SELECT "projects"."id"
FROM "projects"
WHERE "projects"."namespace_id" IN
(SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND (traversal_ids @> ('{39}')))
)
AND "packages_packages"."package_type" = 2
AND "packages_packages"."name" = '@testing-group/hellonpm'
AND "packages_packages"."status" IN (0, 1)
AND "packages_packages"."id" IN
(SELECT MAX(id) AS id
FROM "packages_packages"
WHERE "packages_packages"."project_id" IN
(SELECT "projects"."id"
FROM "projects"
WHERE "projects"."namespace_id" IN
(SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND (traversal_ids @> ('{39}'))
)
)
AND "packages_packages"."package_type" = 2
AND "packages_packages"."name" = '@testing-group/hellonpm'
AND "packages_packages"."status" IN (0, 1)
GROUP BY "packages_packages"."version"
)
Edited by Radamanthus Batnag