internal/postgres: optimize getSymbolHistoryForBuildContext query
getSymbolHistoryForBuildContext now accepts a pathID instead of
packagePath as an argument, to avoid a JOIN on the paths table.
For golang/go#37102
Change-Id: I4e6ac004eb56f2c7744c2ebd7b0e8dcd995f0f4f
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/316230
Trust: Julie Qiu <julie@golang.org>
Reviewed-by: Jonathan Amsterdam <jba@google.com>
diff --git a/internal/postgres/path.go b/internal/postgres/path.go
index 239a0be..82c0457 100644
--- a/internal/postgres/path.go
+++ b/internal/postgres/path.go
@@ -167,3 +167,10 @@
}
return pathToID, nil
}
+
+func getPathID(ctx context.Context, ddb *database.DB, path string) (id int, err error) {
+ err = ddb.QueryRow(ctx,
+ `SELECT id FROM paths WHERE path = $1`,
+ path).Scan(&id)
+ return id, err
+}
diff --git a/internal/postgres/symbol_history.go b/internal/postgres/symbol_history.go
index 9c8272a..50c03c5 100644
--- a/internal/postgres/symbol_history.go
+++ b/internal/postgres/symbol_history.go
@@ -109,6 +109,8 @@
// GetSymbolHistoryWithPackageSymbols is exported for use in tests.
func GetSymbolHistoryWithPackageSymbols(ctx context.Context, ddb *database.DB,
packagePath, modulePath string) (_ map[string]map[string]*internal.UnitSymbol, err error) {
+ defer derrors.WrapStack(&err, "GetSymbolHistoryWithPackageSymbols(ctx, ddb, %q, %q)", packagePath, modulePath)
+ defer middleware.ElapsedStat(ctx, "GetSymbolHistoryWithPackageSymbols")()
versionToNameToUnitSymbols, err := getPackageSymbols(ctx, ddb, packagePath, modulePath)
if err != nil {
return nil, err
@@ -116,39 +118,18 @@
return symbol.IntroducedHistory(versionToNameToUnitSymbols), nil
}
-// GetSymbolHistoryForBuildContext returns a map of the first version when a symbol name is
+// getSymbolHistoryForBuildContext returns a map of the first version when a symbol name is
// added to the API for the specified build context, to the symbol name, to the
// UnitSymbol struct. The UnitSymbol.Children field will always be empty, as
// children names are also tracked.
-func (db *DB) GetSymbolHistoryForBuildContext(ctx context.Context, packagePath, modulePath string,
- build internal.BuildContext) (nameToVersion map[string]string, err error) {
- defer derrors.Wrap(&err, "GetSymbolHistoryForBuildContext(ctx, %q, %q)", packagePath, modulePath)
- defer middleware.ElapsedStat(ctx, "GetSymbolHistoryForBuildContext")()
-
- if experiment.IsActive(ctx, internal.ExperimentReadSymbolHistory) {
- if build.GOOS == internal.All {
- // It doesn't matter which one we use, so just pick a random one.
- build = internal.BuildContextLinux
- }
- return getSymbolHistoryForBuildContext(ctx, db.db, packagePath, modulePath, build)
- }
-
- versionToNameToUnitSymbol, err := GetSymbolHistoryWithPackageSymbols(ctx, db.db, packagePath, modulePath)
- if err != nil {
- return nil, err
- }
- nameToVersion = map[string]string{}
- for v, nts := range versionToNameToUnitSymbol {
- for n := range nts {
- nameToVersion[n] = v
- }
- }
- return nameToVersion, nil
-}
-
-func getSymbolHistoryForBuildContext(ctx context.Context, ddb *database.DB, packagePath, modulePath string,
+func getSymbolHistoryForBuildContext(ctx context.Context, ddb *database.DB, pathID int, modulePath string,
bc internal.BuildContext) (_ map[string]string, err error) {
- defer derrors.WrapStack(&err, "getSymbolHistoryForBuildContext(ctx, ddb, %q, %q)", packagePath, modulePath)
+ defer derrors.WrapStack(&err, "getSymbolHistoryForBuildContext(ctx, ddb, %d, %q)", pathID, modulePath)
+ defer middleware.ElapsedStat(ctx, "getSymbolHistoryForBuildContext")()
+
+ if bc == internal.BuildContextAll {
+ bc = internal.BuildContextLinux
+ }
q := squirrel.Select(
"s1.name AS symbol_name",
@@ -157,9 +138,8 @@
Join("package_symbols ps ON ps.id = sh.package_symbol_id").
Join("symbol_names s1 ON ps.symbol_name_id = s1.id").
Join("symbol_names s2 ON ps.parent_symbol_name_id = s2.id").
- Join("paths p1 ON sh.package_path_id = p1.id").
Join("paths p2 ON sh.module_path_id = p2.id").
- Where(squirrel.Eq{"p1.path": packagePath}).
+ Where(squirrel.Eq{"sh.package_path_id": pathID}).
Where(squirrel.Eq{"p2.path": modulePath}).
Where(squirrel.Eq{"sh.goos": bc.GOOS}).
Where(squirrel.Eq{"sh.goarch": bc.GOARCH})
diff --git a/internal/postgres/symbol_test.go b/internal/postgres/symbol_test.go
index 8bc7218..04d42a6 100644
--- a/internal/postgres/symbol_test.go
+++ b/internal/postgres/symbol_test.go
@@ -404,8 +404,12 @@
t.Fatalf("mismatch on symbol history(-want +got):\n%s", diff)
}
- gotHist2, err := testDB.GetSymbolHistoryForBuildContext(ctx,
- mod10.Packages()[0].Path, mod10.ModulePath, internal.BuildContextWindows)
+ pathID, err := getPathID(ctx, testDB.db, mod10.Packages()[0].Path)
+ if err != nil {
+ t.Fatal(err)
+ }
+ gotHist2, err := getSymbolHistoryForBuildContext(ctx, testDB.db,
+ pathID, mod10.ModulePath, internal.BuildContextWindows)
if err != nil {
t.Fatal(err)
}
diff --git a/internal/postgres/unit.go b/internal/postgres/unit.go
index d19f72a..312f80f 100644
--- a/internal/postgres/unit.go
+++ b/internal/postgres/unit.go
@@ -403,10 +403,10 @@
defer middleware.ElapsedStat(ctx, "getUnitWithAllFields")()
// Get build contexts and unit ID.
- var unitID int
+ var pathID, unitID int
var bcs []internal.BuildContext
err = db.db.RunQuery(ctx, `
- SELECT d.goos, d.goarch, u.id
+ SELECT d.goos, d.goarch, u.id, p.id
FROM units u
INNER JOIN paths p ON p.id = u.path_id
INNER JOIN modules m ON m.id = u.module_id
@@ -419,7 +419,7 @@
var bc internal.BuildContext
// GOOS and GOARCH will be NULL if there are no documentation rows for
// the unit, but we still want the unit ID.
- if err := rows.Scan(database.NullIsEmpty(&bc.GOOS), database.NullIsEmpty(&bc.GOARCH), &unitID); err != nil {
+ if err := rows.Scan(database.NullIsEmpty(&bc.GOOS), database.NullIsEmpty(&bc.GOARCH), &unitID, &pathID); err != nil {
return err
}
if bc.GOOS != "" && bc.GOARCH != "" {
@@ -512,12 +512,29 @@
u.Subdirectories = pkgs
u.UnitMeta = *um
- if experiment.IsActive(ctx, internal.ExperimentSymbolHistoryMainPage) {
- u.SymbolHistory, err = db.GetSymbolHistoryForBuildContext(ctx, um.Path, um.ModulePath, bcMatched)
+ if !experiment.IsActive(ctx, internal.ExperimentSymbolHistoryMainPage) {
+ return &u, nil
+ }
+ if experiment.IsActive(ctx, internal.ExperimentReadSymbolHistory) {
+ u.SymbolHistory, err = getSymbolHistoryForBuildContext(ctx, db.db, pathID, um.ModulePath, bcMatched)
if err != nil {
return nil, err
}
+ return &u, nil
}
+
+ versionToNameToUnitSymbol, err := GetSymbolHistoryWithPackageSymbols(ctx, db.db, um.Path,
+ um.ModulePath)
+ if err != nil {
+ return nil, err
+ }
+ nameToVersion := map[string]string{}
+ for v, nts := range versionToNameToUnitSymbol {
+ for n := range nts {
+ nameToVersion[n] = v
+ }
+ }
+ u.SymbolHistory = nameToVersion
return &u, nil
}
