SQL query time out in work item list

We are getting timeouts in the group level work item list such as https://gitlab.com/groups/gitlab-org/plan-stage/-/work_items (behind namespace_level_work_items feature flag)

Related log entry https://log.gprd.gitlab.net/app/discover#/doc/97f04200-024b-11eb-81e5-155ba78758d4/pubsub-postgres-inf-gprd-000496?id=SZo6J5ABqAvZkerA_rb5 (internal only)

SQL query that timeouts

SELECT 
  "issues"."id", 
  "issues"."title", 
  "issues"."author_id", 
  "issues"."project_id", 
  "issues"."created_at", 
  "issues"."updated_at", 
  "issues"."description", 
  "issues"."milestone_id", 
  "issues"."iid", 
  "issues"."updated_by_id", 
  "issues"."weight", 
  "issues"."confidential", 
  "issues"."moved_to_id", 
  "issues"."due_date", 
  "issues"."lock_version", 
  "issues"."title_html", 
  "issues"."description_html", 
  "issues"."time_estimate", 
  "issues"."relative_position", 
  "issues"."service_desk_reply_to", 
  "issues"."cached_markdown_version", 
  "issues"."last_edited_at", 
  "issues"."last_edited_by_id", 
  "issues"."discussion_locked", 
  "issues"."closed_at", 
  "issues"."closed_by_id", 
  "issues"."state_id", 
  "issues"."duplicated_to_id", 
  "issues"."promoted_to_epic_id", 
  "issues"."health_status", 
  "issues"."external_key", 
  "issues"."sprint_id", 
  "issues"."blocking_issues_count", 
  "issues"."upvotes_count", 
  "issues"."work_item_type_id", 
  "issues"."namespace_id", 
  "issues"."start_date", 
  "issues"."imported_from" 
FROM 
  "issues" 
WHERE 
  (
    NOT EXISTS (
      SELECT 
        $1 
      FROM 
        "banned_users" 
      WHERE 
        (
          issues.author_id = banned_users.user_id
        )
    )
  ) 
  AND "issues"."namespace_id" IN (
    SELECT 
      "namespaces"."id" 
    FROM 
      (
        (
          SELECT 
            "namespaces"."id" 
          FROM 
            "namespaces" 
          WHERE 
            "namespaces"."type" = $2 
            AND (
              traversal_ids @ > ($3)
            )
        ) 
        UNION 
          (
            SELECT 
              "projects"."project_namespace_id" 
            FROM 
              "projects" 
              LEFT JOIN project_features ON projects.id = project_features.project_id 
            WHERE 
              "projects"."namespace_id" IN (
                SELECT 
                  namespaces.traversal_ids[array_length(namespaces.traversal_ids, $4) ] AS id 
                FROM 
                  "namespaces" 
                WHERE 
                  "namespaces"."type" = $5 
                  AND (
                    traversal_ids @ > ($6)
                  )
              ) 
              AND (
                EXISTS (
                  SELECT 
                    $7 
                  FROM 
                    "project_authorizations" 
                  WHERE 
                    "project_authorizations"."user_id" = $8 
                    AND (
                      project_authorizations.project_id = projects.id
                    ) 
                    AND (
                      project_authorizations.access_level >= $9
                    )
                ) 
                OR projects.visibility_level IN ($10, $11)
              ) 
              AND (
                "project_features"."issues_access_level" IS NULL 
                OR "project_features"."issues_access_level" IN ($12, $13) 
                OR (
                  "project_features"."issues_access_level" = $14 
                  AND EXISTS (
                    SELECT 
                      $15 
                    FROM 
                      "project_authorizations" 
                    WHERE 
                      "project_authorizations"."user_id" = $16 
                      AND (
                        project_authorizations.project_id = project_features.project_id
                      ) 
                      AND (
                        project_authorizations.access_level >= $17
                      )
                  )
                )
              )
          )
      ) namespaces
  ) 
  AND "issues"."state_id" = $18 
ORDER BY 
  "issues"."updated_at" DESC, 
  "issues"."id" DESC 
LIMIT 
  $19

GraphQL query

query getWorkItems($fullPath: ID!, $search: String, $sort: WorkItemSort, $state: IssuableState, $authorUsername: String, $in: [IssuableSearchableField!], $afterCursor: String, $beforeCursor: String, $firstPageSize: Int, $lastPageSize: Int, $types: [IssueType!] = null) {
  group(fullPath: $fullPath) {
    id
    workItemStateCounts(
      includeDescendants: true
      sort: $sort
      state: $state
      types: $types
    ) {
      all
      closed
      opened
      __typename
    }
    workItems(
      includeDescendants: true
      search: $search
      sort: $sort
      state: $state
      authorUsername: $authorUsername
      in: $in
      after: $afterCursor
      before: $beforeCursor
      first: $firstPageSize
      last: $lastPageSize
      types: $types
    ) {
      pageInfo {
        ...PageInfo
        __typename
      }
      nodes {
        id
        author {
          id
          avatarUrl
          name
          username
          webUrl
          webPath
          __typename
        }
        closedAt
        confidential
        createdAt
        iid
        reference(full: true)
        state
        title
        updatedAt
        webUrl
        widgets {
          ...WorkItemWidgets
          __typename
        }
        workItemType {
          id
          name
          __typename
        }
        __typename
      }
      __typename
    }
    __typename
  }
}

fragment PageInfo on PageInfo {
  hasNextPage
  hasPreviousPage
  startCursor
  endCursor
  __typename
}

fragment WorkItemWidgets on WorkItemWidget {
  ...BaseWorkItemWidgets
  ... on WorkItemWidgetHealthStatus {
    type
    healthStatus
    __typename
  }
  ... on WorkItemWidgetWeight {
    type
    weight
    __typename
  }
  __typename
}

fragment BaseWorkItemWidgets on WorkItemWidget {
  ... on WorkItemWidgetAssignees {
    type
    assignees {
      nodes {
        ...User
        __typename
      }
      __typename
    }
    __typename
  }
  ... on WorkItemWidgetLabels {
    type
    allowsScopedLabels
    labels {
      nodes {
        id
        color
        description
        title
        __typename
      }
      __typename
    }
    __typename
  }
  ... on WorkItemWidgetMilestone {
    type
    milestone {
      id
      dueDate
      startDate
      title
      webPath
      __typename
    }
    __typename
  }
  ... on WorkItemWidgetStartAndDueDate {
    type
    dueDate
    startDate
    __typename
  }
  __typename
}

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

Request variables

{"types"=>[], "fullPath"=>"gitlab-org/plan-stage", "sort"=>"UPDATED_DESC", "state"=>"opened", "firstPageSize"=>20}

Query plans

Sorted by updated_at

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/29205/commands/90889

Sorted by created_at

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/29205/commands/90890