x/pkgsite: optimize database queries

[findleyr]

As we've seen in #75959 (comment), there is some low hanging fruit in pkgsite's database queries. Since we've been analyzing our queries recently, we should take this opportunity to implement some tactical optimizations.

Specifically, here are some operations that could be faster:

Worker

• module cleaning involves an extremely expensive scan that could be optimized with an index. Additionally @ethanalee-work reports that left joins are significantly faster than EXCEPT clauses.
• computing the staleness timestamp for monitoring again involves an inefficient scan.

Frontend

• serving documentation is dominated by an index scan to compute the number of imports for a package. But this is completely deterministic at ingestion time, and could be denormalized.
• loading directories is the other major source of cost in serving a documentation page. We could partially denormalize this information, and/or defer loading all the directories until the user actually expands the directory viewer.

We don't need to do all of these, and we may find others as we optimize away the lowest hanging fruit.

CC @jba @ethanalee-work

[gabyhelp]

Related Issues

• x/pkgsite: use moduleID in getPackagesInUnit #45854 (closed)
• x/pkgsite: explore using pgx for fast bulk inserts #42707 (closed)

_{(Emoji vote if this was helpful or unhelpful; more detailed feedback welcome in this discussion.)}

[gopherbot]

Change https://go.dev/cl/719800 mentions this issue: internal/postgres: use JOINs in GetModuleVersionsToClean query

[ethanalee-work]

Thanks, we have a CL for speeding up the module cleaning - https://go-review.googlesource.com/c/pkgsite/+/719800.

There should be a ~3x performance improvement (example duration):

Metric	Original Query (EXCEPT)	Corrected Query (LEFT JOIN)	Improvement
Execution Time	~69,000 ms (~69 seconds)	23,442.668 ms (~23 seconds)	~3x faster

There is probably more performance improvement that we can derive from this by adding an additional index.

[ethanalee-work]

Adding an index over version_map (requested_version, module_path, resolved_version) results in a ~15x speedup.

[gopherbot]

Change https://go.dev/cl/720040 mentions this issue: migrations: create version_map (requested_version, module_path, resolved_version) index

[ethanalee-work]

Adding a partial index over:

CREATE INDEX idx_mvs_unprocessed_timestamp
ON module_version_states (index_timestamp)
WHERE last_processed_at IS NULL;

results in a huge improvement for computing the staleness timestamp

Metric	Before Indexes	After Indexes	Improvement
Execution Time	26,495.884 ms (~26.5 seconds)	1.849 ms	~14,330x faster

[gopherbot]

Change https://go.dev/cl/720080 mentions this issue: migrations: add partial module_version_states (index_timestamp) index