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.

image

https://dashboards.gitlab.net/d/alerts-pg_user_tables_replica/alerts-pg-user-table-alerts-replicas?orgId=1&from=now-6h%2Fm&to=now-1m%2Fm&var-PROMETHEUS_DS=Global&var-environment=gprd&var-relname=packages_packages

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
Edited by Andrew Newdigate