Reduce number of SQL queries on SystemNote create (!59102) · Merge requests · GitLab.org / GitLab

Luke Duncalfe requested to merge 21067-reduce-SQL-calls-when-creating-SystemNotes into master Apr 12, 2021

What does this MR do?

This MR avoids issuing an extra SELECT query during the creation of a SystemNote.

Comparison

Using QueryRecorder within a test environment to inspect the before and after of running spec/services/system_notes/issuables_service_spec.rb:22:

Before

SAVEPOINT active_record_2 
SELECT "grafana_integrations".* FROM "grafana_integrations" WHERE "grafana_integrations"."project_id" = 1 LIMIT 1 
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"."avatar" AS t0_r12, "projects"."merge_requests_template" AS t0_r13, "projects"."star_count" AS t0_r14, "projects"."merge_requests_rebase_enabled" AS t0_r15, "projects"."import_type" AS t0_r16, "projects"."import_source" 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_coverage_regex" AS t0_r28, "projects"."build_allow_git_fetch" AS t0_r29, "projects"."build_timeout" AS t0_r30, "projects"."mirror_trigger_builds" AS t0_r31, "projects"."pending_delete" AS t0_r32, "projects"."public_builds" AS t0_r33, "projects"."last_repository_check_failed" AS t0_r34, "projects"."last_repository_check_at" AS t0_r35, "projects"."container_registry_enabled" AS t0_r36, "projects"."only_allow_merge_if_pipeline_succeeds" AS t0_r37, "projects"."has_external_issue_tracker" AS t0_r38, "projects"."repository_storage" AS t0_r39, "projects"."repository_read_only" AS t0_r40, "projects"."request_access_enabled" AS t0_r41, "projects"."has_external_wiki" AS t0_r42, "projects"."ci_config_path" AS t0_r43, "projects"."lfs_enabled" AS t0_r44, "projects"."description_html" AS t0_r45, "projects"."only_allow_merge_if_all_discussions_are_resolved" AS t0_r46, "projects"."repository_size_limit" AS t0_r47, "projects"."printing_merge_request_link_enabled" AS t0_r48, "projects"."auto_cancel_pending_pipelines" AS t0_r49, "projects"."service_desk_enabled" AS t0_r50, "projects"."cached_markdown_version" AS t0_r51, "projects"."delete_error" AS t0_r52, "projects"."last_repository_updated_at" AS t0_r53, "projects"."disable_overriding_approvers_per_merge_request" AS t0_r54, "projects"."storage_version" AS t0_r55, "projects"."resolve_outdated_diff_discussions" AS t0_r56, "projects"."remote_mirror_available_overridden" AS t0_r57, "projects"."only_mirror_protected_branches" AS t0_r58, "projects"."pull_mirror_available_overridden" AS t0_r59, "projects"."jobs_cache_index" AS t0_r60, "projects"."external_authorization_classification_label" AS t0_r61, "projects"."mirror_overwrites_diverged_branches" AS t0_r62, "projects"."pages_https_only" AS t0_r63, "projects"."external_webhook_token" AS t0_r64, "projects"."packages_enabled" AS t0_r65, "projects"."merge_requests_author_approval" AS t0_r66, "projects"."pool_repository_id" AS t0_r67, "projects"."runners_token_encrypted" AS t0_r68, "projects"."bfg_object_map" AS t0_r69, "projects"."detected_repository_languages" AS t0_r70, "projects"."merge_requests_disable_committers_approval" AS t0_r71, "projects"."require_password_to_approve" AS t0_r72, "projects"."emails_disabled" AS t0_r73, "projects"."max_pages_size" AS t0_r74, "projects"."max_artifacts_size" AS t0_r75, "projects"."pull_mirror_branch_prefix" AS t0_r76, "projects"."remove_source_branch_after_merge" AS t0_r77, "projects"."marked_for_deletion_at" AS t0_r78, "projects"."marked_for_deletion_by_user_id" AS t0_r79, "projects"."autoclose_referenced_issues" AS t0_r80, "projects"."suggestion_commit_message" AS t0_r81, "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, "namespaces"."id" AS t2_r0, "namespaces"."name" AS t2_r1, "namespaces"."path" AS t2_r2, "namespaces"."owner_id" AS t2_r3, "namespaces"."created_at" AS t2_r4, "namespaces"."updated_at" AS t2_r5, "namespaces"."type" AS t2_r6, "namespaces"."description" AS t2_r7, "namespaces"."avatar" AS t2_r8, "namespaces"."membership_lock" AS t2_r9, "namespaces"."share_with_group_lock" AS t2_r10, "namespaces"."visibility_level" AS t2_r11, "namespaces"."request_access_enabled" AS t2_r12, "namespaces"."ldap_sync_status" AS t2_r13, "namespaces"."ldap_sync_error" AS t2_r14, "namespaces"."ldap_sync_last_update_at" AS t2_r15, "namespaces"."ldap_sync_last_successful_update_at" AS t2_r16, "namespaces"."ldap_sync_last_sync_at" AS t2_r17, "namespaces"."description_html" AS t2_r18, "namespaces"."lfs_enabled" AS t2_r19, "namespaces"."parent_id" AS t2_r20, "namespaces"."shared_runners_minutes_limit" AS t2_r21, "namespaces"."repository_size_limit" AS t2_r22, "namespaces"."require_two_factor_authentication" AS t2_r23, "namespaces"."two_factor_grace_period" AS t2_r24, "namespaces"."cached_markdown_version" AS t2_r25, "namespaces"."project_creation_level" AS t2_r26, "namespaces"."runners_token" AS t2_r27, "namespaces"."file_template_project_id" AS t2_r28, "namespaces"."saml_discovery_token" AS t2_r29, "namespaces"."runners_token_encrypted" AS t2_r30, "namespaces"."custom_project_templates_group_id" AS t2_r31, "namespaces"."auto_devops_enabled" AS t2_r32, "namespaces"."extra_shared_runners_minutes_limit" AS t2_r33, "namespaces"."last_ci_minutes_notification_at" AS t2_r34, "namespaces"."last_ci_minutes_usage_notification_level" AS t2_r35, "namespaces"."subgroup_creation_level" AS t2_r36, "namespaces"."emails_disabled" AS t2_r37, "namespaces"."max_pages_size" AS t2_r38, "namespaces"."max_artifacts_size" AS t2_r39, "namespaces"."mentions_disabled" AS t2_r40, "namespaces"."default_branch_protection" AS t2_r41, "namespaces"."unlock_membership_to_ldap" AS t2_r42, "namespaces"."max_personal_access_token_lifetime" AS t2_r43, "namespaces"."push_rule_id" AS t2_r44, "namespaces"."shared_runners_enabled" AS t2_r45, "namespaces"."allow_descendants_override_disabled_shared_runners" AS t2_r46, "namespaces"."traversal_ids" AS t2_r47 FROM "projects" 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" WHERE ((LOWER(routes.path) = LOWER('namespace2/project2'))) 
SELECT "issues".* FROM "issues" WHERE "issues"."project_id" = 2 AND "issues"."iid" = 1 
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 5 AND "routes"."source_type" = 'Namespace' LIMIT 1 
INSERT INTO "notes" ("note", "noteable_type", "author_id", "created_at", "updated_at", "project_id", "noteable_id", "system", "discussion_id", "note_html", "cached_markdown_version") VALUES ('marked this issue as related to namespace2/project2#1', 'Issue', 3, '2021-04-23 01:27:30.594923', '2021-04-23 01:27:30.638261', 1, 1, TRUE, '03dc156b2fe76d7cda86a0ea246440970defae6d', '<p data-sourcepos="1:1-1:53" dir="auto">marked this issue as related to <a href="/namespace2/project2/-/issues/1" data-original="namespace2/project2#1" data-link="false" data-link-reference="false" data-project="2" data-issue="2" data-reference-type="issue" data-container="body" data-placement="top" title="My title 2" class="gfm gfm-issue has-tooltip">namespace2/project2#1</a></p>', 1769472) RETURNING "id" 
SAVEPOINT active_record_3 
SELECT "issue_user_mentions".* FROM "issue_user_mentions" WHERE "issue_user_mentions"."issue_id" = 1 AND "issue_user_mentions"."note_id" = 1 ORDER BY "issue_user_mentions"."id" ASC LIMIT 1 
RELEASE SAVEPOINT active_record_3 
SELECT 1 AS one FROM "geo_nodes" LIMIT 1 
RELEASE SAVEPOINT active_record_2 
SELECT "system_note_metadata".* FROM "system_note_metadata" WHERE "system_note_metadata"."note_id" = 1 LIMIT 1 
SAVEPOINT active_record_2 
INSERT INTO "system_note_metadata" ("note_id", "action", "created_at", "updated_at") VALUES (1, 'relate', '2021-04-23 01:27:30.767644', '2021-04-23 01:27:30.767644') RETURNING "id" 
RELEASE SAVEPOINT active_record_2

After

SAVEPOINT active_record_2 
SELECT "grafana_integrations".* FROM "grafana_integrations" WHERE "grafana_integrations"."project_id" = 1 LIMIT 1 
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"."avatar" AS t0_r12, "projects"."merge_requests_template" AS t0_r13, "projects"."star_count" AS t0_r14, "projects"."merge_requests_rebase_enabled" AS t0_r15, "projects"."import_type" AS t0_r16, "projects"."import_source" 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_coverage_regex" AS t0_r28, "projects"."build_allow_git_fetch" AS t0_r29, "projects"."build_timeout" AS t0_r30, "projects"."mirror_trigger_builds" AS t0_r31, "projects"."pending_delete" AS t0_r32, "projects"."public_builds" AS t0_r33, "projects"."last_repository_check_failed" AS t0_r34, "projects"."last_repository_check_at" AS t0_r35, "projects"."container_registry_enabled" AS t0_r36, "projects"."only_allow_merge_if_pipeline_succeeds" AS t0_r37, "projects"."has_external_issue_tracker" AS t0_r38, "projects"."repository_storage" AS t0_r39, "projects"."repository_read_only" AS t0_r40, "projects"."request_access_enabled" AS t0_r41, "projects"."has_external_wiki" AS t0_r42, "projects"."ci_config_path" AS t0_r43, "projects"."lfs_enabled" AS t0_r44, "projects"."description_html" AS t0_r45, "projects"."only_allow_merge_if_all_discussions_are_resolved" AS t0_r46, "projects"."repository_size_limit" AS t0_r47, "projects"."printing_merge_request_link_enabled" AS t0_r48, "projects"."auto_cancel_pending_pipelines" AS t0_r49, "projects"."service_desk_enabled" AS t0_r50, "projects"."cached_markdown_version" AS t0_r51, "projects"."delete_error" AS t0_r52, "projects"."last_repository_updated_at" AS t0_r53, "projects"."disable_overriding_approvers_per_merge_request" AS t0_r54, "projects"."storage_version" AS t0_r55, "projects"."resolve_outdated_diff_discussions" AS t0_r56, "projects"."remote_mirror_available_overridden" AS t0_r57, "projects"."only_mirror_protected_branches" AS t0_r58, "projects"."pull_mirror_available_overridden" AS t0_r59, "projects"."jobs_cache_index" AS t0_r60, "projects"."external_authorization_classification_label" AS t0_r61, "projects"."mirror_overwrites_diverged_branches" AS t0_r62, "projects"."pages_https_only" AS t0_r63, "projects"."external_webhook_token" AS t0_r64, "projects"."packages_enabled" AS t0_r65, "projects"."merge_requests_author_approval" AS t0_r66, "projects"."pool_repository_id" AS t0_r67, "projects"."runners_token_encrypted" AS t0_r68, "projects"."bfg_object_map" AS t0_r69, "projects"."detected_repository_languages" AS t0_r70, "projects"."merge_requests_disable_committers_approval" AS t0_r71, "projects"."require_password_to_approve" AS t0_r72, "projects"."emails_disabled" AS t0_r73, "projects"."max_pages_size" AS t0_r74, "projects"."max_artifacts_size" AS t0_r75, "projects"."pull_mirror_branch_prefix" AS t0_r76, "projects"."remove_source_branch_after_merge" AS t0_r77, "projects"."marked_for_deletion_at" AS t0_r78, "projects"."marked_for_deletion_by_user_id" AS t0_r79, "projects"."autoclose_referenced_issues" AS t0_r80, "projects"."suggestion_commit_message" AS t0_r81, "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, "namespaces"."id" AS t2_r0, "namespaces"."name" AS t2_r1, "namespaces"."path" AS t2_r2, "namespaces"."owner_id" AS t2_r3, "namespaces"."created_at" AS t2_r4, "namespaces"."updated_at" AS t2_r5, "namespaces"."type" AS t2_r6, "namespaces"."description" AS t2_r7, "namespaces"."avatar" AS t2_r8, "namespaces"."membership_lock" AS t2_r9, "namespaces"."share_with_group_lock" AS t2_r10, "namespaces"."visibility_level" AS t2_r11, "namespaces"."request_access_enabled" AS t2_r12, "namespaces"."ldap_sync_status" AS t2_r13, "namespaces"."ldap_sync_error" AS t2_r14, "namespaces"."ldap_sync_last_update_at" AS t2_r15, "namespaces"."ldap_sync_last_successful_update_at" AS t2_r16, "namespaces"."ldap_sync_last_sync_at" AS t2_r17, "namespaces"."description_html" AS t2_r18, "namespaces"."lfs_enabled" AS t2_r19, "namespaces"."parent_id" AS t2_r20, "namespaces"."shared_runners_minutes_limit" AS t2_r21, "namespaces"."repository_size_limit" AS t2_r22, "namespaces"."require_two_factor_authentication" AS t2_r23, "namespaces"."two_factor_grace_period" AS t2_r24, "namespaces"."cached_markdown_version" AS t2_r25, "namespaces"."project_creation_level" AS t2_r26, "namespaces"."runners_token" AS t2_r27, "namespaces"."file_template_project_id" AS t2_r28, "namespaces"."saml_discovery_token" AS t2_r29, "namespaces"."runners_token_encrypted" AS t2_r30, "namespaces"."custom_project_templates_group_id" AS t2_r31, "namespaces"."auto_devops_enabled" AS t2_r32, "namespaces"."extra_shared_runners_minutes_limit" AS t2_r33, "namespaces"."last_ci_minutes_notification_at" AS t2_r34, "namespaces"."last_ci_minutes_usage_notification_level" AS t2_r35, "namespaces"."subgroup_creation_level" AS t2_r36, "namespaces"."emails_disabled" AS t2_r37, "namespaces"."max_pages_size" AS t2_r38, "namespaces"."max_artifacts_size" AS t2_r39, "namespaces"."mentions_disabled" AS t2_r40, "namespaces"."default_branch_protection" AS t2_r41, "namespaces"."unlock_membership_to_ldap" AS t2_r42, "namespaces"."max_personal_access_token_lifetime" AS t2_r43, "namespaces"."push_rule_id" AS t2_r44, "namespaces"."shared_runners_enabled" AS t2_r45, "namespaces"."allow_descendants_override_disabled_shared_runners" AS t2_r46, "namespaces"."traversal_ids" AS t2_r47 FROM "projects" 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" WHERE ((LOWER(routes.path) = LOWER('namespace2/project2'))) 
SELECT "issues".* FROM "issues" WHERE "issues"."project_id" = 2 AND "issues"."iid" = 1 
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 5 AND "routes"."source_type" = 'Namespace' LIMIT 1 
INSERT INTO "notes" ("note", "noteable_type", "author_id", "created_at", "updated_at", "project_id", "noteable_id", "system", "discussion_id", "note_html", "cached_markdown_version") VALUES ('marked this issue as related to namespace2/project2#1', 'Issue', 3, '2021-04-23 01:27:30.594923', '2021-04-23 01:27:30.638261', 1, 1, TRUE, '03dc156b2fe76d7cda86a0ea246440970defae6d', '<p data-sourcepos="1:1-1:53" dir="auto">marked this issue as related to <a href="/namespace2/project2/-/issues/1" data-original="namespace2/project2#1" data-link="false" data-link-reference="false" data-project="2" data-issue="2" data-reference-type="issue" data-container="body" data-placement="top" title="My title 2" class="gfm gfm-issue has-tooltip">namespace2/project2#1</a></p>', 1769472) RETURNING "id" 
INSERT INTO "system_note_metadata" ("note_id", "action", "created_at", "updated_at") VALUES (1, 'relate', '2021-04-23 01:27:30.767644', '2021-04-23 01:27:30.767644') RETURNING "id"
SAVEPOINT active_record_3 
SELECT "issue_user_mentions".* FROM "issue_user_mentions" WHERE "issue_user_mentions"."issue_id" = 1 AND "issue_user_mentions"."note_id" = 1 ORDER BY "issue_user_mentions"."id" ASC LIMIT 1 
RELEASE SAVEPOINT active_record_3 
SELECT 1 AS one FROM "geo_nodes" LIMIT 1 
RELEASE SAVEPOINT active_record_2

Difference

(The INSERT INTO "system_note_metadata" statements are identical, but it has moved).

 SELECT "issues".* FROM "issues" WHERE "issues"."project_id" = 2 AND "issues"."iid" = 1
 SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 5 AND "routes"."source_type" = 'Namespace' LIMIT 1
 INSERT INTO "notes" ("note", "noteable_type", "author_id", "created_at", "updated_at", "project_id", "noteable_id", "system", "discussion_id", "note_html", "cached_markdown_version") VALUES ('marked this issue as related to namespace2/project2#1', 'Issue', 3, '2021-04-23 01:27:30.594923', '2021-04-23 01:27:30.638261', 1, 1, TRUE, '03dc156b2fe76d7cda86a0ea246440970defae6d', '<p data-sourcepos="1:1-1:53" dir="auto">marked this issue as related to <a href="/namespace2/project2/-/issues/1" data-original="namespace2/project2#1" data-link="false" data-link-reference="false" data-project="2" data-issue="2" data-reference-type="issue" data-container="body" data-placement="top" title="My title 2" class="gfm gfm-issue has-tooltip">namespace2/project2#1</a></p>', 1769472) RETURNING "id"
+INSERT INTO "system_note_metadata" ("note_id", "action", "created_at", "updated_at") VALUES (1, 'relate', '2021-04-23 01:27:30.767644', '2021-04-23 01:27:30.767644') RETURNING "id"
 SAVEPOINT active_record_3
 SELECT "issue_user_mentions".* FROM "issue_user_mentions" WHERE "issue_user_mentions"."issue_id" = 1 AND "issue_user_mentions"."note_id" = 1 ORDER BY "issue_user_mentions"."id" ASC LIMIT 1
 RELEASE SAVEPOINT active_record_3
 SELECT 1 AS one FROM "geo_nodes" LIMIT 1
 RELEASE SAVEPOINT active_record_2
-SELECT "system_note_metadata".* FROM "system_note_metadata" WHERE "system_note_metadata"."note_id" = 1 LIMIT 1
-SAVEPOINT active_record_2
-INSERT INTO "system_note_metadata" ("note_id", "action", "created_at", "updated_at") VALUES (1, 'relate', '2021-04-23 01:27:30.767644', '2021-04-23 01:27:30.767644') RETURNING "id"
-RELEASE SAVEPOINT active_record_2

Does this MR meet the acceptance criteria?

Conformity

📋 Does this MR need a changelog?
- I have included a changelog entry.
[-] Documentation (if required)
Code review guidelines
Merge request performance guidelines
Style guides
[-] Database guides
[-] Separation of EE specific content

Related to #21067 (closed)

Edited Apr 23, 2021 by Luke Duncalfe

Reduce number of SQL queries on SystemNote create