Improve work item query when sorting by updated_at
What does this MR do and why?
Using a CTE in the work items finder to provide stability in the query as the planner is sometimes confused as described in https://docs.gitlab.com/ee/development/sql.html#query-plan-flip-problem-with-exists-queries
In !141973 (merged) UNION ALL was changed with a UNION as the planner was also getting confused. This change should fix that too and we can add back UNION ALL so we don't have to remove duplicates.
DB review
Sorting by updated_at
Before
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/29205/commands/90889
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"."due_date",
"issues"."moved_to_id",
"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
"issues"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
(
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids @> ('{10510295}')
)
)
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, 1) ] AS id
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids @> ('{10510295}')
)
)
AND (
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 2890431
AND (
project_authorizations.project_id = projects.id
)
)
OR projects.visibility_level IN (0, 10, 20)
)
AND (
"project_features"."issues_access_level" > 0
OR "project_features"."issues_access_level" IS NULL
)
)
) namespaces
)
AND "issues"."state_id" = 1
ORDER BY
"issues"."updated_at" DESC,
"issues"."id" DESC
LIMIT
21After
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/29345/commands/91331
WITH "namespace_ids" AS MATERIALIZED (
SELECT
"namespaces"."id"
FROM
(
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids @> ('{10510295}')
)
)
UNION ALL
(
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, 1) ] AS id
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids @> ('{10510295}')
)
)
AND (
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 2890431
AND (
project_authorizations.project_id = projects.id
)
)
OR projects.visibility_level IN (0, 10, 20)
)
AND (
"project_features"."issues_access_level" > 0
OR "project_features"."issues_access_level" IS NULL
)
)
) namespaces
)
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"."due_date",
"issues"."moved_to_id",
"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
(
namespace_id IN (
SELECT
id
FROM
namespace_ids
)
)
AND "issues"."state_id" = 1
ORDER BY
"issues"."updated_at" DESC,
"issues"."id" DESC
LIMIT
21Checking for existence
This query was used in https://gitlab.com/gitlab-org/gitlab/-/blob/eecabd7527c82059da7c83f89199a7b060b51a62/app/controllers/groups_controller.rb#L236 when it was causing timeouts and was fixed in !141973 (merged). Since the hierarchy and UNION logic was moved to the work items finder in !145181 (merged), this query is no longer executed, but we can see here that with the CTE using UNION ALL is no longer a problem.
Before
Using UNION
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/25418/commands/80510
SELECT 1 AS one
FROM "issues"
LEFT OUTER JOIN "projects" ON "projects"."id" = "issues"."project_id"
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" = 'Group' AND "namespaces"."id" = 10510295)
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, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{10510295}'))) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 2880930 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 10)) OR projects.visibility_level IN (10,20)) AND ("project_features"."issues_access_level" IS NULL OR "project_features"."issues_access_level" IN (20,30) OR ("project_features"."issues_access_level" = 10 AND EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 2880930 AND (project_authorizations.project_id = project_features.project_id) AND (project_authorizations.access_level >= 10)))))
) namespaces)
AND ("issues"."project_id" IS NULL OR "projects"."archived" = FALSE)
LIMIT 1
Using UNION ALL
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/25418/commands/80532
SELECT 1 AS one
FROM "issues"
LEFT OUTER JOIN "projects" ON "projects"."id" = "issues"."project_id"
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" = 'Group' AND "namespaces"."id" = 10510295)
UNION ALL
(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, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{10510295}'))) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 2880930 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 10)) OR projects.visibility_level IN (10,20)) AND ("project_features"."issues_access_level" IS NULL OR "project_features"."issues_access_level" IN (20,30) OR ("project_features"."issues_access_level" = 10 AND EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 2880930 AND (project_authorizations.project_id = project_features.project_id) AND (project_authorizations.access_level >= 10)))))
) namespaces)
AND ("issues"."project_id" IS NULL OR "projects"."archived" = FALSE)
LIMIT 1After
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/29345/commands/91333
WITH "namespace_ids" AS MATERIALIZED (
SELECT
"namespaces"."id"
FROM
(
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids @> ('{10510295}')
)
)
UNION ALL
(
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, 1) ] AS id
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids @> ('{10510295}')
)
)
AND (
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 2890431
AND (
project_authorizations.project_id = projects.id
)
)
OR projects.visibility_level IN (0, 10, 20)
)
AND (
"project_features"."issues_access_level" > 0
OR "project_features"."issues_access_level" IS NULL
)
)
) namespaces
)
SELECT
1 AS one
FROM
"issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE
"projects"."namespace_id" IN (
SELECT
id
FROM
namespace_ids
)
AND (
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 1
AND (
project_authorizations.project_id = projects.id
)
)
OR projects.visibility_level IN (0, 10, 20)
)
AND (
"project_features"."issues_access_level" > 0
OR "project_features"."issues_access_level" IS NULL
)
AND (
"issues"."project_id" IS NULL
OR "projects"."archived" = FALSE
)
LIMIT
1MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Related to #467794 (closed)
Edited by Mario Celi