Skip to content

Reduce query count for extremely frequent ExpirePipelineCacheWorker [RUN ALL RSPEC] [RUN AS-IF-FOSS]

Thong Kuah requested to merge reduce_expire_pipeline_cache_worker_queries into master

What does this MR do?

Reduce query count for extremely frequent worker. Consolide 5x queries into one using eager_load, which saves us 4N query count for a worker which runs 1 million times an hour at peak.

Related issues:

List of queries (locally) before:

SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 1 LIMIT 1 /*application:test*/
SELECT "projects".* FROM "projects" WHERE "projects"."id" = 1 LIMIT 1 /*application:test*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 2 LIMIT 1 /*application:test*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 2 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Project' LIMIT 1 /*application:test*/
SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."source_project_id" = 1 AND "merge_requests"."source_branch" = 'master' AND (EXISTS (SELECT 1 FROM "merge_request_diffs" INNER JOIN "merge_request_diff_commits" ON "merge_request_diff_commits"."merge_request_diff_id" = "merge_request_diffs"."id" WHERE (merge_requests.latest_merge_request_diff_id = merge_request_diffs.id) AND "merge_request_diff_commits"."sha" = '\xb83d6e391c22777fca1ed3012fce84f633d7fed0')) /*application:test*/
WITH RECURSIVE "base_and_ancestors" AS ((SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 1)
UNION
(SELECT "ci_pipelines".* FROM "ci_pipelines", "base_and_ancestors", "ci_sources_pipelines" WHERE "ci_sources_pipelines"."source_pipeline_id" = "ci_pipelines"."id" AND "ci_sources_pipelines"."pipeline_id" = "base_and_ancestors"."id" AND TRUE)), "base_and_descendants" AS ((SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 1)
UNION
(SELECT "ci_pipelines".* FROM "ci_pipelines", "base_and_descendants", "ci_sources_pipelines" WHERE "ci_sources_pipelines"."pipeline_id" = "ci_pipelines"."id" AND "ci_sources_pipelines"."source_pipeline_id" = "base_and_descendants"."id" AND TRUE)) SELECT "ci_pipelines".* FROM ((SELECT "ci_pipelines".* FROM "base_and_ancestors" AS "ci_pipelines")
UNION
(SELECT "ci_pipelines".* FROM "base_and_descendants" AS "ci_pipelines")) ci_pipelines /*application:test*/
SELECT "projects".* FROM "projects" WHERE "projects"."id" = 1 LIMIT 1 /*application:test*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 2 LIMIT 1 /*application:test*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 2 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test*/

plans:

After (locally):

SELECT "ci_pipelines"."id" AS t0_r0, "ci_pipelines"."ref" AS t0_r1, "ci_pipelines"."sha" AS t0_r2, "ci_pipelines"."before_sha" AS t0_r3, "ci_pipelines"."created_at" AS t0_r4, "ci_pipelines"."updated_at" AS t0_r5, "ci_pipelines"."tag" AS t0_r6, "ci_pipelines"."yaml_errors" AS t0_r7, "ci_pipelines"."committed_at" AS t0_r8, "ci_pipelines"."project_id" AS t0_r9, "ci_pipelines"."status" AS t0_r10, "ci_pipelines"."started_at" AS t0_r11, "ci_pipelines"."finished_at" AS t0_r12, "ci_pipelines"."duration" AS t0_r13, "ci_pipelines"."user_id" AS t0_r14, "ci_pipelines"."lock_version" AS t0_r15, "ci_pipelines"."auto_canceled_by_id" AS t0_r16, "ci_pipelines"."pipeline_schedule_id" AS t0_r17, "ci_pipelines"."source" AS t0_r18, "ci_pipelines"."config_source" AS t0_r19, "ci_pipelines"."protected" AS t0_r20, "ci_pipelines"."failure_reason" AS t0_r21, "ci_pipelines"."iid" AS t0_r22, "ci_pipelines"."merge_request_id" AS t0_r23, "ci_pipelines"."source_sha" AS t0_r24, "ci_pipelines"."target_sha" AS t0_r25, "ci_pipelines"."external_pull_request_id" AS t0_r26, "ci_pipelines"."ci_ref_id" AS t0_r27, "ci_pipelines"."locked" AS t0_r28, "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_coverage_regex" AS t1_r28, "projects"."build_allow_git_fetch" AS t1_r29, "projects"."build_timeout" AS t1_r30, "projects"."mirror_trigger_builds" AS t1_r31, "projects"."pending_delete" AS t1_r32, "projects"."public_builds" AS t1_r33, "projects"."last_repository_check_failed" AS t1_r34, "projects"."last_repository_check_at" AS t1_r35, "projects"."container_registry_enabled" AS t1_r36, "projects"."only_allow_merge_if_pipeline_succeeds" AS t1_r37, "projects"."has_external_issue_tracker" AS t1_r38, "projects"."repository_storage" AS t1_r39, "projects"."repository_read_only" AS t1_r40, "projects"."request_access_enabled" AS t1_r41, "projects"."has_external_wiki" AS t1_r42, "projects"."ci_config_path" AS t1_r43, "projects"."lfs_enabled" AS t1_r44, "projects"."description_html" AS t1_r45, "projects"."only_allow_merge_if_all_discussions_are_resolved" AS t1_r46, "projects"."repository_size_limit" AS t1_r47, "projects"."printing_merge_request_link_enabled" AS t1_r48, "projects"."auto_cancel_pending_pipelines" AS t1_r49, "projects"."service_desk_enabled" AS t1_r50, "projects"."cached_markdown_version" AS t1_r51, "projects"."delete_error" AS t1_r52, "projects"."last_repository_updated_at" AS t1_r53, "projects"."disable_overriding_approvers_per_merge_request" AS t1_r54, "projects"."storage_version" AS t1_r55, "projects"."resolve_outdated_diff_discussions" AS t1_r56, "projects"."remote_mirror_available_overridden" AS t1_r57, "projects"."only_mirror_protected_branches" AS t1_r58, "projects"."pull_mirror_available_overridden" AS t1_r59, "projects"."jobs_cache_index" AS t1_r60, "projects"."external_authorization_classification_label" AS t1_r61, "projects"."mirror_overwrites_diverged_branches" AS t1_r62, "projects"."pages_https_only" AS t1_r63, "projects"."external_webhook_token" AS t1_r64, "projects"."packages_enabled" AS t1_r65, "projects"."merge_requests_author_approval" AS t1_r66, "projects"."pool_repository_id" AS t1_r67, "projects"."runners_token_encrypted" AS t1_r68, "projects"."bfg_object_map" AS t1_r69, "projects"."detected_repository_languages" AS t1_r70, "projects"."merge_requests_disable_committers_approval" AS t1_r71, "projects"."require_password_to_approve" AS t1_r72, "projects"."emails_disabled" AS t1_r73, "projects"."max_pages_size" AS t1_r74, "projects"."max_artifacts_size" AS t1_r75, "projects"."pull_mirror_branch_prefix" AS t1_r76, "projects"."remove_source_branch_after_merge" AS t1_r77, "projects"."marked_for_deletion_at" AS t1_r78, "projects"."marked_for_deletion_by_user_id" AS t1_r79, "projects"."autoclose_referenced_issues" AS t1_r80, "projects"."suggestion_commit_message" AS t1_r81, "routes"."id" AS t2_r0, "routes"."source_id" AS t2_r1, "routes"."source_type" AS t2_r2, "routes"."path" AS t2_r3, "routes"."created_at" AS t2_r4, "routes"."updated_at" AS t2_r5, "routes"."name" AS t2_r6, "namespaces"."id" AS t3_r0, "namespaces"."name" AS t3_r1, "namespaces"."path" AS t3_r2, "namespaces"."owner_id" AS t3_r3, "namespaces"."created_at" AS t3_r4, "namespaces"."updated_at" AS t3_r5, "namespaces"."type" AS t3_r6, "namespaces"."description" AS t3_r7, "namespaces"."avatar" AS t3_r8, "namespaces"."membership_lock" AS t3_r9, "namespaces"."share_with_group_lock" AS t3_r10, "namespaces"."visibility_level" AS t3_r11, "namespaces"."request_access_enabled" AS t3_r12, "namespaces"."ldap_sync_status" AS t3_r13, "namespaces"."ldap_sync_error" AS t3_r14, "namespaces"."ldap_sync_last_update_at" AS t3_r15, "namespaces"."ldap_sync_last_successful_update_at" AS t3_r16, "namespaces"."ldap_sync_last_sync_at" AS t3_r17, "namespaces"."description_html" AS t3_r18, "namespaces"."lfs_enabled" AS t3_r19, "namespaces"."parent_id" AS t3_r20, "namespaces"."shared_runners_minutes_limit" AS t3_r21, "namespaces"."repository_size_limit" AS t3_r22, "namespaces"."require_two_factor_authentication" AS t3_r23, "namespaces"."two_factor_grace_period" AS t3_r24, "namespaces"."cached_markdown_version" AS t3_r25, "namespaces"."project_creation_level" AS t3_r26, "namespaces"."runners_token" AS t3_r27, "namespaces"."file_template_project_id" AS t3_r28, "namespaces"."saml_discovery_token" AS t3_r29, "namespaces"."runners_token_encrypted" AS t3_r30, "namespaces"."custom_project_templates_group_id" AS t3_r31, "namespaces"."auto_devops_enabled" AS t3_r32, "namespaces"."extra_shared_runners_minutes_limit" AS t3_r33, "namespaces"."last_ci_minutes_notification_at" AS t3_r34, "namespaces"."last_ci_minutes_usage_notification_level" AS t3_r35, "namespaces"."subgroup_creation_level" AS t3_r36, "namespaces"."emails_disabled" AS t3_r37, "namespaces"."max_pages_size" AS t3_r38, "namespaces"."max_artifacts_size" AS t3_r39, "namespaces"."mentions_disabled" AS t3_r40, "namespaces"."default_branch_protection" AS t3_r41, "namespaces"."unlock_membership_to_ldap" AS t3_r42, "namespaces"."max_personal_access_token_lifetime" AS t3_r43, "namespaces"."push_rule_id" AS t3_r44, "namespaces"."shared_runners_enabled" AS t3_r45, "namespaces"."allow_descendants_override_disabled_shared_runners" AS t3_r46, "namespaces"."traversal_ids" AS t3_r47, "namespaces"."delayed_project_removal" AS t3_r48, "routes_namespaces"."id" AS t4_r0, "routes_namespaces"."source_id" AS t4_r1, "routes_namespaces"."source_type" AS t4_r2, "routes_namespaces"."path" AS t4_r3, "routes_namespaces"."created_at" AS t4_r4, "routes_namespaces"."updated_at" AS t4_r5, "routes_namespaces"."name" AS t4_r6 FROM "ci_pipelines" LEFT OUTER JOIN "projects" ON "projects"."id" = "ci_pipelines"."project_id" LEFT OUTER JOIN "routes" ON "routes"."source_type" = 'Project' AND "routes"."source_id" = "projects"."id" LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" LEFT OUTER JOIN "routes" "routes_namespaces" ON "routes_namespaces"."source_type" = 'Namespace' AND "routes_namespaces"."source_id" = "namespaces"."id" WHERE "ci_pipelines"."id" = 1 LIMIT 1 /*application:test*/
SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."source_project_id" = 1 AND "merge_requests"."source_branch" = 'master' AND (EXISTS (SELECT 1 FROM "merge_request_diffs" INNER JOIN "merge_request_diff_commits" ON "merge_request_diff_commits"."merge_request_diff_id" = "merge_request_diffs"."id" WHERE (merge_requests.latest_merge_request_diff_id = merge_request_diffs.id) AND "merge_request_diff_commits"."sha" = '\xb83d6e391c22777fca1ed3012fce84f633d7fed0')) /*application:test*/
WITH RECURSIVE "base_and_ancestors" AS ((SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 1)
UNION
(SELECT "ci_pipelines".* FROM "ci_pipelines", "base_and_ancestors", "ci_sources_pipelines" WHERE "ci_sources_pipelines"."source_pipeline_id" = "ci_pipelines"."id" AND "ci_sources_pipelines"."pipeline_id" = "base_and_ancestors"."id" AND TRUE)), "base_and_descendants" AS ((SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 1)
UNION
(SELECT "ci_pipelines".* FROM "ci_pipelines", "base_and_descendants", "ci_sources_pipelines" WHERE "ci_sources_pipelines"."pipeline_id" = "ci_pipelines"."id" AND "ci_sources_pipelines"."source_pipeline_id" = "base_and_descendants"."id" AND TRUE)) SELECT "ci_pipelines".* FROM ((SELECT "ci_pipelines".* FROM "base_and_ancestors" AS "ci_pipelines")
UNION
(SELECT "ci_pipelines".* FROM "base_and_descendants" AS "ci_pipelines")) ci_pipelines /*application:test*/
SELECT "projects".* FROM "projects" WHERE "projects"."id" = 1 LIMIT 1 /*application:test*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 2 LIMIT 1 /*application:test*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 2 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test*/

Query plan for new eager-loaded query: https://console.postgres.ai/gitlab/gitlab-production-tunnel/sessions/2928/commands/9321 https://console.postgres.ai/gitlab/gitlab-production-tunnel/sessions/2940/commands/9378

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Edited by Mayra Cabrera

Merge request reports