Optimize pagination query for inherited variables resolver
What does this MR do and why?
Related to issue: #413418 (closed)
Optimize GraphQL resolver instead of batching all variables and paginating against array introduces a single SQL query to fetch variables for all requested groups in one SQL query.
Screenshots or screen recordings
Before:
Ci::GroupVariable Load (0.2ms) SELECT "ci_group_variables".* FROM "ci_group_variables" WHERE "ci_group_variables"."group_id" = 25
Ci::GroupVariable Load (0.2ms) SELECT "ci_group_variables".* FROM "ci_group_variables" WHERE "ci_group_variables"."group_id" = 83
After
SELECT "ci_group_variables".* FROM "ci_group_variables" WHERE "ci_group_variables"."group_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (25, 83)) ORDER BY "ci_group_variables"."id" DESC LIMIT 6
Database Queries
SELECT "ci_group_variables".* FROM "ci_group_variables" WHERE "ci_group_variables"."group_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (62954852, 56722522)) ORDER BY "ci_group_variables"."id" DESC LIMIT 100
Time: 38.836 ms
How to set up and validate locally
- Navigate to the graphql explorer
http://localhost:3000/-/graphql-explorer. # can be different based on gdk config
- Copy and paste and run the following query to the graphql explorer to test:
query getInheritedCiVariables {
project(fullPath: "gnuwget/subwget/projsubwget") {
id
inheritedCiVariables(first: 5) {
pageInfo {
hasNextPage
hasPreviousPage
startCursor
endCursor
}
nodes {
__typename
id
key
variableType
environmentScope
groupCiCdSettingsPath
groupName
masked
protected
raw
}
}
}
}
Example of output:
{
"data": {
"project": {
"id": "gid://gitlab/Project/25",
"inheritedCiVariables": {
"pageInfo": {
"hasNextPage": true,
"hasPreviousPage": false,
"startCursor": "eyJpZCI6IjIwIn0",
"endCursor": "eyJpZCI6IjE2In0"
},
"nodes": [
{
"__typename": "InheritedCiVariable",
"id": "gid://gitlab/Ci::GroupVariable/20",
"key": "sdfsdfdsfsd",
"variableType": "ENV_VAR",
"environmentScope": "*",
"groupCiCdSettingsPath": "/groups/gnuwget/-/settings/ci_cd",
"groupName": "Gnuwget",
"masked": false,
"protected": true,
"raw": false
}]
}
}
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.
Edited by Dmytro Biryukov