Update query plan in Ci::Catalog::Listing to use denormalized columns
What does this MR do and why?
In !134708 (merged) we denormalized the columns name
and description
from the projects
table and added them to the catalog_resources
table (along with trigram indexes on both columns). Now we can use these columns directly instead of relying on the search functionality from ProjectsFinder
.
This MR updates the query plan in Ci::Catalog::Listing
by replacing the use of ProjectsFinder
with a native search scope on the Ci::Catalog::Resource
model. This improves the search query peformance. The overall functionality has not changed.
Resolves Step 4 of #427928 (closed)
Query plan
SELECT "catalog_resources".* FROM "catalog_resources" WHERE ("catalog_resources"."name" ILIKE '%cat%' OR "catalog_resources"."description" ILIKE '%cat%')
Query plan link:
How to set up and validate locally
Verify that the GraphQl ciCatalogResources
search functionality still works as expected:
- Under the same parent group, create 2 projects with README files named
Orange Project
andApple Project
. (These names need to be unique. We simply want to set up a situation where only one or both of these projects are returned by the search filter.) - Update the description of
Orange Project
toFoo
; update the description ofApple Project
toOrange
. - In the console, first mark
Orange Project
as a catalog resource, then markApple Project
as a catalog resource.
Ci::Catalog::Resource.create!(project: Project.find_by(name: 'Orange Project'))
Ci::Catalog::Resource.create!(project: Project.find_by(name: 'Apple Project'))
- Go to
http://gdk.test:3000/
and test the following query (update the group name as necessary; usescope: "all"
if it has replacedprojectPath
by now.):
query {
ciCatalogResources(projectPath: "group-a/orange-project", search: "orange") {
nodes {
id
name
}
}
}
Observe that both projects from Step 1 are in the response.
- If you change the search term to
"apple"
, then onlyApple Project
will show.
- Optional: You can additionally test the search with a sort param:
query {
ciCatalogResources(projectPath: "group-a/orange-project", search: "orange", sort: NAME_DESC) {
nodes {
id
name
}
}
}
Orange Project
should appear first in the list.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #427928 (closed)