Add index with nupkg file extension to the package files table
Description
NuGet packages can have both .snupkg
and .nupkg
files docs. In that case, before sending a package file, we filter them by the format: "packages_package_files"."file_name" ILIKE '%.nupkg'
. This makes the use of GIN index that isn't combined with other indexes and results in a quite slow database query. Example: link (internal).
Sentry error: https://new-sentry.gitlab.net/organizations/gitlab/issues/848134
Proposal
Add a new index that contains nupkg
file extension:
CREATE INDEX index_files_on_extension_status_package ON packages_package_files (package_id) WHERE ((status = 0) AND (split_part(file_name, '.', -1) = 'nupkg'));
Example of query that uses a new index https://console.postgres.ai/gitlab/gitlab-production-main/sessions/28397/commands/88730
Edited by Moaz Khalifa