Skip to content

N+1 SQL queries in Projects::MergeRequestsController#index

From https://gitlab.com/gitlab-org/gitlab/merge_requests:

image

50+ queries are just due to duplicate queries related to approvals!

  20             "sql": "SELECT \"approval_project_rules_protected_branches\".* FROM \"approval_project_rules_protected_branches\" WHERE \"approval_project_rules_protected_branches\".\"approval_project_rule_id\" = 72703",
  18             "sql": "SELECT \"approval_project_rules\".* FROM \"approval_project_rules\" WHERE \"approval_project_rules\".\"id\" = 72703",
   5             "sql": "SELECT  \"routes\".* FROM \"routes\" WHERE \"routes\".\"source_id\" = 9970 AND \"routes\".\"source_type\" = 'Namespace' LIMIT 1",
   2             "sql": "SELECT \"protected_branches\".* FROM \"protected_branches\" WHERE \"protected_branches\".\"project_id\" = 278964 AND \"protected_branches\".\"code_owner_approval_required\" = TRUE",
   2             "sql": "SELECT \"approval_project_rules\".* FROM \"approval_project_rules\" WHERE \"approval_project_rules\".\"project_id\" = 278964 AND \"approval_project_rules\".\"rule_type\" IN (0, 3) ORDER BY \"approval_project_rules\".\"rule_type\" DESC, \"approval_project_rules\".\"id\" ASC",
   2             "sql": "SELECT  \"projects\".* FROM \"projects\" WHERE \"projects\".\"id\" = 278964 LIMIT 1",
   2             "sql": "SELECT  \"namespaces\".* FROM \"namespaces\" WHERE \"namespaces\".\"id\" = 9970 LIMIT 1",
   1             "sql": "SELECT \"users\".* FROM \"users\" WHERE \"users\".\"id\" IN (4059254, 4985794, 3614911, 4419304, 3540517, 4087087, 3860200, 1562869, 283999, 4626651, 3945330, 493585, 3658061, 1149402, 2756898, 4080648, 2702368, 3993566, 1361348, 120073, 100770)",
   1             "sql": "SELECT \"users\".* FROM \"users\" WHERE \"users\".\"id\" IN (100770, 1607849, 120073, 2191276, 3993566, 4901936, 2020089, 7736, 2702368, 3658061, 64248, 3945330, 33100, 3860200, 4014344, 2561044, 3540517, 3163848, 4985794, 4059254)",
   1             "sql": "SELECT \"users\".* FROM ((SELECT \"users\".* FROM \"users\" INNER JOIN \"approval_merge_request_rules_users\" ON \"users\".\"id\" = \"approval_merge_request_rules_users\".\"user_id\" WHERE \"approval_merge_request_rules_users\".\"approval_merge_request_rule_id\" = 17404375)\nUNION\n(SELECT DISTINCT \"users\".* FROM \"users\" INNER JOIN \"members\" ON \"users\".\"id\" = \"members\".\"user_id\" INNER JOIN \"namespaces\" ON \"members\".\"source_id\" = \"namespaces\".\"id\" INNER JOIN \"approval_merge_request_rules_groups\" ON \"namespaces\".\"id\" = \"approval_merge_request_rules_groups\".\"group_id\" WHERE \"members\".\"type\" IN ('GroupMember') AND \"members\".\"source_type\" = 'Namespace' AND \"namespaces\".\"type\" IN ('Group') AND \"approval_merge_request_rules_groups\".\"approval_merge_request_rule_id\" = 17404375 AND \"members\".\"source_type\" = 'Namespace' AND \"members\".\"requested_at\" IS NULL)) users WHERE \"users\".\"id\" != 2702368 AND \"users\".\"id\" != 2702368",
   1             "sql": "SELECT \"users\".* FROM ((SELECT \"users\".* FROM \"users\" INNER JOIN \"approval_merge_request_rules_users\" ON \"users\".\"id\" = \"approval_merge_request_rules_users\".\"user_id\" WHERE \"approval_merge_request_rules_users\".\"approval_merge_request_rule_id\" = 17404374)\nUNION\n(SELECT DISTINCT \"users\".* FROM \"users\" INNER JOIN \"members\" ON \"users\".\"id\" = \"members\".\"user_id\" INNER JOIN \"namespaces\" ON \"members\".\"source_id\" = \"namespaces\".\"id\" INNER JOIN \"approval_merge_request_rules_groups\" ON \"namespaces\".\"id\" = \"approval_merge_request_rules_groups\".\"group_id\" WHERE \"members\".\"type\" IN ('GroupMember') AND \"members\".\"source_type\" = 'Namespace' AND \"namespaces\".\"type\" IN ('Group') AND \"approval_merge_request_rules_groups\".\"approval_merge_request_rule_id\" = 17404374 AND \"members\".\"source_type\" = 'Namespace' AND \"members\".\"requested_at\" IS NULL)) users WHERE \"users\".\"id\" != 2702368 AND \"users\".\"id\" != 2702368",
   1             "sql": "SELECT \"routes\".* FROM \"routes\" WHERE \"routes\".\"source_type\" = 'Project' AND \"routes\".\"source_id\" IN (278964, 4145167, 5450909, 8281994)",