GeezerVison

What

Replace Python repository-version content lookups based on pk__in=repository_version.content with a direct version_memberships join driven by the package-name predicate.

This affects:

• PyPI Simple package detail responses: /pypi/<distribution>/<package>/;
• PyPI package metadata responses: /pypi/<distribution>/<package>/json;
• published Python distribution JSON responses.

Why

The existing query expands the repository version's content_ids array before applying the package filter. GABI tests against production data showed that a direct membership join avoids array expansion and avoids the poor plan selected for a correlated EXISTS candidate.

Performance Insights ranked the affected SQL shapes among the highest database-load statements over the seven-day production window: the PyPI Simple detail query ranked #4 (B4233AAA3E27C4F1CD1366E8153D8FE3864735D6) and the Python metadata query ranked #5 (51F6729E84EE2E32271BAACF399A8A5D2A885FA3).

These are good optimization candidates because they do not require index-level changes. This is a targeted, lower-risk fix than the broader interval-query experiment attempted in pulp-service#1643, specifically patch 0081 (Jan's interval-query experiment).

Full metadata query results

Each repository's largest package. Warm execution times in milliseconds.

The four SQL shapes tested are:

1. Current: pk__in=repository_version.content, backed by unnest(content_ids).
2. Correlated EXISTS: package filter plus per-row core_repositorycontent membership check.
3. IN subquery: package filter plus content_ptr_id__in=RepositoryContent subquery.
4. Direct join: package filter plus version_memberships repository/version predicates; this PR's shape.

The direct join preserves historical membership with version_added and version_removed bounds.

PyPI Simple detail query results

The same repositories and largest package, testing the narrower SQL shape used by /pypi/<distribution>/<package>/:

The direct join preserves historical membership with version_added and version_removed bounds.

Validation

• Generated SQL from Django ORM in an OCI Env with pulpcore and pulp_python.
• Compared current, correlated-EXISTS, IN-subquery, and direct-join SQL with GABI using EXPLAIN (ANALYZE, BUFFERS, TIMING OFF).
• Tested both Python SQL shapes across the ten largest current Python repository versions.
• Tested representative largest packages: boto3, uv-build, and posthog.
• git diff --check passed.
• Python bytecode compilation passed.
• Repository pre-commit checks passed.