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
Edited by Mario Celi