Fix N+1 queries for linked items widget

Eugenia Grieffrequested to merge
eg/fix-linked-items-n-plus-one-queries into master

What does this MR do and why?

Fixes Fix N+1 queries in work items field linkedItems (#512056 - closed)

The Problem

A fix was attempted before but had to be reverted because it produced incorrect results when the field was nested in queries.

Here's why this happened:

  • The original implementation used the same batch loader key for all instances of linkedItems in a query
  • When linkedItems was nested (appeared multiple times in the query path), results from different nesting levels were mixed together
  • This caused data from parent levels to incorrectly appear in child levels

The New Solution

The new approach creates unique batch loader keys based on the nesting level:

  • We track the query's "current_path" to identify how deeply nested we are
  • Each time we encounter a linkedItems field, we assign it a batch key that includes its nesting level
  • This ensures that items at each nesting level are batched separately

Example

graphqlworkItems(iids: ['1', '2']) { # Uses batch key: `linked_items_level_0`
  linkedItems {
    workItem {
      title
      linkedItems { # Uses batch key: `linked_items_level_1`
        workItem {
          title
        }
      }
    }
  }
}

By using different batch keys for each nesting level, we maintain proper data isolation between levels while still solving the N+1 query problem at each level. Also, this time, I used the feature flag batch_load_linked_items to ensure we can test this safely.

DB queries before 
  WorkItem Load (3.2ms)  SELECT issues.*, issue_links.id AS issue_link_id, issue_links.link_type as issue_link_type_value, issue_links.target_id as issue_link_source_id, issue_links.created_at as issue_link_created_at, issue_links.updated_at as issue_link_updated_at FROM "issues" INNER JOIN issue_links ON
         (issue_links.source_id = issues.id AND issue_links.target_id = 965)
         OR
         (issue_links.target_id = issues.id AND issue_links.source_id = 965) ORDER BY "issue_links"."id" DESC LIMIT 101

WorkItem Load (3.3ms)  SELECT issues.*, issue_links.id AS issue_link_id, issue_links.link_type as issue_link_type_value, issue_links.target_id as issue_link_source_id, issue_links.created_at as issue_link_created_at, issue_links.updated_at as issue_link_updated_at FROM "issues" INNER JOIN issue_links ON
         (issue_links.source_id = issues.id AND issue_links.target_id = 966)
         OR
         (issue_links.target_id = issues.id AND issue_links.source_id = 966) ORDER BY "issue_links"."id" DESC LIMIT 101

WorkItem Load (5.0ms)  SELECT issues.*, issue_links.id AS issue_link_id, issue_links.link_type as issue_link_type_value, issue_links.target_id as issue_link_source_id, issue_links.created_at as issue_link_created_at, issue_links.updated_at as issue_link_updated_at FROM "issues" INNER JOIN issue_links ON
         (issue_links.source_id = issues.id AND issue_links.target_id = 967)
         OR
         (issue_links.target_id = issues.id AND issue_links.source_id = 967) ORDER BY "issue_links"."id" DESC LIMIT 101
DB query after 
WorkItem Load (5.5ms)  SELECT issues.*,
                issue_links.id AS issue_link_id,
                issue_links.link_type AS issue_link_type_value,
                issue_links.target_id AS issue_link_source_id,
                issue_links.source_id AS issue_link_target_id,
                issue_links.created_at AS issue_link_created_at,
                issue_links.updated_at AS issue_link_updated_at FROM "issues" INNER JOIN issue_links ON
          (issue_links.source_id = issues.id AND issue_links.target_id IN (967,966,965))
          OR
          (issue_links.target_id = issues.id AND issue_links.source_id IN (967,966,965)) ORDER BY "issue_links"."id" DESC

Query plans

Without filtering by link type

WorkItem.linked_items_for(ids_array)
query 
SELECT issues.*,
                issue_links.id AS issue_link_id,
                issue_links.link_type AS issue_link_type_value,
                issue_links.target_id AS issue_link_source_id,
                issue_links.source_id AS issue_link_target_id,
                issue_links.created_at AS issue_link_created_at,
                issue_links.updated_at AS issue_link_updated_at FROM "issues" INNER JOIN issue_links ON
          (issue_links.source_id = issues.id AND issue_links.target_id IN (164551864,164474216,164474118,164118864,164118850,164118823,164051578,164051122,164044814,163836681,163836641,163831038,163572458,163564361,163551930,163551893,162732130,162314309,162128877,161963840,161962420,161961168,161805933,161805928,161716525,161695050,161560415,161119408,161119393,160974539,160836934,160836824,160552349,160552338,160350560,159001737,159001714,158852538,158695956,158658714,158029982,157928937,157928932,157928923,157341800,157341791,157341779,157313978,156996277,156920400,155335302,155204721,155143516,155130594,155129400,154971322,154948903,154948890,154948881,154870923,154870651,154869328,154865774,154855896,154854757,154530829,154529364,154493733,154388478,154352318,152623004,152005864,151225402))
          OR
          (issue_links.target_id = issues.id AND issue_links.source_id IN (164551864,164474216,164474118,164118864,164118850,164118823,164051578,164051122,164044814,163836681,163836641,163831038,163572458,163564361,163551930,163551893,162732130,162314309,162128877,161963840,161962420,161961168,161805933,161805928,161716525,161695050,161560415,161119408,161119393,160974539,160836934,160836824,160552349,160552338,160350560,159001737,159001714,158852538,158695956,158658714,158029982,157928937,157928932,157928923,157341800,157341791,157341779,157313978,156996277,156920400,155335302,155204721,155143516,155130594,155129400,154971322,154948903,154948890,154948881,154870923,154870651,154869328,154865774,154855896,154854757,154530829,154529364,154493733,154388478,154352318,152623004,152005864,151225402)) ORDER BY "issue_links"."id" DESC;

PLAN: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/37806/commands/115477

Filtered by relates_to link type

WorkItem.linked_items_for(ids_array, link_type: 'relates_to')
query 
SELECT issues.*,
                issue_links.id AS issue_link_id,
                issue_links.link_type AS issue_link_type_value,
                issue_links.target_id AS issue_link_source_id,
                issue_links.source_id AS issue_link_target_id,
                issue_links.created_at AS issue_link_created_at,
                issue_links.updated_at AS issue_link_updated_at FROM "issues" INNER JOIN issue_links ON
          (issue_links.source_id = issues.id AND issue_links.target_id IN (164551864,164474216,164474118,164118864,164118850,164118823,164051578,164051122,164044814,163836681,163836641,163831038,163572458,163564361,163551930,163551893,162732130,162314309,162128877,161963840,161962420,161961168,161805933,161805928,161716525,161695050,161560415,161119408,161119393,160974539,160836934,160836824,160552349,160552338,160350560,159001737,159001714,158852538,158695956,158658714,158029982,157928937,157928932,157928923,157341800,157341791,157341779,157313978,156996277,156920400,155335302,155204721,155143516,155130594,155129400,154971322,154948903,154948890,154948881,154870923,154870651,154869328,154865774,154855896,154854757,154530829,154529364,154493733,154388478,154352318,152623004,152005864,151225402) AND issue_links.link_type = 0)
          OR
          (issue_links.target_id = issues.id AND issue_links.source_id IN (164551864,164474216,164474118,164118864,164118850,164118823,164051578,164051122,164044814,163836681,163836641,163831038,163572458,163564361,163551930,163551893,162732130,162314309,162128877,161963840,161962420,161961168,161805933,161805928,161716525,161695050,161560415,161119408,161119393,160974539,160836934,160836824,160552349,160552338,160350560,159001737,159001714,158852538,158695956,158658714,158029982,157928937,157928932,157928923,157341800,157341791,157341779,157313978,156996277,156920400,155335302,155204721,155143516,155130594,155129400,154971322,154948903,154948890,154948881,154870923,154870651,154869328,154865774,154855896,154854757,154530829,154529364,154493733,154388478,154352318,152623004,152005864,151225402) AND issue_links.link_type = 0) ORDER BY "issue_links"."id" DESC;

PLAN: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/37806/commands/115479

Filtered by blocks link type

WorkItem.linked_items_for(ids_array, link_type: 'blocks')
query 
SELECT issues.*,
                issue_links.id AS issue_link_id,
                issue_links.link_type AS issue_link_type_value,
                issue_links.target_id AS issue_link_source_id,
                issue_links.source_id AS issue_link_target_id,
                issue_links.created_at AS issue_link_created_at,
                issue_links.updated_at AS issue_link_updated_at FROM "issues" INNER JOIN issue_links ON issue_links.target_id = issues.id WHERE "issue_links"."source_id" IN (164551864, 164474216, 164474118, 164118864, 164118850, 164118823, 164051578, 164051122, 164044814, 163836681, 163836641, 163831038, 163572458, 163564361, 163551930, 163551893, 162732130, 162314309, 162128877, 161963840, 161962420, 161961168, 161805933, 161805928, 161716525, 161695050, 161560415, 161119408, 161119393, 160974539, 160836934, 160836824, 160552349, 160552338, 160350560, 159001737, 159001714, 158852538, 158695956, 158658714, 158029982, 157928937, 157928932, 157928923, 157341800, 157341791, 157341779, 157313978, 156996277, 156920400, 155335302, 155204721, 155143516, 155130594, 155129400, 154971322, 154948903, 154948890, 154948881, 154870923, 154870651, 154869328, 154865774, 154855896, 154854757, 154530829, 154529364, 154493733, 154388478, 154352318, 152623004, 152005864, 151225402) AND "issue_links"."link_type" = 1 ORDER BY "issue_links"."id" DESC;

PLAN: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/37806/commands/115480

Filtered by is_blocked_by link type

WorkItem.linked_items_for(ids_array, link_type: 'is_blocked_by')
query 
SELECT issues.*,
                issue_links.id AS issue_link_id,
                issue_links.link_type AS issue_link_type_value,
                issue_links.target_id AS issue_link_source_id,
                issue_links.source_id AS issue_link_target_id,
                issue_links.created_at AS issue_link_created_at,
                issue_links.updated_at AS issue_link_updated_at FROM "issues" INNER JOIN issue_links ON issue_links.source_id = issues.id WHERE "issue_links"."target_id" IN (164551864, 164474216, 164474118, 164118864, 164118850, 164118823, 164051578, 164051122, 164044814, 163836681, 163836641, 163831038, 163572458, 163564361, 163551930, 163551893, 162732130, 162314309, 162128877, 161963840, 161962420, 161961168, 161805933, 161805928, 161716525, 161695050, 161560415, 161119408, 161119393, 160974539, 160836934, 160836824, 160552349, 160552338, 160350560, 159001737, 159001714, 158852538, 158695956, 158658714, 158029982, 157928937, 157928932, 157928923, 157341800, 157341791, 157341779, 157313978, 156996277, 156920400, 155335302, 155204721, 155143516, 155130594, 155129400, 154971322, 154948903, 154948890, 154948881, 154870923, 154870651, 154869328, 154865774, 154855896, 154854757, 154530829, 154529364, 154493733, 154388478, 154352318, 152623004, 152005864, 151225402) AND "issue_links"."link_type" = 1 ORDER BY "issue_links"."id" DESC;

PLAN: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/37806/commands/115481

References

Fix N+1 queries in work items field linkedItems (#512056 - closed)

Screenshots or screen recordings

No UI changes

How to set up and validate locally

  1. In rails console enable the FF and create work items with linked items:
Click to expand 
Feature.enable(:batch_load_linked_items)

project, author  = Project.last, User.first

item_a, item_b, item_c = %w[item_a item_b item_c].map do |title|
  FactoryBot.create(:work_item, project: project, author: author, title: title)
end

related, blocking, blocked = %w[related_to_a blocking_b blocked_by_c].map do |title|
  FactoryBot.create(:work_item, project: project, author: author, title: title)
end

nested1, nested2, nested3  = %w[nested1 nested2 nested3].map do |title|
  FactoryBot.create(:work_item, project: project, author: author, title: title)
end

FactoryBot.create(:work_item_link, source: item_a, target: related)
FactoryBot.create(:work_item_link, source: blocking, target: item_b, link_type: 'blocks')
FactoryBot.create(:work_item_link, source: item_c, target: blocked, link_type: 'blocks')

FactoryBot.create(:work_item_link, source: related, target: nested1)
FactoryBot.create(:work_item_link, source: nested2, target: blocking)
FactoryBot.create(:work_item_link, source: blocked, target: nested3)

work_item_iids = [item_a, item_b, item_c].pluck(:iid).map(&:to_s)

linked_items_query = <<~QUERY
  query allLinkedWorkItems {
    project(fullPath: "#{project.full_path}") {
      workItems(iids: #{work_item_iids}) {
          edges {
            node {
              title
              widgets(onlyTypes: [LINKED_ITEMS]) {
                ... on WorkItemWidgetLinkedItems {
                  linkedItems {
                    edges {
                      node {
                        linkType
                        workItem {
                          title
                          widgets(onlyTypes: [LINKED_ITEMS]) {  
                            ... on WorkItemWidgetLinkedItems {
                              linkedItems { 
                                nodes {
                                  linkType
                                  workItem { title }
                                }
                              }
                            }
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
    }
  }
QUERY
print(linked_items_query)
  1. Visit http://gdk.test:3000/-/graphql-explorer and run the generated query. Verify the response is correct
expected response 

{
  "data": {
    "project": {
      "workItems": {
        "edges": [
          {
            "node": {
              "title": "item_c",
              "widgets": [
                {
                  "linkedItems": {
                    "edges": [
                      {
                        "node": {
                          "linkType": "blocks",
                          "workItem": {
                            "title": "blocked_by_c",
                            "widgets": [
                              {
                                "linkedItems": {
                                  "nodes": [
                                    {
                                      "linkType": "relates_to",
                                      "workItem": {
                                        "title": "nested3"
                                      }
                                    },
                                    {
                                      "linkType": "is_blocked_by",
                                      "workItem": {
                                        "title": "item_c"
                                      }
                                    }
                                  ]
                                }
                              }
                            ]
                          }
                        }
                      }
                    ]
                  }
                }
              ]
            }
          },
          {
            "node": {
              "title": "item_b",
              "widgets": [
                {
                  "linkedItems": {
                    "edges": [
                      {
                        "node": {
                          "linkType": "is_blocked_by",
                          "workItem": {
                            "title": "blocking_b",
                            "widgets": [
                              {
                                "linkedItems": {
                                  "nodes": [
                                    {
                                      "linkType": "relates_to",
                                      "workItem": {
                                        "title": "nested2"
                                      }
                                    },
                                    {
                                      "linkType": "blocks",
                                      "workItem": {
                                        "title": "item_b"
                                      }
                                    }
                                  ]
                                }
                              }
                            ]
                          }
                        }
                      }
                    ]
                  }
                }
              ]
            }
          },
          {
            "node": {
              "title": "item_a",
              "widgets": [
                {
                  "linkedItems": {
                    "edges": [
                      {
                        "node": {
                          "linkType": "relates_to",
                          "workItem": {
                            "title": "related_to_a",
                            "widgets": [
                              {
                                "linkedItems": {
                                  "nodes": [
                                    {
                                      "linkType": "relates_to",
                                      "workItem": {
                                        "title": "nested1"
                                      }
                                    },
                                    {
                                      "linkType": "relates_to",
                                      "workItem": {
                                        "title": "item_a"
                                      }
                                    }
                                  ]
                                }
                              }
                            ]
                          }
                        }
                      }
                    ]
                  }
                }
              ]
            }
          }
        ]
      }
    }
  }
}
screen recording

Screen_Recording_2025-04-28_at_15.15.06

  1. Check log/development.log to verify no N+1s are present for issue_links table
  2. Disable the FF Feature.disable(:batch_load_linked_items) and repeat the request to verify that the response doesn't change.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Eugenia Grieff

Merge request reports