Denormalize namespace traversal ids to match builds for group runners
What does this MR do and why?
This merge request decouples group runners query from namespaces table.
Database
The merge request add a denormalization of namespace_traversal_ids to match builds for group runners more efficiently and to decouple this query from namespaces and projects tables to unblock groupsharding team.
This should be also more efficient because we are using self and ancestors strategy to match builds and namespaces in the most efficient way possible, making use of the limit of nesting groups / namespaces we have set to 20 levels.
The old query for group runners
SELECT "ci_pending_builds".*
FROM "ci_pending_builds"
WHERE "ci_pending_builds"."project_id" IN
(SELECT "projects"."id"
FROM "projects"
INNER JOIN "project_ci_cd_settings" ON "project_ci_cd_settings"."project_id" = "projects"."id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "projects"."namespace_id" IN
(WITH RECURSIVE "base_and_descendants" AS (
(SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
FROM "namespaces"
INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."namespace_id" = "namespaces"."id"
WHERE "namespaces"."type" = 'Group'
AND "ci_runner_namespaces"."runner_id" = 6)
UNION
(SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND "project_ci_cd_settings"."group_runners_enabled" = TRUE
AND ("project_features"."builds_access_level" > 0
OR "project_features"."builds_access_level" IS NULL)
AND "projects"."pending_delete" = FALSE)
AND (tag_ids = {})
ORDER BY build_id ASC;The new query for group runners
New query:
SELECT
"ci_pending_builds".*
FROM
"ci_pending_builds"
WHERE (namespace_traversal_ids && ARRAY[193]::int[])
AND (tag_ids <@ ARRAY[123]::int[]);
/* AND (tag_ids = '{}'); */10k pending builds
No index: query plan
Planning Time: 1.463 ms
Execution Time: 18.912 msGin index: query plan
Planning Time: 0.927 ms
Execution Time: 0.198 ms120k pending builds
No index: query plan
Planning Time: 0.228 ms
Execution Time: 50.327 msGin index: query plan
Planning Time: 0.770 ms
Execution Time: 2.338 ms520k pending builds
No index: query plan
Planning Time: 0.256 ms
Execution Time: 80.576 msGin index: query plan
Planning Time: 0.237 ms
Execution Time: 16.418 ms1M pending builds
No index: query plan
Planning Time: 0.220 ms
Execution Time: 149.576 msGIN index: query plan
Planning Time: 0.407 ms
Execution Time: 19.044 ms5M pending builds
Gin index: query plan
Planning Time: 0.586 ms
Execution Time: 72.837 msEdited by Grzegorz Bizon