Backend: Add index on catalog_resources.search_vector to improve search query performance
Summary
In #430889 (closed), we implemented Postgres Full Text Search functionality on the name
and description
columns in catalog_resources
. The search_vector
value is auto generated when the name/description is updated.
Initially, composite search vector indexes with visibility_level
were planned to be added in #429056 (closed). However, as private projects will remain included and combined with Public/Internal data in the Ci::Catalog::Listing
queries (per 2023-11-29 CI Catalog weekly meeting), it's uncertain how much of a benefit these indices would offer or if they need to be changed.
Proposal
For now we should at least add a non-composite index on catalog_resources.search_vector
. With reference to the Postgres Full text index documentation, we can add an index like so:
CREATE INDEX index_name ON catalog_resources USING GIN (search_vector);
Note: It may be advisable to add the index in a post deploy migration rather than a regular migration.