Skip to content

Remove allow database cross join from runner_matchers

Marius Bobin requested to merge 339621-ci-matchers-cross-database-query into master

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.

Edited by Marius Bobin

Merge request reports