Remove allow database cross join from runner_matchers
What does this MR do and why?
Related to #339621 (closed)
!76454 (merged) removed the join between namespaces
and runners
, so the allow wrap it's not needed.
Even if this MR doesn't change any of the SQL that's being generated, I've added the SQL queries for documentation purpose:
Old query
SELECT
array_agg(ci_runners.id),
"ci_runners"."runner_type",
"ci_runners"."public_projects_minutes_cost_factor",
"ci_runners"."private_projects_minutes_cost_factor",
"ci_runners"."run_untagged",
"ci_runners"."access_level",
(
SELECT
COALESCE(array_agg(tags.name ORDER BY name), ARRAY[]::text[])
FROM
"taggings"
INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id"
WHERE (taggings.taggable_id = "ci_runners".id)
AND "taggings"."context" = 'tags'
AND "taggings"."taggable_type" = 'Ci::Runner')
FROM ((
SELECT
"ci_runners".*
FROM
"ci_runners"
INNER JOIN "ci_runner_projects" ON "ci_runners"."id" = "ci_runner_projects"."runner_id"
WHERE
"ci_runner_projects"."project_id" = 41)
UNION (
SELECT
"ci_runners".*
FROM
"ci_runners"
INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
INNER JOIN "namespaces" ON "namespaces"."id" = "ci_runner_namespaces"."namespace_id"
AND "namespaces"."type" = 'Group'
WHERE
"namespaces"."id" IN ( WITH RECURSIVE "base_and_ancestors" 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 "projects" ON "projects"."namespace_id" = "namespaces"."id"
WHERE
"namespaces"."type" = 'Group'
AND "projects"."id" = 41)
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_ancestors"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = "base_and_ancestors"."parent_id"))
SELECT
id
FROM
"base_and_ancestors" AS "namespaces"))
UNION (
SELECT
"ci_runners".*
FROM
"ci_runners"
WHERE
"ci_runners"."runner_type" = 1)) ci_runners
WHERE
"ci_runners"."active" = TRUE
AND (contacted_at > '2022-01-12 14:08:14.311154')
GROUP BY
"ci_runners"."runner_type",
"ci_runners"."public_projects_minutes_cost_factor",
"ci_runners"."private_projects_minutes_cost_factor",
"ci_runners"."run_untagged",
"ci_runners"."access_level",
(
SELECT
COALESCE(array_agg(tags.name ORDER BY name), ARRAY[]::text[])
FROM
"taggings"
INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id"
WHERE (taggings.taggable_id = "ci_runners".id)
AND "taggings"."context" = 'tags'
AND "taggings"."taggable_type" = 'Ci::Runner')
Execution plans for gitlab-org/gitlab
: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/7968/commands/28500
New query
SELECT
array_agg(ci_runners.id),
"ci_runners"."runner_type",
"ci_runners"."public_projects_minutes_cost_factor",
"ci_runners"."private_projects_minutes_cost_factor",
"ci_runners"."run_untagged",
"ci_runners"."access_level",
(
SELECT
COALESCE(array_agg(tags.name ORDER BY name), ARRAY[]::text[])
FROM
"taggings"
INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id"
WHERE (taggings.taggable_id = "ci_runners".id)
AND "taggings"."context" = 'tags'
AND "taggings"."taggable_type" = 'Ci::Runner')
FROM ((
SELECT
"ci_runners".*
FROM
"ci_runners"
INNER JOIN "ci_runner_projects" ON "ci_runners"."id" = "ci_runner_projects"."runner_id"
WHERE
"ci_runner_projects"."project_id" = 41)
UNION (
SELECT
"ci_runners".*
FROM
"ci_runners"
INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
WHERE
"ci_runner_namespaces"."namespace_id" IN (49, 48, 47, 46, 45, 22))
UNION (
SELECT
"ci_runners".*
FROM
"ci_runners"
WHERE
"ci_runners"."runner_type" = 1)) ci_runners
WHERE
"ci_runners"."active" = TRUE
AND (contacted_at > '2022-01-12 13:06:04.395220')
GROUP BY
"ci_runners"."runner_type",
"ci_runners"."public_projects_minutes_cost_factor",
"ci_runners"."private_projects_minutes_cost_factor",
"ci_runners"."run_untagged",
"ci_runners"."access_level",
(
SELECT
COALESCE(array_agg(tags.name ORDER BY name), ARRAY[]::text[])
FROM
"taggings"
INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id"
WHERE (taggings.taggable_id = "ci_runners".id)
AND "taggings"."context" = 'tags'
AND "taggings"."taggable_type" = 'Ci::Runner')
Execution plans for gitlab-org/gitlab
: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/7968/commands/28501
Screenshots or screen recordings
These are strongly recommended to assist reviewers and reduce the time to merge your change.
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
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.