perf(packages): add (ref_type, ref_id) index to package_property - #39465
Open
UnderwaterOverground wants to merge 3 commits into
Open
UnderwaterOverground wants to merge 3 commits into
UnderwaterOverground wants to merge 3 commits into
Conversation
Every package property lookup filters on both ref_type and ref_id, but the table only has single-column indexes. SQLite has no ANALYZE statistics by default, so the planner can pick the ref_type index (only three distinct values) and scan every property of that type for each lookup. On a registry with ~30k file properties this made container ListPackages API calls take 14-22s. A composite (ref_type, ref_id) index is chosen regardless of statistics. It deliberately excludes name: GetProperties orders by id, and SQLite prefers a single-column index over (ref_type, ref_id, name) for that.
GiteaBot
added
the
lgtm/need 2
wxiaoguang
reviewed
Sep 29, 2026
| cols = append(cols, idx.Cols) | ||
| } | ||
| assert.Contains(t, cols, []string{"ref_type", "ref_id"}) | ||
| assert.Contains(t, cols, []string{"ref_type"}) |
Contributor
There was a problem hiding this comment.
If ("ref_type", "ref_id") exists, ("ref_type") is just a duplicate and useless.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Every package property lookup (
GetProperties,GetPropertiesByName,InsertOrUpdateProperty,DeleteAllProperties, ...) filters onref_typeandref_id, butpackage_propertyonly has single-column indexes onref_type,ref_idandname.On SQLite, which has no
ANALYZEstatistics unless someone runs it manually, the planner has no way to tell those indexes apart. On my instance it pickedIDX_package_property_ref_type, which has only three distinct values, so every lookup scanned all ~30k file properties. ContainerListPackagescalls (GET /api/v1/packages/{owner}?type=container&q=...) took 14–22 s each and showed up as[Slow SQL Query]in the logs.This adds a composite
(ref_type, ref_id)index plus migration 356.nameis deliberately left out:GetPropertiesorders byid, and in my tests SQLite still chose the single-column index over(ref_type, ref_id, name).Measured on a copy of the affected production SQLite DB (SQLite 3.50.4, no
sqlite_stat1), 300 ×SELECT ... FROM package_property WHERE ref_type = 1 AND ref_id = ? ORDER BY id:USING INDEX IDX_package_property_ref_type (ref_type=?)(ref_type, ref_id, name)ref_type)(ref_type, ref_id)(this PR)USING INDEX IDX_package_property_ref (ref_type=? AND ref_id=?)After the fix, the API calls went from ~15 s to ~100 ms. Running
ANALYZEmanually also fixes it, but only until the next time nobody runs it.Existing single-column indexes are kept (
IgnoreDropIndices);ref_idandnameare still used by joins and name/value searches elsewhere.Tests:
TestAddPackagePropertyRefIndex(new),go test ./models/packages/....AI disclosure: I used an AI assistant (Claude) to investigate the slow query, draft the change and write this description. I reviewed the change and reproduced the before/after numbers above on my own instance.