internal/postgres: speed up imported-by counts
The SQL statement that updated imported-by counts was using an unindexed
column (package_path) in search_documents, so it ran very slowly (5
minutes to update 1000 rows, for example.)
Switching to an indexed column (package_path_id) should make it faster.
Change-Id: I8a32aff6ed43870af4508c382e3d9dabcec3155d
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/567137
LUCI-TryBot-Result: Go LUCI <golang-scoped@luci-project-accounts.iam.gserviceaccount.com>
kokoro-CI: kokoro <noreply+kokoro@google.com>
Reviewed-by: Michael Matloob <matloob@golang.org>
diff --git a/internal/postgres/search.go b/internal/postgres/search.go
index 36cb72a..db9c746 100644
--- a/internal/postgres/search.go
+++ b/internal/postgres/search.go
@@ -902,7 +902,8 @@
imported_by_count = c.imported_by_count,
imported_by_count_updated_at = CURRENT_TIMESTAMP
FROM computed_imported_by_counts c
- WHERE s.package_path = c.package_path;`
+ INNER JOIN paths p ON p.path = c.package_path
+ WHERE s.package_path_id = p.id;`
n, err := db.Exec(ctx, updateStmt)
if err != nil {
