Add API endpoint to list group descendants (!42620) · Merge requests · GitLab.org / GitLab

Eugenia Grieff requested to merge 217115-assign-epic-to-a-group-when-creating-within-another-epic into master Sep 17, 2020

What does this MR do?

Related to #217115 (closed)

These changes introduce a new REST API endpoint for groups (/api/v4/groups/:id/descendant_groups) that will fetch the list of descendant groups for a given group. Similarly to the endpoint /api/v4/groups/:id/subgroups, the parent is not included in the list.

Query changes for `GroupsFinder`

When the param parent is present in GroupsFinder and we set include_parent_descendants as true the finder will execute a different query when filtering by_parent.

Query without group descendants

finder_params_without_descendants = {owned: false, include_parent_descendants: false, parent: group, all_available: true}
GroupsFinder.new(current_user, finder_params_without_descendants).execute

SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = 9970 ORDER BY "namespaces"."id" DESC

Query with group descendants

finder_params_with_descendants = {owned: false, include_parent_descendants: true, parent: group, all_available: true}
GroupsFinder.new(current_user, finder_params_with_descendants).execute

Query

SELECT
    "namespaces".*
FROM
    "namespaces"
WHERE
    "namespaces"."type" = 'Group'
    AND "namespaces"."id" IN ( WITH RECURSIVE "base_and_descendants" AS (
(
                SELECT
                    "namespaces".*
                FROM
                    "namespaces"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."parent_id" = 9970)
            UNION (
                SELECT
                    "namespaces".*
                FROM
                    "namespaces",
                    "base_and_descendants"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."parent_id" = "base_and_descendants"."id"))
            SELECT
                "namespaces"."id"
            FROM
                "base_and_descendants" AS "namespaces")
    ORDER BY
        "namespaces"."id" DESC

Query Plan

Sort  (cost=1956.61..1956.68 rows=29 width=346) (actual time=838.250..838.275 rows=180 loops=1)
   Sort Key: namespaces.id DESC
   Sort Method: quicksort  Memory: 108kB
   Buffers: shared hit=950 read=499 dirtied=7
   I/O Timings: read=818.740
   ->  Nested Loop  (cost=1433.13..1955.91 rows=29 width=346) (actual time=549.585..837.452 rows=180 loops=1)
         Buffers: shared hit=947 read=499 dirtied=7
         I/O Timings: read=818.740
         ->  HashAggregate  (cost=1432.69..1434.20 rows=151 width=4) (actual time=544.966..545.246 rows=180 loops=1)
               Group Key: namespaces_1.id
               Buffers: shared hit=418 read=308 dirtied=7
               I/O Timings: read=530.993
               ->  CTE Scan on base_and_descendants namespaces_1  (cost=1427.78..1430.80 rows=151 width=4) (actual time=6.708..544.638 rows=180 loops=1)
                     Buffers: shared hit=418 read=308 dirtied=7
                     I/O Timings: read=530.993
                     CTE base_and_descendants
                       ->  Recursive Union  (cost=0.43..1427.78 rows=151 width=346) (actual time=6.703..543.808 rows=180 loops=1)
                             Buffers: shared hit=418 read=308 dirtied=7
                             I/O Timings: read=530.993
                             ->  Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2  (cost=0.43..14.07 rows=1 width=346) (actual time=6.676..112.002 rows=43 loops=1)
                                   Index Cond: (namespaces_2.parent_id = 9970)
                                   Filter: ((namespaces_2.type)::text = 'Group'::text)
                                   Rows Removed by Filter: 0
                                   Buffers: shared read=46
                                   I/O Timings: read=111.380
                             ->  Nested Loop  (cost=0.43..141.07 rows=15 width=346) (actual time=5.992..107.493 rows=34 loops=4)
                                   Buffers: shared hit=418 read=262 dirtied=7
                                   I/O Timings: read=419.613
                                   ->  WorkTable Scan on base_and_descendants  (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.032 rows=45 loops=4)
                                   ->  Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_3  (cost=0.43..14.07 rows=2 width=346) (actual time=1.491..2.384 rows=1 loops=180)
                                         Index Cond: (namespaces_3.parent_id = base_and_descendants.id)
                                         Filter: ((namespaces_3.type)::text = 'Group'::text)
                                         Rows Removed by Filter: 0
                                         Buffers: shared hit=418 read=262 dirtied=7
                                         I/O Timings: read=419.613
         ->  Index Scan using namespaces_pkey on public.namespaces  (cost=0.43..3.46 rows=1 width=346) (actual time=1.620..1.620 rows=1 loops=180)
               Index Cond: (namespaces.id = namespaces_1.id)
               Filter: ((namespaces.type)::text = 'Group'::text)
               Rows Removed by Filter: 0
               Buffers: shared hit=529 read=191
               I/O Timings: read=287.748

Summary

Time: 839.877 ms
  - planning: 1.240 ms
  - execution: 838.637 ms
    - I/O read: 818.740 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 950 (~7.40 MiB) from the buffer pool
  - reads: 499 (~3.90 MiB) from the OS file cache, including disk I/O
  - dirtied: 7 (~56.00 KiB)
  - writes: 0

Link to database-lab

With extra params (without group descendants)

finder_params = {include_parent_descendants: false, parent: parent_group, with_custom_attributes: true, min_access_level: 40, owned: true, statistics: true}
GroupsFinder.new(current_user, finder_params).execute

Query

SELECT "namespaces".*
FROM "namespaces"
INNER JOIN "members"
    ON "namespaces"."id" = "members"."source_id"
WHERE "members"."type" = 'GroupMember'
        AND "members"."source_type" = 'Namespace'
        AND "namespaces"."type" = 'Group'
        AND "members"."user_id" = 1
        AND "members"."requested_at" IS NULL
        AND (access_level >= 10)
        AND "members"."access_level" = 50
        AND "namespaces"."parent_id" = 9970
ORDER BY  "namespaces"."id" DESC

With extra params (with group descendants)

finder_params = {include_parent_descendants: true, parent: parent_group, with_custom_attributes: true, min_access_level: 40, owned: true, statistics: true}
GroupsFinder.new(current_user, finder_params).execute

Query

SELECT
    "namespaces".*
FROM
    "namespaces"
    INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
WHERE
    "members"."type" = 'GroupMember'
    AND "members"."source_type" = 'Namespace'
    AND "namespaces"."type" = 'Group'
    AND "members"."user_id" = 1
    AND "members"."requested_at" IS NULL
    AND (access_level >= 10)
    AND "members"."access_level" = 50
    AND "namespaces"."id" IN ( WITH RECURSIVE "base_and_descendants" AS (
(
                SELECT
                    "namespaces".*
                FROM
                    "namespaces"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."parent_id" = 9970)
            UNION (
                SELECT
                    "namespaces".*
                FROM
                    "namespaces",
                    "base_and_descendants"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."parent_id" = "base_and_descendants"."id"))
            SELECT
                "namespaces"."id"
            FROM
                "base_and_descendants" AS "namespaces")
    ORDER BY
        "namespaces"."id" DESC

Query Plan

 Sort  (cost=1456.79..1456.80 rows=1 width=346) (actual time=516.738..516.738 rows=0 loops=1)
   Sort Key: namespaces.id DESC
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=755 read=199 dirtied=2
   I/O Timings: read=505.710
   ->  Nested Loop Semi Join  (cost=1428.66..1456.78 rows=1 width=346) (actual time=516.729..516.729 rows=0 loops=1)
         Buffers: shared hit=755 read=199 dirtied=2
         I/O Timings: read=505.710
         ->  Nested Loop  (cost=0.87..22.58 rows=1 width=350) (actual time=23.541..511.095 rows=19 loops=1)
               Buffers: shared hit=33 read=199 dirtied=2
               I/O Timings: read=505.710
               ->  Index Scan using index_members_on_user_id on public.members  (cost=0.44..19.13 rows=1 width=4) (actual time=16.854..387.120 rows=19 loops=1)
                     Index Cond: (members.user_id = 1)
                     Filter: ((members.requested_at IS NULL) AND (members.access_level >= 10) AND ((members.type)::text = 'GroupMember'::text) AND ((members.source_type)::text = 'Namespace'::text) AND (members.access_level = 50))
                     Rows Removed by Filter: 142
                     Buffers: shared read=156 dirtied=2
                     I/O Timings: read=382.710
               ->  Index Scan using namespaces_pkey on public.namespaces  (cost=0.43..3.46 rows=1 width=346) (actual time=6.514..6.514 rows=1 loops=19)
                     Index Cond: (namespaces.id = members.source_id)
                     Filter: ((namespaces.type)::text = 'Group'::text)
                     Rows Removed by Filter: 0
                     Buffers: shared hit=33 read=43
                     I/O Timings: read=123.000
         ->  CTE Scan on base_and_descendants namespaces_1  (cost=1427.78..1430.80 rows=151 width=4) (actual time=0.009..0.256 rows=180 loops=19)
               Buffers: shared hit=722
               CTE base_and_descendants
                 ->  Recursive Union  (cost=0.43..1427.78 rows=151 width=346) (actual time=0.090..2.450 rows=180 loops=1)
                       Buffers: shared hit=722
                       ->  Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2  (cost=0.43..14.07 rows=1 width=346) (actual time=0.037..0.181 rows=43 loops=1)
                             Index Cond: (namespaces_2.parent_id = 9970)
                             Filter: ((namespaces_2.type)::text = 'Group'::text)
                             Rows Removed by Filter: 0
                             Buffers: shared hit=46
                       ->  Nested Loop  (cost=0.43..141.07 rows=15 width=346) (actual time=0.037..0.386 rows=34 loops=4)
                             Buffers: shared hit=676
                             ->  WorkTable Scan on base_and_descendants  (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.014 rows=45 loops=4)
                             ->  Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_3  (cost=0.43..14.07 rows=2 width=346) (actual time=0.006..0.007 rows=1 loops=180)
                                   Index Cond: (namespaces_3.parent_id = base_and_descendants.id)
                                   Filter: ((namespaces_3.type)::text = 'Group'::text)
                                   Rows Removed by Filter: 0
                                   Buffers: shared hit=676

Summary

Time: 518.873 ms
  - planning: 1.805 ms
  - execution: 517.068 ms
    - I/O read: 505.710 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 755 (~5.90 MiB) from the buffer pool
  - reads: 199 (~1.60 MiB) from the OS file cache, including disk I/O
  - dirtied: 2 (~16.00 KiB)
  - writes: 0

Link to database-lab

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers
Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

Edited Sep 29, 2020 by Eugenia Grieff

Add API endpoint to list group descendants

What does this MR do?

Query changes for GroupsFinder

Query without group descendants

Query with group descendants

With extra params (without group descendants)

With extra params (with group descendants)

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

Merge request reports

Query changes for `GroupsFinder`