Skip to content

Use Group linear ancestor scopes

Francisco Javier López (ex-Gitlab) requested to merge 339235-fj-use-group-linear-ancestor-scopes into master

What does this MR do and why?

In this MR, we're switching the behavior of the method Group#invited_or_shared_group_members to use the linear version. The new behavior is behind the linear_ee_group_ancestor_scopes feature flag.

Database queries

The queries have been tested using the most expensive groups with 21 parents (5 groups at the moment).

The original query was:

SELECT "members".*
FROM "members"
LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id"
WHERE "members"."type" = 'GroupMember'
  AND "members"."source_type" = 'Namespace'
  AND "users"."state" = 'active'
  AND "members"."requested_at" IS NULL
  AND "members"."invite_token" IS NULL
  AND (members.access_level > 5)
  AND "members"."source_id" IN
    (WITH RECURSIVE "base_and_ancestors" AS (
                                               (SELECT "namespaces".*
                                                FROM "namespaces"
                                                WHERE "namespaces"."type" = 'Group'
                                                  AND "namespaces"."id" IN (1,
                                                                            2))
                                             UNION
                                               (SELECT "namespaces".*
                                                FROM "namespaces",
                                                     "base_and_ancestors"
                                                WHERE "namespaces"."type" = 'Group'
                                                  AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT "namespaces"."id"
     FROM "base_and_ancestors" AS "namespaces")

This is the query plan and the times are:

Time: 6.817 ms
  - planning: 2.285 ms
  - execution: 4.532 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1410 (~11.00 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

The new query is:

SELECT "members".*
FROM "members"
LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id"
WHERE "members"."type" = 'GroupMember'
  AND "members"."source_type" = 'Namespace'
  AND "users"."state" = 'active'
  AND "members"."requested_at" IS NULL
  AND "members"."invite_token" IS NULL
  AND (members.access_level > 5)
  AND "members"."source_id" IN
    (SELECT "namespaces"."id"
     FROM
       (SELECT "namespaces".*
        FROM "namespaces"
        WHERE "namespaces"."id" IN
            (SELECT unnest(traversal_ids)
             FROM "namespaces"
             WHERE "namespaces"."id" IN (1,2))) namespaces)

This is the query plan and the times are:

Time: 3.819 ms
  - planning: 1.634 ms
  - execution: 2.185 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1407 (~11.00 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

How to set up and validate locally

  1. Enable the feature flag for linear scopes

    Feature.enable(:use_traversal_ids)

  2. Enable the feature flag for linear ancestors scopes

    Feature.enable(:use_traversal_ids_for_ancestor_scopes)

  3. Enable the feature flag for Group linear ancestors scopes

    Feature.enable(:linear_ee_group_ancestor_scopes)

  4. Execute the following in the Rails console with the proper id depending on the scope testing:

Group.new.send(:invited_or_shared_group_members, Group.where(id: [1, 2]))

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #339235 (closed)

Edited by Francisco Javier López (ex-Gitlab)

Merge request reports