Eliminate cross-db-join in RunnersFinder

What does this MR do and why?

This MR eliminates cross-db-joins in Ci::RunnersFinder. It starts using the ci_namespace_mirrors and ci_project_mirrors tables.

• Based on: !75517 (merged)
• Related to #336432 (closed) & #336433 (closed)
• The changes are behind a feature flag ci_find_runners_by_ci_mirrors #347226 (closed).

Database

Benchmark preparation in postgres.ai:

INSERT INTO ci_project_mirrors (project_id, namespace_id)
SELECT "projects"."id", "projects"."namespace_id" FROM "projects"
ON CONFLICT (project_id) DO NOTHING;

INSERT INTO ci_namespace_mirrors (namespace_id, traversal_ids)
SELECT "namespaces"."id", "namespaces"."traversal_ids" FROM "namespaces"
ON CONFLICT (namespace_id) DO NOTHING;

ANALYZE ci_project_mirrors;
ANALYZE ci_namespace_mirrors;

SQL query 1

Ci::Runner.belonging_to_group_or_project(@group.id)

SELECT "ci_runners".* FROM (
  (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
     (SELECT "ci_namespace_mirrors"."namespace_id"
        FROM "ci_namespace_mirrors"
       WHERE (traversal_ids @> ARRAY['1234']::int[])))
  UNION
  (SELECT "ci_runners".*
   FROM "ci_runners"
   INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
   WHERE "ci_runner_projects"."project_id" IN
     (SELECT "ci_project_mirrors"."project_id"
      FROM "ci_project_mirrors"
      WHERE "ci_project_mirrors"."namespace_id" IN
        (SELECT "ci_namespace_mirrors"."namespace_id"
           FROM "ci_namespace_mirrors"
          WHERE (traversal_ids @> ARRAY['1234']::int[]))))
  )
ci_runners;

EXPLAIN ANALYZE to groups that have many subgroups and projects:

1. Group 1:
   • cold: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7477/commands/26538
   • hot: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7477/commands/26539
2. Group 2:
   • cold: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7477/commands/26540
   • hot: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7477/commands/26541

SQL query 2

Ci::Runner.belonging_to_group(@group.id)

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" = 1234

EXPLAIN ANALYZE to groups that have many subgroups and projects:

1. Group 1:
   • cold: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7616/commands/27038
   • hot: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7616/commands/27039
2. Group 2:
   • cold: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7616/commands/27040
   • hot: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7616/commands/27041

---

Previous Query

descendant_projects = Project.for_group_and_its_subgroups(@group)
Ci::Runner.legacy_belonging_to_group_or_project(@group.self_and_descendants, descendant_projects)

SQL

SELECT "ci_runners".*
FROM (
  (
    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 (
      SELECT "namespaces"."id"
      FROM "namespaces"
      WHERE "namespaces"."type" = 'Group'
        AND "namespaces"."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"
              WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 327
            )
            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"
        )
    )
  )
  UNION
  (
    SELECT "ci_runners".*
    FROM "ci_runners"
    INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
    WHERE "ci_runner_projects"."project_id" IN (
      SELECT "projects"."id"
      FROM "projects"
      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"
            WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 327
          )
          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 "id" FROM "base_and_descendants" AS "namespaces"
      )
    )
  )
) ci_runners;

EXPLAIN ANALYZE to groups that have many subgroups and projects:

1. Group 1:
   • cold: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7819/commands/27877
   • hot: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7819/commands/27878
2. Group 2:
   • cold: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7819/commands/27879
   • hot: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7819/commands/27880

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.