Skip to content

Resolve "Optimize Routable.where_full_path_in by avoiding joins"

Manoj M J requested to merge 432848-optimize-routable-where_full_path_in-by-avoiding-joins into master

What does this MR do and why?

For #432848 (closed)

As we are building Cells, and tables are being tagged as either a clusterwide table or cell-local table.

routes is a clusterwide table and project/namespaces are cell-local tables.

Because these belong to different database schemas, cross-joins between them cannot happen and hence the queries should be rewritten to prevent cross-joins from happening.

This MR attempts to do that, in the where_full_path_in(paths) method in the Routable module, that is later mixed into Namespace and Project models.

Details

This method has different flavours: It can be used as

  • Project.where_full_path_in(paths) or Group.where_full_path_in(paths)
  • Project.where_full_path_in(paths, use_includes: false) or Group.where_full_path_in(paths, use_includes: false)

Note:

  • use_includes: true is the default parameter of this method. When true, the associated routes of these projects/groups are included so as to prevent N+1 queries in areas where this is used. Rails issues a LEFT OUTER JOIN routes to get this done.

  • When use_includes: false, these routes are not eager loaded. And hence, the join is simply an INNER JOIN routes

or it's chained versions:

  • Project.where(something: something).where_full_path_in(paths)/ Group.where(something: something).where_full_path_in(paths).
  • Project.where(something: something).where_full_path_in(paths, use_includes: false)/ Group.where(something: something).where_full_path_in(paths, use_includes: false).

(Note: Routable.where_full_path_in(paths) itself cannot be used, because Routable is only a concern and hence the class method where_full_path_in is only available in classes where Routable is mixed into.)

The fix in this MR follows the same pattern we used in !130842 (diffs), to fix Routable.find_by_full_path method, which has been working well for a while now.

Example of one of the changes

Essentially, we are replacing the cross-joins between projects/routes AND namespaces/routes such that they now produce

  • 2 different queries, when use_includes: false 
paths = ["Commit451", "asd"]
Group.where_full_path_in(paths, use_includes: false)
Before After
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"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM "namespaces" INNER JOIN "routes" ON "routes"."source_type" = 'Namespace' AND "routes"."source_id" = "namespaces"."id" WHERE "namespaces"."type" = 'Group' AND ((LOWER(routes.path) = LOWER('Commit451')) OR (LOWER(routes.path) = LOWER('asd'))) (1) SELECT "routes"."source_id" FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND ((LOWER(routes.path) = LOWER('Commit451')) OR (LOWER(routes.path) = LOWER('asd'))), ------------ (2) 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"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (29, 72)
  • 3 different queries, when use_includes: true 
paths = ["Commit451", "asd"]
Group.where_full_path_in(paths, use_includes: true)
Before After
SELECT "namespaces"."id" AS t0_r0, "namespaces"."name" AS t0_r1, "namespaces"."path" AS t0_r2, "namespaces"."owner_id" AS t0_r3, "namespaces"."created_at" AS t0_r4, "namespaces"."updated_at" AS t0_r5, "namespaces"."type" AS t0_r6, "namespaces"."description" AS t0_r7, "namespaces"."avatar" AS t0_r8, "namespaces"."membership_lock" AS t0_r9, "namespaces"."share_with_group_lock" AS t0_r10, "namespaces"."visibility_level" AS t0_r11, "namespaces"."request_access_enabled" AS t0_r12, "namespaces"."ldap_sync_status" AS t0_r13, "namespaces"."ldap_sync_error" AS t0_r14, "namespaces"."ldap_sync_last_update_at" AS t0_r15, "namespaces"."ldap_sync_last_successful_update_at" AS t0_r16, "namespaces"."ldap_sync_last_sync_at" AS t0_r17, "namespaces"."description_html" AS t0_r18, "namespaces"."lfs_enabled" AS t0_r19, "namespaces"."parent_id" AS t0_r20, "namespaces"."shared_runners_minutes_limit" AS t0_r21, "namespaces"."repository_size_limit" AS t0_r22, "namespaces"."require_two_factor_authentication" AS t0_r23, "namespaces"."two_factor_grace_period" AS t0_r24, "namespaces"."cached_markdown_version" AS t0_r25, "namespaces"."project_creation_level" AS t0_r26, "namespaces"."runners_token" AS t0_r27, "namespaces"."file_template_project_id" AS t0_r28, "namespaces"."saml_discovery_token" AS t0_r29, "namespaces"."runners_token_encrypted" AS t0_r30, "namespaces"."custom_project_templates_group_id" AS t0_r31, "namespaces"."auto_devops_enabled" AS t0_r32, "namespaces"."extra_shared_runners_minutes_limit" AS t0_r33, "namespaces"."last_ci_minutes_notification_at" AS t0_r34, "namespaces"."last_ci_minutes_usage_notification_level" AS t0_r35, "namespaces"."subgroup_creation_level" AS t0_r36, "namespaces"."emails_disabled" AS t0_r37, "namespaces"."max_pages_size" AS t0_r38, "namespaces"."max_artifacts_size" AS t0_r39, "namespaces"."mentions_disabled" AS t0_r40, "namespaces"."default_branch_protection" AS t0_r41, "namespaces"."max_personal_access_token_lifetime" AS t0_r42, "namespaces"."push_rule_id" AS t0_r43, "namespaces"."shared_runners_enabled" AS t0_r44, "namespaces"."allow_descendants_override_disabled_shared_runners" AS t0_r45, "namespaces"."traversal_ids" AS t0_r46, "namespaces"."organization_id" AS t0_r47, "routes"."id" AS t1_r0, "routes"."source_id" AS t1_r1, "routes"."source_type" AS t1_r2, "routes"."path" AS t1_r3, "routes"."created_at" AS t1_r4, "routes"."updated_at" AS t1_r5, "routes"."name" AS t1_r6, "routes"."namespace_id" AS t1_r7 FROM "namespaces" LEFT OUTER JOIN "routes" ON "routes"."source_type" = 'Namespace' AND "routes"."source_id" = "namespaces"."id" WHERE "namespaces"."type" = 'Group' AND ((LOWER(routes.path) = LOWER('Commit451')) OR (LOWER(routes.path) = LOWER('asd'))) (1) SELECT "routes"."source_id" FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND ((LOWER(routes.path) = LOWER('Commit451')) OR (LOWER(routes.path) = LOWER('asd'))), ------------ (2) 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"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (29, 72), ------------ (3) SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND "routes"."source_id" IN (29, 72)

Query plans

Queries before this change

We are using projects = gitlab-org/gitlab, gnutls/gnutls.

We are using groups = gitlab-org, gitmate

projects = ['gitlab-org/gitlab', 'gnutls/gnutls']
groups = ['gitlab-org', 'gitmate']

Without chaining:

  • Projects, use_includes: true 
Project.where_full_path_in(projects, use_includes: true)
SELECT "projects"."id" AS t0_r0, "projects"."name" AS t0_r1, "projects"."path" AS t0_r2, "projects"."description" AS t0_r3, "projects"."created_at" AS t0_r4, "projects"."updated_at" AS t0_r5, "projects"."creator_id" AS t0_r6, "projects"."namespace_id" AS t0_r7, "projects"."last_activity_at" AS t0_r8, "projects"."import_url" AS t0_r9, "projects"."visibility_level" AS t0_r10, "projects"."archived" AS t0_r11, "projects"."merge_requests_template" AS t0_r12, "projects"."star_count" AS t0_r13, "projects"."merge_requests_rebase_enabled" AS t0_r14, "projects"."import_type" AS t0_r15, "projects"."import_source" AS t0_r16, "projects"."avatar" AS t0_r17, "projects"."approvals_before_merge" AS t0_r18, "projects"."reset_approvals_on_push" AS t0_r19, "projects"."merge_requests_ff_only_enabled" AS t0_r20, "projects"."issues_template" AS t0_r21, "projects"."mirror" AS t0_r22, "projects"."mirror_last_update_at" AS t0_r23, "projects"."mirror_last_successful_update_at" AS t0_r24, "projects"."mirror_user_id" AS t0_r25, "projects"."shared_runners_enabled" AS t0_r26, "projects"."runners_token" AS t0_r27, "projects"."build_allow_git_fetch" AS t0_r28, "projects"."build_timeout" AS t0_r29, "projects"."mirror_trigger_builds" AS t0_r30, "projects"."public_builds" AS t0_r31, "projects"."pending_delete" AS t0_r32, "projects"."last_repository_check_failed" AS t0_r33, "projects"."last_repository_check_at" AS t0_r34, "projects"."only_allow_merge_if_pipeline_succeeds" AS t0_r35, "projects"."has_external_issue_tracker" AS t0_r36, "projects"."repository_storage" AS t0_r37, "projects"."request_access_enabled" AS t0_r38, "projects"."has_external_wiki" AS t0_r39, "projects"."repository_read_only" AS t0_r40, "projects"."lfs_enabled" AS t0_r41, "projects"."description_html" AS t0_r42, "projects"."only_allow_merge_if_all_discussions_are_resolved" AS t0_r43, "projects"."repository_size_limit" AS t0_r44, "projects"."service_desk_enabled" AS t0_r45, "projects"."printing_merge_request_link_enabled" AS t0_r46, "projects"."auto_cancel_pending_pipelines" AS t0_r47, "projects"."cached_markdown_version" AS t0_r48, "projects"."last_repository_updated_at" AS t0_r49, "projects"."ci_config_path" AS t0_r50, "projects"."disable_overriding_approvers_per_merge_request" AS t0_r51, "projects"."delete_error" AS t0_r52, "projects"."storage_version" AS t0_r53, "projects"."resolve_outdated_diff_discussions" AS t0_r54, "projects"."remote_mirror_available_overridden" AS t0_r55, "projects"."only_mirror_protected_branches" AS t0_r56, "projects"."pull_mirror_available_overridden" AS t0_r57, "projects"."jobs_cache_index" AS t0_r58, "projects"."external_authorization_classification_label" AS t0_r59, "projects"."mirror_overwrites_diverged_branches" AS t0_r60, "projects"."external_webhook_token" AS t0_r61, "projects"."pages_https_only" AS t0_r62, "projects"."packages_enabled" AS t0_r63, "projects"."merge_requests_author_approval" AS t0_r64, "projects"."pool_repository_id" AS t0_r65, "projects"."runners_token_encrypted" AS t0_r66, "projects"."bfg_object_map" AS t0_r67, "projects"."detected_repository_languages" AS t0_r68, "projects"."merge_requests_disable_committers_approval" AS t0_r69, "projects"."require_password_to_approve" AS t0_r71, "projects"."max_pages_size" AS t0_r72, "projects"."max_artifacts_size" AS t0_r73, "projects"."pull_mirror_branch_prefix" AS t0_r74, "projects"."remove_source_branch_after_merge" AS t0_r75, "projects"."marked_for_deletion_at" AS t0_r76, "projects"."marked_for_deletion_by_user_id" AS t0_r77, "projects"."suggestion_commit_message" AS t0_r78, "projects"."autoclose_referenced_issues" AS t0_r79, "projects"."project_namespace_id" AS t0_r80, "projects"."hidden" AS t0_r81, "projects"."organization_id" AS t0_r82, "routes"."id" AS t1_r0, "routes"."source_id" AS t1_r1, "routes"."source_type" AS t1_r2, "routes"."path" AS t1_r3, "routes"."created_at" AS t1_r4, "routes"."updated_at" AS t1_r5, "routes"."name" AS t1_r6, "routes"."namespace_id" AS t1_r7 FROM "projects" LEFT OUTER JOIN "routes" ON "routes"."source_type" = 'Project' AND "routes"."source_id" = "projects"."id" WHERE ((LOWER(routes.path) = LOWER('gitlab-org/gitlab')) OR (LOWER(routes.path) = LOWER('gnutls/gnutls')))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77922

  • Projects, use_includes: false 
Project.where_full_path_in(projects, use_includes: false)
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_last_update_at", "projects"."mirror_last_successful_update_at", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "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"."max_pages_size", "projects"."max_artifacts_size", "projects"."pull_mirror_branch_prefix", "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", "projects"."hidden", "projects"."organization_id" FROM "projects" INNER JOIN "routes" ON "routes"."source_type" = 'Project' AND "routes"."source_id" = "projects"."id" WHERE ((LOWER(routes.path) = LOWER('gitlab-org/gitlab')) OR (LOWER(routes.path) = LOWER('gnutls/gnutls')))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77923

  • Groups, use_includes: true 
Group.where_full_path_in(groups, use_includes: true)
SELECT "namespaces"."id" AS t0_r0, "namespaces"."name" AS t0_r1, "namespaces"."path" AS t0_r2, "namespaces"."owner_id" AS t0_r3, "namespaces"."created_at" AS t0_r4, "namespaces"."updated_at" AS t0_r5, "namespaces"."type" AS t0_r6, "namespaces"."description" AS t0_r7, "namespaces"."avatar" AS t0_r8, "namespaces"."membership_lock" AS t0_r9, "namespaces"."share_with_group_lock" AS t0_r10, "namespaces"."visibility_level" AS t0_r11, "namespaces"."request_access_enabled" AS t0_r12, "namespaces"."ldap_sync_status" AS t0_r13, "namespaces"."ldap_sync_error" AS t0_r14, "namespaces"."ldap_sync_last_update_at" AS t0_r15, "namespaces"."ldap_sync_last_successful_update_at" AS t0_r16, "namespaces"."ldap_sync_last_sync_at" AS t0_r17, "namespaces"."description_html" AS t0_r18, "namespaces"."lfs_enabled" AS t0_r19, "namespaces"."parent_id" AS t0_r20, "namespaces"."shared_runners_minutes_limit" AS t0_r21, "namespaces"."repository_size_limit" AS t0_r22, "namespaces"."require_two_factor_authentication" AS t0_r23, "namespaces"."two_factor_grace_period" AS t0_r24, "namespaces"."cached_markdown_version" AS t0_r25, "namespaces"."project_creation_level" AS t0_r26, "namespaces"."runners_token" AS t0_r27, "namespaces"."file_template_project_id" AS t0_r28, "namespaces"."saml_discovery_token" AS t0_r29, "namespaces"."runners_token_encrypted" AS t0_r30, "namespaces"."custom_project_templates_group_id" AS t0_r31, "namespaces"."auto_devops_enabled" AS t0_r32, "namespaces"."extra_shared_runners_minutes_limit" AS t0_r33, "namespaces"."last_ci_minutes_notification_at" AS t0_r34, "namespaces"."last_ci_minutes_usage_notification_level" AS t0_r35, "namespaces"."subgroup_creation_level" AS t0_r36, "namespaces"."emails_disabled" AS t0_r37, "namespaces"."max_pages_size" AS t0_r38, "namespaces"."max_artifacts_size" AS t0_r39, "namespaces"."mentions_disabled" AS t0_r40, "namespaces"."default_branch_protection" AS t0_r41, "namespaces"."max_personal_access_token_lifetime" AS t0_r42, "namespaces"."push_rule_id" AS t0_r43, "namespaces"."shared_runners_enabled" AS t0_r44, "namespaces"."allow_descendants_override_disabled_shared_runners" AS t0_r45, "namespaces"."traversal_ids" AS t0_r46, "namespaces"."organization_id" AS t0_r47, "routes"."id" AS t1_r0, "routes"."source_id" AS t1_r1, "routes"."source_type" AS t1_r2, "routes"."path" AS t1_r3, "routes"."created_at" AS t1_r4, "routes"."updated_at" AS t1_r5, "routes"."name" AS t1_r6, "routes"."namespace_id" AS t1_r7 FROM "namespaces" LEFT OUTER JOIN "routes" ON "routes"."source_type" = 'Namespace' AND "routes"."source_id" = "namespaces"."id" WHERE "namespaces"."type" = 'Group' AND ((LOWER(routes.path) = LOWER('gitlab-org')) OR (LOWER(routes.path) = LOWER('gitmate')))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77924

  • Groups, use_includes: false 
Group.where_full_path_in(groups, use_includes: false)
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"."lfs_enabled", "namespaces"."description_html", "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"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM "namespaces" INNER JOIN "routes" ON "routes"."source_type" = 'Namespace' AND "routes"."source_id" = "namespaces"."id" WHERE "namespaces"."type" = 'Group' AND ((LOWER(routes.path) = LOWER('gitlab-org')) OR (LOWER(routes.path) = LOWER('gitmate')))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77928

With Chaining

  • Projects, chained, use_includes: true 
Project.where(id: [1,7,8,9, 278964]).where_full_path_in(projects, use_includes: true)
SELECT "projects"."id" AS t0_r0, "projects"."name" AS t0_r1, "projects"."path" AS t0_r2, "projects"."description" AS t0_r3, "projects"."created_at" AS t0_r4, "projects"."updated_at" AS t0_r5, "projects"."creator_id" AS t0_r6, "projects"."namespace_id" AS t0_r7, "projects"."last_activity_at" AS t0_r8, "projects"."import_url" AS t0_r9, "projects"."visibility_level" AS t0_r10, "projects"."archived" AS t0_r11, "projects"."merge_requests_template" AS t0_r12, "projects"."star_count" AS t0_r13, "projects"."merge_requests_rebase_enabled" AS t0_r14, "projects"."import_type" AS t0_r15, "projects"."import_source" AS t0_r16, "projects"."avatar" AS t0_r17, "projects"."approvals_before_merge" AS t0_r18, "projects"."reset_approvals_on_push" AS t0_r19, "projects"."merge_requests_ff_only_enabled" AS t0_r20, "projects"."issues_template" AS t0_r21, "projects"."mirror" AS t0_r22, "projects"."mirror_last_update_at" AS t0_r23, "projects"."mirror_last_successful_update_at" AS t0_r24, "projects"."mirror_user_id" AS t0_r25, "projects"."shared_runners_enabled" AS t0_r26, "projects"."runners_token" AS t0_r27, "projects"."build_allow_git_fetch" AS t0_r28, "projects"."build_timeout" AS t0_r29, "projects"."mirror_trigger_builds" AS t0_r30, "projects"."public_builds" AS t0_r31, "projects"."pending_delete" AS t0_r32, "projects"."last_repository_check_failed" AS t0_r33, "projects"."last_repository_check_at" AS t0_r34, "projects"."only_allow_merge_if_pipeline_succeeds" AS t0_r35, "projects"."has_external_issue_tracker" AS t0_r36, "projects"."repository_storage" AS t0_r37, "projects"."request_access_enabled" AS t0_r38, "projects"."has_external_wiki" AS t0_r39, "projects"."repository_read_only" AS t0_r40, "projects"."lfs_enabled" AS t0_r41, "projects"."description_html" AS t0_r42, "projects"."only_allow_merge_if_all_discussions_are_resolved" AS t0_r43, "projects"."repository_size_limit" AS t0_r44, "projects"."service_desk_enabled" AS t0_r45, "projects"."printing_merge_request_link_enabled" AS t0_r46, "projects"."auto_cancel_pending_pipelines" AS t0_r47, "projects"."cached_markdown_version" AS t0_r48, "projects"."last_repository_updated_at" AS t0_r49, "projects"."ci_config_path" AS t0_r50, "projects"."disable_overriding_approvers_per_merge_request" AS t0_r51, "projects"."delete_error" AS t0_r52, "projects"."storage_version" AS t0_r53, "projects"."resolve_outdated_diff_discussions" AS t0_r54, "projects"."remote_mirror_available_overridden" AS t0_r55, "projects"."only_mirror_protected_branches" AS t0_r56, "projects"."pull_mirror_available_overridden" AS t0_r57, "projects"."jobs_cache_index" AS t0_r58, "projects"."external_authorization_classification_label" AS t0_r59, "projects"."mirror_overwrites_diverged_branches" AS t0_r60, "projects"."external_webhook_token" AS t0_r61, "projects"."pages_https_only" AS t0_r62, "projects"."packages_enabled" AS t0_r63, "projects"."merge_requests_author_approval" AS t0_r64, "projects"."pool_repository_id" AS t0_r65, "projects"."runners_token_encrypted" AS t0_r66, "projects"."bfg_object_map" AS t0_r67, "projects"."detected_repository_languages" AS t0_r68, "projects"."merge_requests_disable_committers_approval" AS t0_r69, "projects"."require_password_to_approve" AS t0_r71, "projects"."max_pages_size" AS t0_r72, "projects"."max_artifacts_size" AS t0_r73, "projects"."pull_mirror_branch_prefix" AS t0_r74, "projects"."remove_source_branch_after_merge" AS t0_r75, "projects"."marked_for_deletion_at" AS t0_r76, "projects"."marked_for_deletion_by_user_id" AS t0_r77, "projects"."suggestion_commit_message" AS t0_r78, "projects"."autoclose_referenced_issues" AS t0_r79, "projects"."project_namespace_id" AS t0_r80, "projects"."hidden" AS t0_r81, "projects"."organization_id" AS t0_r82, "routes"."id" AS t1_r0, "routes"."source_id" AS t1_r1, "routes"."source_type" AS t1_r2, "routes"."path" AS t1_r3, "routes"."created_at" AS t1_r4, "routes"."updated_at" AS t1_r5, "routes"."name" AS t1_r6, "routes"."namespace_id" AS t1_r7 FROM "projects" LEFT OUTER JOIN "routes" ON "routes"."source_type" = 'Project' AND "routes"."source_id" = "projects"."id" WHERE "projects"."id" IN (1, 7, 8, 9, 278964) AND ((LOWER(routes.path) = LOWER('gitlab-org/gitlab')) OR (LOWER(routes.path) = LOWER('gnutls/gnutls')))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77929

  • Projects, chained, use_includes: false 
Project.where(id: [1,7,8,9, 278964]).where_full_path_in(projects, use_includes: false)
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_last_update_at", "projects"."mirror_last_successful_update_at", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "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"."max_pages_size", "projects"."max_artifacts_size", "projects"."pull_mirror_branch_prefix", "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", "projects"."hidden", "projects"."organization_id" FROM "projects" INNER JOIN "routes" ON "routes"."source_type" = 'Project' AND "routes"."source_id" = "projects"."id" WHERE "projects"."id" IN (1, 7, 8, 9, 278964) AND ((LOWER(routes.path) = LOWER('gitlab-org/gitlab')) OR (LOWER(routes.path) = LOWER('gnutls/gnutls')))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77930

  • Groups, chained, use_includes: true 
Group.where(id: [1,7,8,9, 9970]).where_full_path_in(groups, use_includes: true)
SELECT "namespaces"."id" AS t0_r0, "namespaces"."name" AS t0_r1, "namespaces"."path" AS t0_r2, "namespaces"."owner_id" AS t0_r3, "namespaces"."created_at" AS t0_r4, "namespaces"."updated_at" AS t0_r5, "namespaces"."type" AS t0_r6, "namespaces"."description" AS t0_r7, "namespaces"."avatar" AS t0_r8, "namespaces"."membership_lock" AS t0_r9, "namespaces"."share_with_group_lock" AS t0_r10, "namespaces"."visibility_level" AS t0_r11, "namespaces"."request_access_enabled" AS t0_r12, "namespaces"."ldap_sync_status" AS t0_r13, "namespaces"."ldap_sync_error" AS t0_r14, "namespaces"."ldap_sync_last_update_at" AS t0_r15, "namespaces"."ldap_sync_last_successful_update_at" AS t0_r16, "namespaces"."ldap_sync_last_sync_at" AS t0_r17, "namespaces"."lfs_enabled" AS t0_r18, "namespaces"."description_html" AS t0_r19, "namespaces"."parent_id" AS t0_r20, "namespaces"."shared_runners_minutes_limit" AS t0_r21, "namespaces"."repository_size_limit" AS t0_r22, "namespaces"."require_two_factor_authentication" AS t0_r23, "namespaces"."two_factor_grace_period" AS t0_r24, "namespaces"."cached_markdown_version" AS t0_r25, "namespaces"."project_creation_level" AS t0_r26, "namespaces"."runners_token" AS t0_r27, "namespaces"."file_template_project_id" AS t0_r28, "namespaces"."saml_discovery_token" AS t0_r29, "namespaces"."runners_token_encrypted" AS t0_r30, "namespaces"."custom_project_templates_group_id" AS t0_r31, "namespaces"."auto_devops_enabled" AS t0_r32, "namespaces"."extra_shared_runners_minutes_limit" AS t0_r33, "namespaces"."last_ci_minutes_notification_at" AS t0_r34, "namespaces"."last_ci_minutes_usage_notification_level" AS t0_r35, "namespaces"."subgroup_creation_level" AS t0_r36, "namespaces"."emails_disabled" AS t0_r37, "namespaces"."max_pages_size" AS t0_r38, "namespaces"."max_artifacts_size" AS t0_r39, "namespaces"."mentions_disabled" AS t0_r42, "namespaces"."default_branch_protection" AS t0_r43, "namespaces"."max_personal_access_token_lifetime" AS t0_r44, "namespaces"."push_rule_id" AS t0_r45, "namespaces"."shared_runners_enabled" AS t0_r46, "namespaces"."allow_descendants_override_disabled_shared_runners" AS t0_r47, "namespaces"."traversal_ids" AS t0_r48, "namespaces"."organization_id" AS t0_r49, "routes"."id" AS t1_r0, "routes"."source_id" AS t1_r1, "routes"."source_type" AS t1_r2, "routes"."path" AS t1_r3, "routes"."created_at" AS t1_r4, "routes"."updated_at" AS t1_r5, "routes"."name" AS t1_r6, "routes"."namespace_id" AS t1_r7 FROM "namespaces" LEFT OUTER JOIN "routes" ON "routes"."source_type" = 'Namespace' AND "routes"."source_id" = "namespaces"."id" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (1, 7, 8, 9, 9970) AND ((LOWER(routes.path) = LOWER('gitlab-org')) OR (LOWER(routes.path) = LOWER('gitmate')))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77931

  • Groups, chained, use_includes: false 
Group.where(id: [1,7,8,9, 9970]).where_full_path_in(groups, use_includes: false)
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"."lfs_enabled", "namespaces"."description_html", "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"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM "namespaces" INNER JOIN "routes" ON "routes"."source_type" = 'Namespace' AND "routes"."source_id" = "namespaces"."id" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (1, 7, 8, 9, 9970) AND ((LOWER(routes.path) = LOWER('gitlab-org')) OR (LOWER(routes.path) = LOWER('gitmate')))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77933

Queries after this change

We are using projects = gitlab-org/gitlab, gnutls/gnutls.

We are using groups = gitlab-org, gitmate

projects = ['gitlab-org/gitlab', 'gnutls/gnutls']
groups = ['gitlab-org', 'gitmate']

Without chaining:

  • Projects, use_includes: true 
Project.where_full_path_in(projects, use_includes: true)
SELECT "routes"."source_id" FROM "routes" WHERE "routes"."source_type" = 'Project' AND ((LOWER(routes.path) = LOWER('gitlab-org/gitlab')) OR (LOWER(routes.path) = LOWER('gnutls/gnutls')))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77934

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_last_update_at", "projects"."mirror_last_successful_update_at", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "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"."max_pages_size", "projects"."max_artifacts_size", "projects"."pull_mirror_branch_prefix", "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", "projects"."hidden", "projects"."organization_id" FROM "projects" WHERE "projects"."id" IN (179611, 278964)

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77935

SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" IN (179611, 278964)

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77936

  • Projects, use_includes: false
Project.where_full_path_in(projects, use_includes: false)

2 queries, same as above minus the query to preload routes:

SELECT "routes"."source_id" FROM "routes" WHERE "routes"."source_type" = 'Project' AND ((LOWER(routes.path) = LOWER('gitlab-org/gitlab')) OR (LOWER(routes.path) = LOWER('gnutls/gnutls')))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77934

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_last_update_at", "projects"."mirror_last_successful_update_at", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "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"."max_pages_size", "projects"."max_artifacts_size", "projects"."pull_mirror_branch_prefix", "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", "projects"."hidden", "projects"."organization_id" FROM "projects" WHERE "projects"."id" IN (179611, 278964)

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77935

  • Groups, use_includes: true 
Group.where_full_path_in(groups, use_includes: true)
SELECT "routes"."source_id" FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND ((LOWER(routes.path) = LOWER('gitlab-org')) OR (LOWER(routes.path) = LOWER('gitmate')))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77937

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"."lfs_enabled", "namespaces"."description_html", "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"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (9970, 287496)

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77938

SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND "routes"."source_id" IN (9970, 287496)

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77939

  • Groups, use_includes: false 
Group.where_full_path_in(groups, use_includes: false)

2 queries, same as above minus the query to preload routes:

SELECT "routes"."source_id" FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND ((LOWER(routes.path) = LOWER('gitlab-org')) OR (LOWER(routes.path) = LOWER('gitmate')))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77937

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"."lfs_enabled", "namespaces"."description_html", "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"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (9970, 287496)

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77938

With chaining

  • Projects, chained, use_includes: true 
Project.where(id: [1,7,8,9, 278964]).where_full_path_in(projects, use_includes: true)
SELECT "routes"."source_id" FROM "routes" WHERE "routes"."source_type" = 'Project' AND ((LOWER(routes.path) = LOWER('gitlab-org/gitlab')) OR (LOWER(routes.path) = LOWER('gnutls/gnutls')))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77940

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_last_update_at", "projects"."mirror_last_successful_update_at", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "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"."max_pages_size", "projects"."max_artifacts_size", "projects"."pull_mirror_branch_prefix", "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", "projects"."hidden", "projects"."organization_id" FROM "projects" WHERE "projects"."id" IN (1, 7, 8, 9, 278964) AND "projects"."id" IN (179611, 278964)

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77941

SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" = 278964

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77942

  • Projects, chained, use_includes: false 
Project.where(id: [1,7,8,9, 278964]).where_full_path_in(projects, use_includes: false)

2 queries, same as above minus the query to preload routes:

SELECT "routes"."source_id" FROM "routes" WHERE "routes"."source_type" = 'Project' AND ((LOWER(routes.path) = LOWER('gitlab-org/gitlab')) OR (LOWER(routes.path) = LOWER('gnutls/gnutls')))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77940

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_last_update_at", "projects"."mirror_last_successful_update_at", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "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"."max_pages_size", "projects"."max_artifacts_size", "projects"."pull_mirror_branch_prefix", "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", "projects"."hidden", "projects"."organization_id" FROM "projects" WHERE "projects"."id" IN (1, 7, 8, 9, 278964) AND "projects"."id" IN (179611, 278964)

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77941

  • Groups, chained, use_includes: true 
Group.where(id: [1,7,8,9, 9970]).where_full_path_in(groups, use_includes: true)
SELECT "routes"."source_id" FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND ((LOWER(routes.path) = LOWER('gitlab-org')) OR (LOWER(routes.path) = LOWER('gitmate')))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77943

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"."lfs_enabled", "namespaces"."description_html", "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"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (1, 7, 8, 9, 9970) AND "namespaces"."id" IN (9970, 287496)

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77944

SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND "routes"."source_id" = 9970

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77945

  • Groups, chained, use_includes: false 
Group.where(id: [1,7,8,9, 9970]).where_full_path_in(groups, use_includes: false)

2 queries, same as above minus the query to preload routes:

SELECT "routes"."source_id" FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND ((LOWER(routes.path) = LOWER('gitlab-org')) OR (LOWER(routes.path) = LOWER('gitmate')))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77943

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"."lfs_enabled", "namespaces"."description_html", "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"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (1, 7, 8, 9, 9970) AND "namespaces"."id" IN (9970, 287496)

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24398/commands/77944

Note:

  • Change has been added behind a feature flag optimize_where_full_path_in , hence there is no changelog.
  • In a follow-up MR, I intend to change the name of the paramater use_includes to preload_routes: !137886 (comment 1675442036)

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before After

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.

Related to #432848 (closed)

Edited by Manoj M J

Merge request reports