Group NuGet metadata query bypasses lower(name) partial index after deprecated status added to INSTALLABLE_STATUSES

Summary

The group-level NuGet metadata endpoint runs a slow, unindexed query at high volume. The partial index built to serve this lookup is no longer eligible for the query, so Postgres falls back to a worse plan.

Kibana: https://log.gprd.gitlab.net/app/r/s/GomSn

Endpoint: GET /api/:version/groups/:id/-/packages/nuget/metadata/*package_name/index

The query originates from the .exists? guard in find_packages (lib/api/helpers/packages/nuget.rb:10), which runs once per request. NuGet clients (dotnet restore) hit this endpoint once per package in the dependency graph, so the raw frequency is client-driven.

The query

SELECT 1 AS one FROM "packages_packages"
WHERE "packages_packages"."package_type" = 4
  AND "packages_packages"."project_id" IN (
    SELECT "projects"."id" FROM "projects"
    WHERE (( EXISTS (SELECT 1 FROM "project_authorizations"
                     WHERE user_id = ? AND project_id = projects.id
                       AND access_level >= ?)
             OR projects.visibility_level IN (?, ?))
        OR EXISTS (SELECT 1 FROM "project_features"
                   WHERE project_id = projects.id
                     AND package_registry_access_level = ?))
      AND "projects"."namespace_id" IN (
        SELECT "namespaces"."id" FROM "namespaces"
        WHERE "namespaces"."type" = 'Group' AND traversal_ids @> (?)))
  AND "packages_packages"."status" IN (0, 1, 5)
  AND "packages_packages"."version" IS NOT NULL
  AND LOWER("packages_packages"."name") = ?
LIMIT 1;

Root cause

There is a partial index purpose-built for this LOWER(name) lookup:

CREATE INDEX idx_pkgs_project_id_lower_name_when_nuget_installable_version
  ON packages_packages (project_id, lower(name::text))
  WHERE package_type = 4 AND version IS NOT NULL
    AND status = ANY (ARRAY[0, 1]);

Its predicate covers only status IN (0, 1) (default, hidden). The query now filters status IN (0, 1, 5):

enum: default=0, hidden=1, ..., deprecated=5
Packages::Package::INSTALLABLE_STATUSES = [:default, :hidden, :deprecated] # => [0, 1, 5]

Because a query matching status = 5 can return rows outside the index predicate, Postgres cannot use this partial index. It falls back to a broader plan (e.g. index_packages_project_id_name_partial_for_nuget on (project_id, name) plus a lower(name) filter), which degrades as a group's package count grows.

The deprecated status does not apply to NuGet

deprecated is an npm-only concept. The only code that writes status = deprecated is Packages::Npm::DeprecatePackageService (app/services/packages/npm/deprecate_package_service.rb:107). No NuGet, Conan, Maven, or PyPI path ever sets it, so a NuGet package can never have status = 5.

The problem is scope. INSTALLABLE_STATUSES lives on the shared base model Packages::Package, and Packages::Nuget::Package does not override installable_statuses. So every package type's installable scope filters status IN (0, 1, 5), even though only npm can ever produce a 5. For NuGet that status = 5 clause matches nothing; it is dead weight, and it is exactly what disqualifies the partial index.

When it broke

deprecated (status 5) was added to INSTALLABLE_STATUSES in !174312 (merged) (merged Dec 2024), an npm-focused change. It added deprecated to the shared base constant and did not update the NuGet index predicate, which still reads ARRAY[0, 1].

Why we are only seeing it now

Index eligibility is a planner decision based on the predicate, not on data, so the query lost this index the moment the MR merged in Dec 2024. This is a latent regression. It only surfaces as a slow query once the fallback plan gets expensive, which is data-dependent: a large group's NuGet package set grows, or the planner flips to a worse plan after ANALYZE. The deprecated backfill in that MR only touched npm packages, so it is not the trigger; the status IN (0, 1, 5) filter alone disqualifies the index regardless of whether any deprecated NuGet rows exist (none ever do).

Proposed fix

Preferred: override installable_statuses in Packages::Nuget::Package to return [:default, :hidden]. This reverts the query to status IN (0, 1), which the existing index already serves, needs no DB migration, and changes zero results because no NuGet row is ever deprecated.

Broader consideration: the same dead status = 5 filter also applies to Conan, Maven, PyPI, and other types that have no deprecation concept. The more complete fix is to make deprecated installable only for npm rather than on the shared base. That is a larger change touching every type and should be scoped separately.

Whichever direction is chosen, the ARRAY[0, 1] predicates on the other installable partial indexes remain a latent trap the day another type gains a deprecation flow, and are worth auditing:

  • idx_installable_conan_pkgs_on_project_id_id
  • idx_packages_on_project_id_name_id_version_when_installable_npm
  • idx_pkgs_on_project_id_name_version_on_installable_terraform
  • index_packages_on_available_pypi_packages

Alternative if the shared constant is kept: recreate idx_pkgs_project_id_lower_name_when_nuget_installable_version with status = ANY (ARRAY[0, 1, 5]) (add concurrently, drop the stale one). This restores index use but leaves the logically dead status = 5 clause in place.

Edited by 🤖 GitLab Bot 🤖