Update packages finder helper to use Namespace#all_projects
Part of #324220 (closed)
Following on from !56346 (merged), we're still seeing huge numbers of tuple fetches from the packages_packages
table.
These are leading to large numbers of alerts in pagerduty and slack.
Up to 50% of all tuple fetches across all replicas are for the packages_packages
table, driven by a relatively small number of Maven repository accesses by a single customer, https://gitlab.com/gitlab-com/gl-infra/scalability/-/issues/620.
This query dominates the slowlog:
/*application:web,correlation_id:01F12KRTERTGWCB4D5AN5BAZJS,endpoint_id:/api/:version/groups/:id/-/packages/mavenpath/:file_name*/
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" IN (
SELECT
"projects"."id"
FROM
"projects"
INNER JOIN (
SELECT
"id"
FROM (
SELECT
"namespaces".*
FROM
"namespaces"
INNER JOIN ( WITH RECURSIVE "base_and_descendants" AS ((
SELECT
$1 AS depth,
ARRAY[namespaces.id] AS tree_path,
$2 AS tree_cycle,
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = $3
AND "namespaces"."id" = $4)
UNION (
SELECT
("base_and_descendants"."depth" + $5),
tree_path || "namespaces".id,
"namespaces".id = ANY (tree_path),
"namespaces".*
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = $6
AND "namespaces"."parent_id" = "base_and_descendants"."id"
AND "base_and_descendants"."tree_cycle" = $7))
SELECT DISTINCT
"id",
"depth"
FROM
"base_and_descendants" AS "namespaces") namespaces_join_table ON namespaces_join_table.id = namespaces.id
WHERE
"namespaces"."type" = $8
ORDER BY
"namespaces_join_table"."depth" ASC) AS "namespaces"
WHERE
"namespaces"."type" = $9) namespaces ON namespaces.id = projects.namespace_id
WHERE (EXISTS (
SELECT
$10
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = $11
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN ($12, $13)))
AND "packages_maven_metadata"."path" = $14
ORDER BY
"packages_packages"."id" DESC
LIMIT $15