Remove cross-joins from Project#upstream_projects,#downstream_projects

What does this MR do and why?

1. Remove cross-joins from Project#upstream_projects,#downstream_projects. These methods were joining between ci_* and non ci_* tables which is not going to be allowed when we move ci_* tables to a new database. disable_joins was chosen here as this does not increase the cardinality of data being returned as the only usage of this (aside from counts handled in the next commit) is project.downstream_projects.each. This is fine to switch to disable_joins as there is no pagination, no limits and no filtering. You can read more at https://docs.gitlab.com/ee/development/database/multiple_databases.html#use-disable_joins-for-has_one-or-has_many-through-relations .
2. Use _subscriptions join table to count upstream/downstream projects. Prior to this change we were joining through upstream_subscriptions and downstream_subscriptions relations to count upstream_projects and downstream_projects. This uses the ci_subscriptions_projects table. Since we've added disable_joins to these has_many ... through: ... relations these queries will now be less efficient. So instead we've simplified the query to not join at all as the ci_subscriptions_projects.upstream_project_id and ci_subscriptions_projects.downstream_project_id are already both NOT NULL foreign keys which means we already know for sure that counting the ci_subscriptions_projects rows is equivalent to counting the projects rows.

Database queries

Load all query

Before

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/6687/commands/23577

SELECT "projects"."id", "projects"."name", "projects"."path", "projects"."description", "projects"."created_at", "projects"."updated_at", "projects"."creator_id", "projects"."namespace_id", "projects"."last_activity_at", "projects"."import_url", "projects"."visibility_level", "projects"."archived", "projects"."merge_requests_template", "projects"."star_count", "projects"."merge_requests_rebase_enabled", "projects"."import_type", "projects"."import_source", "projects"."avatar", "projects"."approvals_before_merge", "projects"."reset_approvals_on_push", "projects"."merge_requests_ff_only_enabled", "projects"."issues_template", "projects"."mirror", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "projects"."build_coverage_regex", "projects"."build_allow_git_fetch", "projects"."build_timeout", "projects"."mirror_trigger_builds", "projects"."public_builds", "projects"."pending_delete", "projects"."last_repository_check_failed", "projects"."last_repository_check_at", "projects"."only_allow_merge_if_pipeline_succeeds", "projects"."has_external_issue_tracker", "projects"."repository_storage", "projects"."request_access_enabled", "projects"."has_external_wiki", "projects"."repository_read_only", "projects"."lfs_enabled", "projects"."description_html", "projects"."only_allow_merge_if_all_discussions_are_resolved", "projects"."repository_size_limit", "projects"."service_desk_enabled", "projects"."printing_merge_request_link_enabled", "projects"."auto_cancel_pending_pipelines", "projects"."cached_markdown_version", "projects"."last_repository_updated_at", "projects"."ci_config_path", "projects"."disable_overriding_approvers_per_merge_request", "projects"."delete_error", "projects"."storage_version", "projects"."resolve_outdated_diff_discussions", "projects"."remote_mirror_available_overridden", "projects"."only_mirror_protected_branches", "projects"."pull_mirror_available_overridden", "projects"."jobs_cache_index", "projects"."external_authorization_classification_label", "projects"."mirror_overwrites_diverged_branches", "projects"."external_webhook_token", "projects"."pages_https_only", "projects"."packages_enabled", "projects"."merge_requests_author_approval", "projects"."pool_repository_id", "projects"."runners_token_encrypted", "projects"."bfg_object_map", "projects"."detected_repository_languages", "projects"."merge_requests_disable_committers_approval", "projects"."require_password_to_approve", "projects"."emails_disabled", "projects"."max_pages_size", "projects"."max_artifacts_size", "projects"."remove_source_branch_after_merge", "projects"."marked_for_deletion_at", "projects"."marked_for_deletion_by_user_id", "projects"."suggestion_commit_message", "projects"."autoclose_referenced_issues", "projects"."project_namespace_id" FROM "projects" INNER JOIN "ci_subscriptions_projects" ON "projects"."id" = "ci_subscriptions_projects"."downstream_project_id" WHERE "ci_subscriptions_projects"."upstream_project_id" = 278964

After

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/6687/commands/23578

SELECT "ci_subscriptions_projects"."downstream_project_id" FROM "ci_subscriptions_projects" WHERE "ci_subscriptions_projects"."upstream_project_id" = 278964

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/6687/commands/23579

SELECT "projects"."id", "projects"."name", "projects"."path", "projects"."description", "projects"."created_at", "projects"."updated_at", "projects"."creator_id", "projects"."namespace_id", "projects"."last_activity_at", "projects"."import_url", "projects"."visibility_level", "projects"."archived", "projects"."avatar", "projects"."merge_requests_template", "projects"."star_count", "projects"."merge_requests_rebase_enabled", "projects"."import_type", "projects"."import_source", "projects"."approvals_before_merge", "projects"."reset_approvals_on_push", "projects"."merge_requests_ff_only_enabled", "projects"."issues_template", "projects"."mirror", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "projects"."build_coverage_regex", "projects"."build_allow_git_fetch", "projects"."build_timeout", "projects"."mirror_trigger_builds", "projects"."pending_delete", "projects"."public_builds", "projects"."last_repository_check_failed", "projects"."last_repository_check_at", "projects"."only_allow_merge_if_pipeline_succeeds", "projects"."has_external_issue_tracker", "projects"."repository_storage", "projects"."repository_read_only", "projects"."request_access_enabled", "projects"."has_external_wiki", "projects"."ci_config_path", "projects"."lfs_enabled", "projects"."description_html", "projects"."only_allow_merge_if_all_discussions_are_resolved", "projects"."repository_size_limit", "projects"."printing_merge_request_link_enabled", "projects"."auto_cancel_pending_pipelines", "projects"."service_desk_enabled", "projects"."cached_markdown_version", "projects"."delete_error", "projects"."last_repository_updated_at", "projects"."disable_overriding_approvers_per_merge_request", "projects"."storage_version", "projects"."resolve_outdated_diff_discussions", "projects"."remote_mirror_available_overridden", "projects"."only_mirror_protected_branches", "projects"."pull_mirror_available_overridden", "projects"."jobs_cache_index", "projects"."external_authorization_classification_label", "projects"."mirror_overwrites_diverged_branches", "projects"."pages_https_only", "projects"."external_webhook_token", "projects"."packages_enabled", "projects"."merge_requests_author_approval", "projects"."pool_repository_id", "projects"."runners_token_encrypted", "projects"."bfg_object_map", "projects"."detected_repository_languages", "projects"."merge_requests_disable_committers_approval", "projects"."require_password_to_approve", "projects"."emails_disabled", "projects"."max_pages_size", "projects"."max_artifacts_size", "projects"."remove_source_branch_after_merge", "projects"."marked_for_deletion_at", "projects"."marked_for_deletion_by_user_id", "projects"."autoclose_referenced_issues", "projects"."suggestion_commit_message", "projects"."project_namespace_id" FROM "projects" WHERE "projects"."id" IN (16597098)

Count queries

Before

#upstream_projects.count:

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/6687/commands/23580

SELECT COUNT(*) FROM "projects" INNER JOIN "ci_subscriptions_projects" ON "projects"."id" = "ci_subscriptions_projects"."upstream_project_id" WHERE "ci_subscriptions_projects"."downstream_project_id" = 278964

#downstream_projects.count:

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/6687/commands/23581

SELECT COUNT(*) FROM "projects" INNER JOIN "ci_subscriptions_projects" ON "projects"."id" = "ci_subscriptions_projects"."downstream_project_id" WHERE "ci_subscriptions_projects"."upstream_project_id" = 278964

#downstream_project_subscriptions.any?:

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/6706/commands/23672

After

#upstream_projects.count:

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/6687/commands/23582

SELECT COUNT(*) FROM "ci_subscriptions_projects" WHERE "ci_subscriptions_projects"."downstream_project_id" = 278964

#downstream_projects.count:

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/6687/commands/23583

SELECT COUNT(*) FROM "ci_subscriptions_projects" WHERE "ci_subscriptions_projects"."upstream_project_id" = 278964

#downstream_projects.any?:

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/6706/commands/23676

SELECT 1 AS one FROM "projects" INNER JOIN "ci_subscriptions_projects" ON "projects"."id" = "ci_subscriptions_projects"."downstream_project_id" WHERE "ci_subscriptions_projects"."upstream_project_id" = 278964 LIMIT 1

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.

Related to #340781 (closed)