Update catalog listing queries to use new visibility_level column

What does this MR do and why?

We have now fully denormalized the visibility_level column in the table catalog_resources. We implemented the syncing process in !137238 (merged) and !138865 (merged). And in !139165 (merged), we completed the data backfill.

Now we can update the queries in Ci::Catalog::Listing so that they use catalog_resources.visibility_level instead of projects.visibility_level. This allows us to avoid JOIN-ing with the projects table and improve query performance.

Resolves the last step of #429056 (closed).

Query Plans

1. all scope query

Ci::Catalog::Listing.new(current_user).resources(scope: :all)

Before:

SELECT "catalog_resources"."id" AS t0_r0, "catalog_resources"."project_id" AS t0_r1, "catalog_resources"."created_at" AS t0_r2, "catalog_resources"."state" AS t0_r3, "catalog_resources"."latest_released_at" AS t0_r4, "catalog_resources"."name" AS t0_r5, "catalog_resources"."description" AS t0_r6, "catalog_resources"."visibility_level" AS t0_r7, "catalog_resources"."search_vector" AS t0_r8, "projects"."id" AS t1_r0, "projects"."name" AS t1_r1, "projects"."path" AS t1_r2, "projects"."description" AS t1_r3, "projects"."created_at" AS t1_r4, "projects"."updated_at" AS t1_r5, "projects"."creator_id" AS t1_r6, "projects"."namespace_id" AS t1_r7, "projects"."last_activity_at" AS t1_r8, "projects"."import_url" AS t1_r9, "projects"."visibility_level" AS t1_r10, "projects"."archived" AS t1_r11, "projects"."avatar" AS t1_r12, "projects"."merge_requests_template" AS t1_r13, "projects"."star_count" AS t1_r14, "projects"."merge_requests_rebase_enabled" AS t1_r15, "projects"."import_type" AS t1_r16, "projects"."import_source" AS t1_r17, "projects"."approvals_before_merge" AS t1_r18, "projects"."reset_approvals_on_push" AS t1_r19, "projects"."merge_requests_ff_only_enabled" AS t1_r20, "projects"."issues_template" AS t1_r21, "projects"."mirror" AS t1_r22, "projects"."mirror_last_update_at" AS t1_r23, "projects"."mirror_last_successful_update_at" AS t1_r24, "projects"."mirror_user_id" AS t1_r25, "projects"."shared_runners_enabled" AS t1_r26, "projects"."runners_token" AS t1_r27, "projects"."build_allow_git_fetch" AS t1_r28, "projects"."build_timeout" AS t1_r29, "projects"."mirror_trigger_builds" AS t1_r30, "projects"."pending_delete" AS t1_r31, "projects"."public_builds" AS t1_r32, "projects"."last_repository_check_failed" AS t1_r33, "projects"."last_repository_check_at" AS t1_r34, "projects"."only_allow_merge_if_pipeline_succeeds" AS t1_r35, "projects"."has_external_issue_tracker" AS t1_r36, "projects"."repository_storage" AS t1_r37, "projects"."repository_read_only" AS t1_r38, "projects"."request_access_enabled" AS t1_r39, "projects"."has_external_wiki" AS t1_r40, "projects"."ci_config_path" AS t1_r41, "projects"."lfs_enabled" AS t1_r42, "projects"."description_html" AS t1_r43, "projects"."only_allow_merge_if_all_discussions_are_resolved" AS t1_r44, "projects"."repository_size_limit" AS t1_r45, "projects"."printing_merge_request_link_enabled" AS t1_r46, "projects"."auto_cancel_pending_pipelines" AS t1_r47, "projects"."service_desk_enabled" AS t1_r48, "projects"."cached_markdown_version" AS t1_r49, "projects"."delete_error" AS t1_r50, "projects"."last_repository_updated_at" AS t1_r51, "projects"."disable_overriding_approvers_per_merge_request" AS t1_r52, "projects"."storage_version" AS t1_r53, "projects"."resolve_outdated_diff_discussions" AS t1_r54, "projects"."remote_mirror_available_overridden" AS t1_r55, "projects"."only_mirror_protected_branches" AS t1_r56, "projects"."pull_mirror_available_overridden" AS t1_r57, "projects"."jobs_cache_index" AS t1_r58, "projects"."external_authorization_classification_label" AS t1_r59, "projects"."mirror_overwrites_diverged_branches" AS t1_r60, "projects"."pages_https_only" AS t1_r61, "projects"."external_webhook_token" AS t1_r62, "projects"."packages_enabled" AS t1_r63, "projects"."merge_requests_author_approval" AS t1_r64, "projects"."pool_repository_id" AS t1_r65, "projects"."runners_token_encrypted" AS t1_r66, "projects"."bfg_object_map" AS t1_r67, "projects"."detected_repository_languages" AS t1_r68, "projects"."merge_requests_disable_committers_approval" AS t1_r69, "projects"."require_password_to_approve" AS t1_r70, "projects"."max_pages_size" AS t1_r71, "projects"."max_artifacts_size" AS t1_r72, "projects"."pull_mirror_branch_prefix" AS t1_r73, "projects"."remove_source_branch_after_merge" AS t1_r74, "projects"."marked_for_deletion_at" AS t1_r75, "projects"."marked_for_deletion_by_user_id" AS t1_r76, "projects"."autoclose_referenced_issues" AS t1_r77, "projects"."suggestion_commit_message" AS t1_r78, "projects"."project_namespace_id" AS t1_r79, "projects"."hidden" AS t1_r80, "projects"."organization_id" AS t1_r81
FROM "catalog_resources"
INNER JOIN "projects" ON "projects"."id" = "catalog_resources"."project_id"
WHERE "catalog_resources"."state" = 1
AND (
  EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 12867582 AND (project_authorizations.project_id = projects.id))
  OR projects.visibility_level IN (10,20)
)
ORDER BY "catalog_resources"."created_at" DESC

Query plan link: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24636/commands/78400

After:

• This query no longer INNER JOINs with projects and is now executed in two queries to preload the projects.

SELECT "catalog_resources".*
FROM "catalog_resources"
WHERE "catalog_resources"."state" = 1
AND (
  EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 12867582 AND (project_authorizations.project_id = catalog_resources.project_id))
  OR catalog_resources.visibility_level IN (10,20)
)
ORDER BY "catalog_resources"."created_at" DESC

Query plan link: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24636/commands/78401

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_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"."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"."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"."autoclose_referenced_issues", "projects"."suggestion_commit_message", "projects"."project_namespace_id", "projects"."hidden", "projects"."organization_id"
FROM "projects"
WHERE "projects"."id" IN (52629264, 20783498, 52120058, 46810591, 45661242)

Query plan link: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24636/commands/78402

2. namespaces scope query

Ci::Catalog::Listing.new(current_user).resources(scope: :namespaces)

Before:

SELECT "catalog_resources"."id" AS t0_r0, "catalog_resources"."project_id" AS t0_r1, "catalog_resources"."created_at" AS t0_r2, "catalog_resources"."state" AS t0_r3, "catalog_resources"."latest_released_at" AS t0_r4, "catalog_resources"."name" AS t0_r5, "catalog_resources"."description" AS t0_r6, "catalog_resources"."visibility_level" AS t0_r7, "catalog_resources"."search_vector" AS t0_r8, "projects"."id" AS t1_r0, "projects"."name" AS t1_r1, "projects"."path" AS t1_r2, "projects"."description" AS t1_r3, "projects"."created_at" AS t1_r4, "projects"."updated_at" AS t1_r5, "projects"."creator_id" AS t1_r6, "projects"."namespace_id" AS t1_r7, "projects"."last_activity_at" AS t1_r8, "projects"."import_url" AS t1_r9, "projects"."visibility_level" AS t1_r10, "projects"."archived" AS t1_r11, "projects"."avatar" AS t1_r12, "projects"."merge_requests_template" AS t1_r13, "projects"."star_count" AS t1_r14, "projects"."merge_requests_rebase_enabled" AS t1_r15, "projects"."import_type" AS t1_r16, "projects"."import_source" AS t1_r17, "projects"."approvals_before_merge" AS t1_r18, "projects"."reset_approvals_on_push" AS t1_r19, "projects"."merge_requests_ff_only_enabled" AS t1_r20, "projects"."issues_template" AS t1_r21, "projects"."mirror" AS t1_r22, "projects"."mirror_last_update_at" AS t1_r23, "projects"."mirror_last_successful_update_at" AS t1_r24, "projects"."mirror_user_id" AS t1_r25, "projects"."shared_runners_enabled" AS t1_r26, "projects"."runners_token" AS t1_r27, "projects"."build_allow_git_fetch" AS t1_r28, "projects"."build_timeout" AS t1_r29, "projects"."mirror_trigger_builds" AS t1_r30, "projects"."pending_delete" AS t1_r31, "projects"."public_builds" AS t1_r32, "projects"."last_repository_check_failed" AS t1_r33, "projects"."last_repository_check_at" AS t1_r34, "projects"."only_allow_merge_if_pipeline_succeeds" AS t1_r35, "projects"."has_external_issue_tracker" AS t1_r36, "projects"."repository_storage" AS t1_r37, "projects"."repository_read_only" AS t1_r38, "projects"."request_access_enabled" AS t1_r39, "projects"."has_external_wiki" AS t1_r40, "projects"."ci_config_path" AS t1_r41, "projects"."lfs_enabled" AS t1_r42, "projects"."description_html" AS t1_r43, "projects"."only_allow_merge_if_all_discussions_are_resolved" AS t1_r44, "projects"."repository_size_limit" AS t1_r45, "projects"."printing_merge_request_link_enabled" AS t1_r46, "projects"."auto_cancel_pending_pipelines" AS t1_r47, "projects"."service_desk_enabled" AS t1_r48, "projects"."cached_markdown_version" AS t1_r49, "projects"."delete_error" AS t1_r50, "projects"."last_repository_updated_at" AS t1_r51, "projects"."disable_overriding_approvers_per_merge_request" AS t1_r52, "projects"."storage_version" AS t1_r53, "projects"."resolve_outdated_diff_discussions" AS t1_r54, "projects"."remote_mirror_available_overridden" AS t1_r55, "projects"."only_mirror_protected_branches" AS t1_r56, "projects"."pull_mirror_available_overridden" AS t1_r57, "projects"."jobs_cache_index" AS t1_r58, "projects"."external_authorization_classification_label" AS t1_r59, "projects"."mirror_overwrites_diverged_branches" AS t1_r60, "projects"."pages_https_only" AS t1_r61, "projects"."external_webhook_token" AS t1_r62, "projects"."packages_enabled" AS t1_r63, "projects"."merge_requests_author_approval" AS t1_r64, "projects"."pool_repository_id" AS t1_r65, "projects"."runners_token_encrypted" AS t1_r66, "projects"."bfg_object_map" AS t1_r67, "projects"."detected_repository_languages" AS t1_r68, "projects"."merge_requests_disable_committers_approval" AS t1_r69, "projects"."require_password_to_approve" AS t1_r70, "projects"."max_pages_size" AS t1_r71, "projects"."max_artifacts_size" AS t1_r72, "projects"."pull_mirror_branch_prefix" AS t1_r73, "projects"."remove_source_branch_after_merge" AS t1_r74, "projects"."marked_for_deletion_at" AS t1_r75, "projects"."marked_for_deletion_by_user_id" AS t1_r76, "projects"."autoclose_referenced_issues" AS t1_r77, "projects"."suggestion_commit_message" AS t1_r78, "projects"."project_namespace_id" AS t1_r79, "projects"."hidden" AS t1_r80, "projects"."organization_id" AS t1_r81
FROM "catalog_resources"
INNER JOIN "projects" ON "projects"."id" = "catalog_resources"."project_id"
WHERE "catalog_resources"."state" = 1
AND "projects"."id" IN ( -- This is an unnecessary subquery that is also removed in the updated query.
  SELECT "projects"."id"
  FROM "projects"
  INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
  WHERE "project_authorizations"."user_id" = 12867582
) 
ORDER BY "catalog_resources"."created_at" DESC

Query plan link: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24636/commands/78403

After:

• This query no longer INNER JOINs with projects and is now executed in two queries to preload the projects.
• Moreover, this query was updated to mirror the all scope query so that it uses EXISTS.. instead of the IN (... subquery.

SELECT "catalog_resources".*
FROM "catalog_resources"
WHERE "catalog_resources"."state" = 1
AND (
  EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 12867582 AND (project_authorizations.project_id = catalog_resources.project_id))
)
ORDER BY "catalog_resources"."created_at" DESC

Query plan link: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24636/commands/78405

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_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"."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"."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"."autoclose_referenced_issues", "projects"."suggestion_commit_message", "projects"."project_namespace_id", "projects"."hidden", "projects"."organization_id"
FROM "projects"
WHERE "projects"."id" IN (52629264, 20783498, 52120058, 46810591, 45661242)

Query plan link: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24636/commands/78406

3. by_namespace query

Ci::Catalog::Listing.new(current_user).resources(namespace: namespace)

Before:

SELECT "catalog_resources"."id" AS t0_r0, "catalog_resources"."project_id" AS t0_r1, "catalog_resources"."created_at" AS t0_r2, "catalog_resources"."state" AS t0_r3, "catalog_resources"."latest_released_at" AS t0_r4, "catalog_resources"."name" AS t0_r5, "catalog_resources"."description" AS t0_r6, "catalog_resources"."visibility_level" AS t0_r7, "catalog_resources"."search_vector" AS t0_r8, "projects"."id" AS t1_r0, "projects"."name" AS t1_r1, "projects"."path" AS t1_r2, "projects"."description" AS t1_r3, "projects"."created_at" AS t1_r4, "projects"."updated_at" AS t1_r5, "projects"."creator_id" AS t1_r6, "projects"."namespace_id" AS t1_r7, "projects"."last_activity_at" AS t1_r8, "projects"."import_url" AS t1_r9, "projects"."visibility_level" AS t1_r10, "projects"."archived" AS t1_r11, "projects"."avatar" AS t1_r12, "projects"."merge_requests_template" AS t1_r13, "projects"."star_count" AS t1_r14, "projects"."merge_requests_rebase_enabled" AS t1_r15, "projects"."import_type" AS t1_r16, "projects"."import_source" AS t1_r17, "projects"."approvals_before_merge" AS t1_r18, "projects"."reset_approvals_on_push" AS t1_r19, "projects"."merge_requests_ff_only_enabled" AS t1_r20, "projects"."issues_template" AS t1_r21, "projects"."mirror" AS t1_r22, "projects"."mirror_last_update_at" AS t1_r23, "projects"."mirror_last_successful_update_at" AS t1_r24, "projects"."mirror_user_id" AS t1_r25, "projects"."shared_runners_enabled" AS t1_r26, "projects"."runners_token" AS t1_r27, "projects"."build_allow_git_fetch" AS t1_r28, "projects"."build_timeout" AS t1_r29, "projects"."mirror_trigger_builds" AS t1_r30, "projects"."pending_delete" AS t1_r31, "projects"."public_builds" AS t1_r32, "projects"."last_repository_check_failed" AS t1_r33, "projects"."last_repository_check_at" AS t1_r34, "projects"."only_allow_merge_if_pipeline_succeeds" AS t1_r35, "projects"."has_external_issue_tracker" AS t1_r36, "projects"."repository_storage" AS t1_r37, "projects"."repository_read_only" AS t1_r38, "projects"."request_access_enabled" AS t1_r39, "projects"."has_external_wiki" AS t1_r40, "projects"."ci_config_path" AS t1_r41, "projects"."lfs_enabled" AS t1_r42, "projects"."description_html" AS t1_r43, "projects"."only_allow_merge_if_all_discussions_are_resolved" AS t1_r44, "projects"."repository_size_limit" AS t1_r45, "projects"."printing_merge_request_link_enabled" AS t1_r46, "projects"."auto_cancel_pending_pipelines" AS t1_r47, "projects"."service_desk_enabled" AS t1_r48, "projects"."cached_markdown_version" AS t1_r49, "projects"."delete_error" AS t1_r50, "projects"."last_repository_updated_at" AS t1_r51, "projects"."disable_overriding_approvers_per_merge_request" AS t1_r52, "projects"."storage_version" AS t1_r53, "projects"."resolve_outdated_diff_discussions" AS t1_r54, "projects"."remote_mirror_available_overridden" AS t1_r55, "projects"."only_mirror_protected_branches" AS t1_r56, "projects"."pull_mirror_available_overridden" AS t1_r57, "projects"."jobs_cache_index" AS t1_r58, "projects"."external_authorization_classification_label" AS t1_r59, "projects"."mirror_overwrites_diverged_branches" AS t1_r60, "projects"."pages_https_only" AS t1_r61, "projects"."external_webhook_token" AS t1_r62, "projects"."packages_enabled" AS t1_r63, "projects"."merge_requests_author_approval" AS t1_r64, "projects"."pool_repository_id" AS t1_r65, "projects"."runners_token_encrypted" AS t1_r66, "projects"."bfg_object_map" AS t1_r67, "projects"."detected_repository_languages" AS t1_r68, "projects"."merge_requests_disable_committers_approval" AS t1_r69, "projects"."require_password_to_approve" AS t1_r70, "projects"."max_pages_size" AS t1_r71, "projects"."max_artifacts_size" AS t1_r72, "projects"."pull_mirror_branch_prefix" AS t1_r73, "projects"."remove_source_branch_after_merge" AS t1_r74, "projects"."marked_for_deletion_at" AS t1_r75, "projects"."marked_for_deletion_by_user_id" AS t1_r76, "projects"."autoclose_referenced_issues" AS t1_r77, "projects"."suggestion_commit_message" AS t1_r78, "projects"."project_namespace_id" AS t1_r79, "projects"."hidden" AS t1_r80, "projects"."organization_id" AS t1_r81
FROM "catalog_resources"
INNER JOIN "projects" ON "projects"."id" = "catalog_resources"."project_id"
WHERE "catalog_resources"."state" = 1
AND (
  EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 12867582 AND (project_authorizations.project_id = projects.id))
  OR projects.visibility_level IN (10,20)
)
AND "projects"."namespace_id" IN (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{6543}')))
ORDER BY "catalog_resources"."created_at" DESC

Query plan link: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24636/commands/78407

After:

• This query had minimal improvement. We still need to INNER JOIN with projects here because we haven't yet denormalized namespace_id nor traversal_ids.
• This is a legacy query that we may remove in the future.

SELECT "catalog_resources"."id" AS t0_r0, "catalog_resources"."project_id" AS t0_r1, "catalog_resources"."created_at" AS t0_r2, "catalog_resources"."state" AS t0_r3, "catalog_resources"."latest_released_at" AS t0_r4, "catalog_resources"."name" AS t0_r5, "catalog_resources"."description" AS t0_r6, "catalog_resources"."visibility_level" AS t0_r7, "catalog_resources"."search_vector" AS t0_r8, "projects"."id" AS t1_r0, "projects"."name" AS t1_r1, "projects"."path" AS t1_r2, "projects"."description" AS t1_r3, "projects"."created_at" AS t1_r4, "projects"."updated_at" AS t1_r5, "projects"."creator_id" AS t1_r6, "projects"."namespace_id" AS t1_r7, "projects"."last_activity_at" AS t1_r8, "projects"."import_url" AS t1_r9, "projects"."visibility_level" AS t1_r10, "projects"."archived" AS t1_r11, "projects"."avatar" AS t1_r12, "projects"."merge_requests_template" AS t1_r13, "projects"."star_count" AS t1_r14, "projects"."merge_requests_rebase_enabled" AS t1_r15, "projects"."import_type" AS t1_r16, "projects"."import_source" AS t1_r17, "projects"."approvals_before_merge" AS t1_r18, "projects"."reset_approvals_on_push" AS t1_r19, "projects"."merge_requests_ff_only_enabled" AS t1_r20, "projects"."issues_template" AS t1_r21, "projects"."mirror" AS t1_r22, "projects"."mirror_last_update_at" AS t1_r23, "projects"."mirror_last_successful_update_at" AS t1_r24, "projects"."mirror_user_id" AS t1_r25, "projects"."shared_runners_enabled" AS t1_r26, "projects"."runners_token" AS t1_r27, "projects"."build_allow_git_fetch" AS t1_r28, "projects"."build_timeout" AS t1_r29, "projects"."mirror_trigger_builds" AS t1_r30, "projects"."pending_delete" AS t1_r31, "projects"."public_builds" AS t1_r32, "projects"."last_repository_check_failed" AS t1_r33, "projects"."last_repository_check_at" AS t1_r34, "projects"."only_allow_merge_if_pipeline_succeeds" AS t1_r35, "projects"."has_external_issue_tracker" AS t1_r36, "projects"."repository_storage" AS t1_r37, "projects"."repository_read_only" AS t1_r38, "projects"."request_access_enabled" AS t1_r39, "projects"."has_external_wiki" AS t1_r40, "projects"."ci_config_path" AS t1_r41, "projects"."lfs_enabled" AS t1_r42, "projects"."description_html" AS t1_r43, "projects"."only_allow_merge_if_all_discussions_are_resolved" AS t1_r44, "projects"."repository_size_limit" AS t1_r45, "projects"."printing_merge_request_link_enabled" AS t1_r46, "projects"."auto_cancel_pending_pipelines" AS t1_r47, "projects"."service_desk_enabled" AS t1_r48, "projects"."cached_markdown_version" AS t1_r49, "projects"."delete_error" AS t1_r50, "projects"."last_repository_updated_at" AS t1_r51, "projects"."disable_overriding_approvers_per_merge_request" AS t1_r52, "projects"."storage_version" AS t1_r53, "projects"."resolve_outdated_diff_discussions" AS t1_r54, "projects"."remote_mirror_available_overridden" AS t1_r55, "projects"."only_mirror_protected_branches" AS t1_r56, "projects"."pull_mirror_available_overridden" AS t1_r57, "projects"."jobs_cache_index" AS t1_r58, "projects"."external_authorization_classification_label" AS t1_r59, "projects"."mirror_overwrites_diverged_branches" AS t1_r60, "projects"."pages_https_only" AS t1_r61, "projects"."external_webhook_token" AS t1_r62, "projects"."packages_enabled" AS t1_r63, "projects"."merge_requests_author_approval" AS t1_r64, "projects"."pool_repository_id" AS t1_r65, "projects"."runners_token_encrypted" AS t1_r66, "projects"."bfg_object_map" AS t1_r67, "projects"."detected_repository_languages" AS t1_r68, "projects"."merge_requests_disable_committers_approval" AS t1_r69, "projects"."require_password_to_approve" AS t1_r70, "projects"."max_pages_size" AS t1_r71, "projects"."max_artifacts_size" AS t1_r72, "projects"."pull_mirror_branch_prefix" AS t1_r73, "projects"."remove_source_branch_after_merge" AS t1_r74, "projects"."marked_for_deletion_at" AS t1_r75, "projects"."marked_for_deletion_by_user_id" AS t1_r76, "projects"."autoclose_referenced_issues" AS t1_r77, "projects"."suggestion_commit_message" AS t1_r78, "projects"."project_namespace_id" AS t1_r79, "projects"."hidden" AS t1_r80, "projects"."organization_id" AS t1_r81
FROM "catalog_resources" INNER JOIN "projects" ON "projects"."id" = "catalog_resources"."project_id"
WHERE "catalog_resources"."state" = 1
AND (
  EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 12867582 AND (project_authorizations.project_id = catalog_resources.project_id))
  OR catalog_resources.visibility_level IN (10,20)
)
AND "projects"."namespace_id" IN (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{6543}')))
ORDER BY "catalog_resources"."created_at" DESC

Query plan link: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24636/commands/78409

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 #429056 (closed)