Improve performance of work item children list

We're having a few N+1 issues in our Hierarchy widget when loading children and additional data. This leads to us being above the error budget in Product Planning, since the workItemTreeQuery often takes >2 seconds to load.

In the tasks you can find a few queries that are already identified to have N+1 issues, but there are more than that. If you find another N+1 problem, add it as a task to this issue.

workItemTreeQuery

Query to test

query workItemTreeQuery($id: WorkItemID!, $pageSize: Int = 100, $endCursor: String) {
  workItem(id: $id) {
    namespace {
      id
      fullName
      __typename
    }
    ...WorkItemHierarchy
    __typename
  }
}

fragment WorkItemHierarchy on WorkItem {
  id
  workItemType {
    id
    name
    iconName
    __typename
  }
  title
  confidential
  userPermissions {
    deleteWorkItem
    updateWorkItem
    adminParentLink
    setWorkItemMetadata
    createNote
    adminWorkItemLink
    __typename
  }
  widgets {
    type
    ... on WorkItemWidgetHierarchy {
      type
      hasChildren
      depthLimitReachedByType {
        workItemType {
          id
          name
          __typename
        }
        depthLimitReached
        __typename
      }
      rolledUpCountsByType {
        countsByState {
          opened
          all
          closed
          __typename
        }
        workItemType {
          id
          name
          iconName
          __typename
        }
        __typename
      }
      parent {
        id
        __typename
      }
      children(first: $pageSize, after: $endCursor) {
        pageInfo {
          ...PageInfo
          __typename
        }
        count
        nodes {
          id
          iid
          confidential
          workItemType {
            id
            name
            iconName
            __typename
          }
          namespace {
            id
            fullPath
            name
            __typename
          }
          title
          state
          createdAt
          closedAt
          webUrl
          reference(full: true)
          widgets {
            ... on WorkItemWidgetHierarchy {
              type
              hasChildren
              rolledUpCountsByType {
                countsByState {
                  all
                  closed
                  __typename
                }
                workItemType {
                  id
                  name
                  iconName
                  __typename
                }
                __typename
              }
              __typename
            }
            ...WorkItemMetadataWidgets
            __typename
          }
          __typename
        }
        __typename
      }
      __typename
    }
    ...WorkItemMetadataWidgets
    __typename
  }
  __typename
}

fragment PageInfo on PageInfo {
  hasNextPage
  hasPreviousPage
  startCursor
  endCursor
  __typename
}

fragment WorkItemMetadataWidgets on WorkItemWidget {
  type
  ... on WorkItemWidgetStartAndDueDate {
    dueDate
    startDate
    __typename
  }
  ... on WorkItemWidgetWeight {
    weight
    rolledUpWeight
    widgetDefinition {
      editable
      rollUp
      __typename
    }
    __typename
  }
  ... on WorkItemWidgetProgress {
    progress
    updatedAt
    __typename
  }
  ... on WorkItemWidgetHealthStatus {
    healthStatus
    rolledUpHealthStatus {
      count
      healthStatus
      __typename
    }
    __typename
  }
  ... on WorkItemWidgetMilestone {
    milestone {
      ...MilestoneFragment
      __typename
    }
    __typename
  }
  ... on WorkItemWidgetAssignees {
    allowsMultipleAssignees
    canInviteMembers
    assignees {
      nodes {
        ...User
        __typename
      }
      __typename
    }
    __typename
  }
  ... on WorkItemWidgetLabels {
    allowsScopedLabels
    labels {
      nodes {
        ...Label
        __typename
      }
      __typename
    }
    __typename
  }
  ... on WorkItemWidgetLinkedItems {
    linkedItems {
      nodes {
        linkId
        linkType
        __typename
      }
      __typename
    }
    __typename
  }
  __typename
}

fragment Label on Label {
  id
  title
  description
  color
  textColor
  __typename
}

fragment User on User {
  id
  avatarUrl
  name
  username
  webUrl
  webPath
  __typename
}

fragment MilestoneFragment on Milestone {
  expired
  id
  title
  state
  startDate
  dueDate
  webPath
  __typename
}

Variables

{
  "pageSize": 50,
  "id": "gid://gitlab/WorkItem/144139838",
  "endCursor": ""
}