internal/postgres: optimize unit metadata lookup for unknown module paths

When a user requests a package with a symbolic version (e.g., @master)
and the module boundary is not yet known, pkgsite currently triggers
a "greedy" index scan on the modules table's series_path index to find
the best match.

This change optimizes the lookup by explicitly providing candidate
module paths using internal.CandidateModulePaths. This allows the
Postgres optimizer to perform efficient point lookups on the
module_path index rather than a linear scan.

Fixes golang/go#77367

Change-Id: I072a72dc043ff7fb704977f07ba9fbd4ec821815
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/741300
kokoro-CI: kokoro <noreply+kokoro@google.com>
Auto-Submit: Ethan Lee <ethanalee@google.com>
Reviewed-by: Jonathan Amsterdam <jba@google.com>
LUCI-TryBot-Result: Go LUCI <golang-scoped@luci-project-accounts.iam.gserviceaccount.com>

2 files changed, 43 insertions, 1 deletions

diff --git a/internal/postgres/unit.go b/internal/postgres/unit.go
index 33c58401..2ead3294 100644
--- a/internal/postgres/unit.go
+++ b/internal/postgres/unit.go
@@ -79,8 +79,11 @@ func (db *DB) getUnitMetaWithKnownVersion(ctx context.Context, fullPath, moduleP
 		query = query.Where(squirrel.Eq{"version": version})
 	}
 	if modulePath == internal.UnknownModulePath {
+		candidates := internal.CandidateModulePaths(fullPath)
 		// If we don't know the module, look for the one  with the longest series path.
-		query = query.OrderBy("m.series_path DESC").Limit(1)
+		query = query.Where(squirrel.Eq{"m.module_path": candidates}).
+			OrderBy("m.series_path DESC").
+			Limit(1)
 	} else {
 		query = query.Where(squirrel.Eq{"m.module_path": modulePath})
 	}
diff --git a/internal/postgres/unit_test.go b/internal/postgres/unit_test.go
index 60ebfb83..e8dc3046 100644
--- a/internal/postgres/unit_test.go
+++ b/internal/postgres/unit_test.go
@@ -202,6 +202,45 @@ func testGetUnitMeta(t *testing.T, ctx context.Context) {
 	}
 }
 
+func TestGetUnitMeta_UnknownModulePathMaster(t *testing.T) {
+	t.Parallel()
+	testDB, release := acquire(t)
+	defer release()
+	ctx := context.Background()
+
+	// Both modules contain the path "m.com/a/b" at the "master" version.
+	for _, testModule := range []struct {
+		module, version, suffix string
+	}{
+		{"m.com", "v1.0.0", "a/b"},
+		{"m.com/a", "v1.1.0", "b"},
+	} {
+		m := sample.Module(testModule.module, testModule.version, testModule.suffix)
+		MustInsertModule(ctx, t, testDB, m)
+		// Map the concrete version to the symbolic "master" version.
+		if err := testDB.UpsertVersionMap(ctx, &internal.VersionMap{
+			ModulePath:       m.ModulePath,
+			RequestedVersion: "master",
+			ResolvedVersion:  m.Version,
+		}); err != nil {
+			t.Fatal(err)
+		}
+	}
+
+	fullPath := "m.com/a/b"
+	got, err := testDB.GetUnitMeta(ctx, fullPath, internal.UnknownModulePath, "master")
+	if err != nil {
+		t.Fatal(err)
+	}
+
+	wantModule := "m.com/a"
+	wantVersion := "v1.1.0"
+	if got.ModulePath != wantModule || got.Version != wantVersion {
+		t.Errorf("GetUnitMeta(%q, unknown, master) = %s@%s; want %s@%s",
+			fullPath, got.ModulePath, got.Version, wantModule, wantVersion)
+	}
+}
+
 func TestGetLatestUnitVersion(t *testing.T) {
 	ctx := context.Background()