Improve GQL query for fetching groups and projects in token allowlist
What does this MR do and why?
Implements #473747 (closed)
Problem
When adding a group or project to the allowlist for CI_JOB_TOKEN
, the search is limited to showing up to 4 items in the results.
This means that some groups/projects will not show up if there are more than 4 groups/projects with the same name or path. In some cases, it becomes impossible to add a parent subgroup, even when typing the full path of the parent group.
Proposed Solution
The previous implementation of this issue was reverted since the query resulted in 503
errors (see #473747 (comment 2076629688)). This is likely due to the inclusion of searchNamespaces: true
, making the query expensive to run. See the query plan below, where the query takes around 78s to execute.
In this MR, we removed searchNamespaces: true
and instead sorted by id
and increased the limit
of the search results from 4
to 10
. sort: id_asc
will help prioritize groups that were created first. It is more likely that groups with shorter full paths will show up in the results this way, though this will not be guaranteed.
Generated SQL
Current SQL
For groups: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31176/commands/96847
Time: 5.681 s
- planning: 6.066 ms
- execution: 5.675 s
- I/O read: 5.230 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 8689 (~67.90 MiB) from the buffer pool
- reads: 8100 (~63.30 MiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."type" != 'Project'
AND ("namespaces"."path" ILIKE '%$1%'
OR "namespaces"."name" ILIKE '%$1%')
LIMIT 4;
For projects: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31176/commands/96848
Time: 15.077 ms
- planning: 8.889 ms
- execution: 6.188 ms
- I/O read: 5.555 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 0 from the buffer pool
- reads: 8 (~64.00 KiB) from the OS file cache, including disk I/O
- dirtied: 1 (~8.00 KiB)
- writes: 0
SELECT
"projects"."id"
FROM
"projects"
WHERE (("projects"."path" ILIKE '%$1%'
OR "projects"."name" ILIKE '%$1%')
OR "projects"."description" ILIKE '%$1%')
LIMIT 4;
Proposed Changes
(Limit increased to 10
, sorted by id_asc
)
For groups: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31176/commands/96849
Time: 1.101 s
- planning: 7.996 ms
- execution: 1.093 s
- I/O read: 11.767 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 16793 (~131.20 MiB) from the buffer pool
- reads: 589 (~4.60 MiB) from the OS file cache, including disk I/O
- dirtied: 5 (~40.00 KiB)
- writes: 0
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."type" != 'Project'
AND ("namespaces"."path" ILIKE '%$1%'
OR "namespaces"."name" ILIKE '%$1%')
ORDER BY
"namespaces"."id" ASC
LIMIT 10;
For projects: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31176/commands/96850
Time: 10.706 ms
- planning: 9.157 ms
- execution: 1.549 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 492 (~3.80 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
SELECT
"projects"."id"
FROM
"projects"
WHERE (("projects"."path" ILIKE '%$1%'
OR "projects"."name" ILIKE '%$1%')
OR "projects"."description" ILIKE '%$1%')
ORDER BY
"projects"."id" ASC
LIMIT 10;
Reverted Changes
(Sorted by id_asc
; projects have searchNamespaces: true
)
For groups: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31176/commands/96853
Time: 1.086 min
- planning: 6.214 ms
- execution: 1.086 min
- I/O read: 1.068 min
- I/O write: 0.000 ms
Shared buffers:
- hits: 17260 (~134.80 MiB) from the buffer pool
- reads: 99321 (~775.90 MiB) from the OS file cache, including disk I/O
- dirtied: 1080 (~8.40 MiB)
- writes: 0
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."type" != 'Project'
AND ("namespaces"."path" ILIKE '%$1%'
OR "namespaces"."name" ILIKE '%$1%')
ORDER BY
"namespaces"."id" ASC
LIMIT 4;
For projects: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31176/commands/96860
Project.select('id').search("gitlab", include_namespace: true).order_by("id_asc").limit(4).to_sql
Time: 78.163 ms
- planning: 14.152 ms
- execution: 64.011 ms
- I/O read: 62.720 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 107 (~856.00 KiB) from the buffer pool
- reads: 45 (~360.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
SELECT
"projects"."id"
FROM
"projects"
INNER JOIN "routes" ON "routes"."source_type" = 'Project'
AND "routes"."source_id" = "projects"."id"
WHERE (("routes"."path" ILIKE '%$1%'
OR "routes"."name" ILIKE '%$1%')
OR "projects"."description" ILIKE '%$1%')
ORDER BY
"projects"."id" ASC
LIMIT 4;
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Screenshots or screen recordings
Search Term | Before | After |
---|---|---|
cookie |
||
dessert |
||
dessert/dessert |
|
How to set up and validate locally
- Create more than 4 subgroups, each one nested by the previous. Create one group for each subgroup, then a subgroup for each subgroup. Make sure the subgroups have the same name, and the projects also have the same name. You should have a setup like the following:
group/subgroup1/project
group/subgroup1/subgroup/project
group/subgroup1/subgroup/subgroup/project
group/subgroup1/subgroup/subgroup/subgroup/project
group/subgroup1/subgroup/subgroup/subgroup/subgroup/project
- Go to the CI/CD Settings and expand Job token permissions
- Click Add group or project and try to search for the project name, project path, subgroup name, and subgroup path. You should have better search results than before the GQL was updated.