Skip to content

Improve .groups_user_can_read_epics performance

Adam Hegyi requested to merge 37368-optimize-epic-issue-groups-query into master

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"

Plan

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"

Plan

Conformity

Related to #37368 (closed)

Edited by 🤖 GitLab Bot 🤖

Merge request reports