Remove cross-joins from Project#upstream_projects,#downstream_projects
What does this MR do and why?
- Remove cross-joins from
Project#upstream_projects
,#downstream_projects
. These methods were joining betweenci_*
and nonci_*
tables which is not going to be allowed when we moveci_*
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) isproject.downstream_projects.each
. This is fine to switch todisable_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 . - Use
_subscriptions
join table to count upstream/downstream projects. Prior to this change we were joining throughupstream_subscriptions
anddownstream_subscriptions
relations to countupstream_projects
anddownstream_projects
. This uses theci_subscriptions_projects
table. Since we've addeddisable_joins
to thesehas_many ... through: ...
relations these queries will now be less efficient. So instead we've simplified the query to not join at all as theci_subscriptions_projects.upstream_project_id
andci_subscriptions_projects.downstream_project_id
are already bothNOT NULL
foreign keys which means we already know for sure that counting theci_subscriptions_projects
rows is equivalent to counting theprojects
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)