Skip to content

Reduce query load on access level preload

Alex Pooley requested to merge 334667-internal-server-error-500-when-a-request-with-pagination-size-under-12-on-gitlab-com into master

Related to #334667 (closed)

What does this MR do and why?

This MR solves some performance problems with the /api/v4/projects endpoint where the preloading of access levels is very slow.

There's two main problems:

  1. The @projects records query is effectively performed twice.
  2. The access_levels query can produce very poor queries due to the join of @projects.

This specific problem was noted in #334667 (comment 632358325).

This MR changes the query to use left outer joins instead of a subquery.

alias is used because there's another join performed in projects_finder.rb. otherwise, http://localhost:3000/api/v4/projects?membership=true&page=1&per_page=10 gives a 500 error because of:

ActiveRecord::StatementInvalid (PG::DuplicateAlias: ERROR:  table name "project_authorizations" specified more than once

Query plans

Before

Without membership param

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/9431/commands/33497

Raw query 
SELECT
    MAX("project_authorizations"."access_level") AS maximum_access_level,
    "project_authorizations"."project_id" AS project_authorizations_project_id
FROM
    "project_authorizations"
WHERE
    "project_authorizations"."user_id" = 10327656
    AND "project_authorizations"."project_id" IN (
        SELECT
            "projects"."id"
        FROM
            "projects"
        WHERE (EXISTS (
                SELECT
                    1
                FROM
                    "project_authorizations"
                WHERE
                    "project_authorizations"."user_id" = 10327656
                    AND (project_authorizations.project_id = projects.id))
                OR projects.visibility_level IN (0, 10, 20))
            AND "projects"."pending_delete" = FALSE
            AND "projects"."hidden" = FALSE
        ORDER BY
            "projects"."created_at" DESC,
            "projects"."id" DESC
        LIMIT 20 OFFSET 0)
GROUP BY
    "project_authorizations"."project_id"

With membership param

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/9431/commands/33496

Raw query 
SELECT
    MAX("project_authorizations"."access_level") AS maximum_access_level,
    "project_authorizations"."project_id" AS project_authorizations_project_id
FROM
    "project_authorizations"
WHERE
    "project_authorizations"."user_id" = 10327656
    AND "project_authorizations"."project_id" IN (
        SELECT
            "projects"."id"
        FROM
            "projects"
            INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
        WHERE
            "project_authorizations"."user_id" = 10327656
            AND "projects"."pending_delete" = FALSE
            AND "projects"."hidden" = FALSE
        ORDER BY
            "projects"."created_at" DESC,
            "projects"."id" DESC
        LIMIT 10 OFFSET 0)
GROUP BY
    "project_authorizations"."project_id"

After

Without membership param

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/9431/commands/33494

Raw query 
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",
    "projects"."hidden",
    "project_authorizations_2"."access_level",
    "projects"."id" AS t0_r0,
    "project_authorizations"."user_id" AS t1_r0,
    "project_authorizations"."project_id" AS t1_r1,
    "project_authorizations"."access_level" AS t1_r2
FROM
    "projects"
    LEFT OUTER JOIN "project_authorizations" ON "project_authorizations"."project_id" = "projects"."id"
    LEFT OUTER JOIN "project_authorizations" "project_authorizations_2" ON "project_authorizations_2"."project_id" = "projects"."id"
        AND "project_authorizations_2"."user_id" = 10327656
WHERE (EXISTS (
        SELECT
            1
        FROM
            "project_authorizations"
        WHERE
            "project_authorizations"."user_id" = 10327656
            AND (project_authorizations.project_id = projects.id))
        OR projects.visibility_level IN (0, 10, 20))
AND "projects"."pending_delete" = FALSE
AND "projects"."hidden" = FALSE
AND "projects"."id" IN (34905807, 34905795, 34905793, 34905786, 34905778, 34905774, 34905766, 34905756, 34905737, 34905731, 34905729, 34905723, 34905713, 34905703, 34905674, 34905667, 34905665, 34905658, 34905651, 34905647, 34905643, 34905640, 34905638, 34905623, 34905613, 34905565, 34905543, 34905489, 34905474, 34905454, 34905448, 34905447, 34905440, 34905437, 34905414, 34905409, 34905408, 34905399, 34905398, 34905380, 34905363, 34905349, 34905347, 34905342, 34905328, 34905319, 34905297, 34905294, 34905284, 34905270, 34905261, 34905199, 34905192, 34905191, 34905187, 34905173, 34905167, 34905162, 34905153, 34905151, 34905117, 34905110, 34905104, 34905099, 34905097, 34905048, 34905034, 34905008, 34905000, 34904999, 34904997, 34904994, 34904978, 34904941, 34904922, 34904920, 34904916, 34904872, 34904832, 34904819, 34904778, 34904744, 34904737, 34904734, 34904715, 34904708, 34904656, 34904652, 34904645, 34904629, 34904603, 34904595, 34904591, 34904570, 34904569, 34904564, 34904562, 34904537, 34904533, 34904531)
ORDER BY
    "projects"."created_at" DESC,
    "projects"."id" DESC

With membership param

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/9431/commands/33495

Raw query 
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",
    "projects"."hidden",
    "project_authorizations_2"."access_level",
    "projects"."id" AS t0_r0,
    "project_authorizations_projects"."user_id" AS t1_r0,
    "project_authorizations_projects"."project_id" AS t1_r1,
    "project_authorizations_projects"."access_level" AS t1_r2
FROM
    "projects"
    INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
    LEFT OUTER JOIN "project_authorizations" "project_authorizations_projects" ON "project_authorizations_projects"."project_id" = "projects"."id"
    LEFT OUTER JOIN "project_authorizations" "project_authorizations_2" ON "project_authorizations_2"."project_id" = "projects"."id"
        AND "project_authorizations_2"."user_id" = 10327656
WHERE
    "project_authorizations"."user_id" = 10327656
    AND "projects"."pending_delete" = FALSE
    AND "projects"."hidden" = FALSE
    AND "projects"."id" IN (34535775, 34535770, 34535766, 34535762, 34535759, 34535756, 34535745, 34535739, 34535724, 34535720, 34535716, 34535711, 34535708, 34340878, 34340830, 34340823, 34340814, 34340810, 34340806, 34340803, 34340799, 34340795, 8207776)
ORDER BY
    "projects"."created_at" DESC,
    "projects"."id" DESC

How to set up and validate locally

Check the queries generated for:

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Abdul Wadood

Merge request reports