Improve .groups_user_can_read_epics performance
What does this MR do?
This MR changes the original query a bit by moving the epics lookup into INNER JOIN
. The improvement is behind a feature flag: optimized_groups_user_can_read_epics_method
.
Original Query:
WITH recursive "base_and_ancestors" AS (
(
SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."id" IN (WITH recursive "base_and_descendants" AS (
(
SELECT "epics".*
FROM "epics"
WHERE "epics"."id" = 15413)
UNION
(
SELECT "epics".*
FROM "epics",
"base_and_descendants"
WHERE "epics"."parent_id" = "base_and_descendants"."id"))
SELECT "group_id"
FROM "base_and_descendants" AS "epics"))
UNION
(
SELECT "namespaces".*
FROM "namespaces",
"base_and_ancestors"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."id" = "base_and_ancestors"."parent_id"))
SELECT "id",
"parent_id",
"plan_id"
FROM "base_and_ancestors" AS "namespaces"
New Query:
WITH recursive "base_and_ancestors" AS (
(
SELECT "namespaces".*
FROM "namespaces"
INNER JOIN (WITH recursive "base_and_descendants" AS (
(
SELECT "epics".*
FROM "epics"
WHERE "epics"."id" = 15413)
UNION
(
SELECT "epics".*
FROM "epics",
"base_and_descendants"
WHERE "epics"."parent_id" = "base_and_descendants"."id"))
SELECT "group_id"
FROM "base_and_descendants" AS "epics") AS epics
ON epics.group_id = namespaces.id
WHERE "namespaces"."type" IN ('Group'))
UNION
(
SELECT "namespaces".*
FROM "namespaces",
"base_and_ancestors"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."id" = "base_and_ancestors"."parent_id"))
SELECT "id",
"parent_id",
"plan_id"
FROM "base_and_ancestors" AS "namespaces"
Conformity
-
Changelog entry -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Related to #37368 (closed)
Edited by 🤖 GitLab Bot 🤖