Skip to content
Snippets Groups Projects

Replace Group ancestors scope with linear version

All threads resolved!

What does this MR do and why?

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

Database changes

In these examples, we're passing 6k groups to the query to test it with heavy data.

The old query was:

SELECT "id"
FROM
  (SELECT *
   FROM namespaces) AS namespaces_with_emails_disabled
WHERE "namespaces_with_emails_disabled"."id" IN
    (SELECT "namespaces"."id"
     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" = 1614863
       AND "members"."requested_at" IS NULL
       AND (access_level >= 10))
  AND (EXISTS
         (WITH RECURSIVE "base_and_ancestors" AS (
                                                    (SELECT "namespaces".*
                                                     FROM "namespaces"
                                                     WHERE "namespaces"."type" = 'Group'
                                                       AND (id = namespaces_with_emails_disabled.id))
                                                  UNION
                                                    (SELECT "namespaces".*
                                                     FROM "namespaces",
                                                          "base_and_ancestors"
                                                     WHERE "namespaces"."type" = 'Group'
                                                       AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT 1
          FROM "base_and_ancestors" AS "namespaces"
          WHERE "namespaces"."emails_disabled" = TRUE
          LIMIT 1))

This is the query plan and the execution times:

Time: 208.663 ms
  - planning: 1.798 ms
  - execution: 206.865 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 90505 (~707.10 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 "id"
FROM
  (SELECT *
   FROM namespaces) AS namespaces_with_emails_disabled
WHERE "namespaces_with_emails_disabled"."id" IN
    (SELECT "namespaces"."id"
     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" = 1614863
       AND "members"."requested_at" IS NULL
       AND (access_level >= 10))
  AND (EXISTS
         (SELECT 1
          FROM
            (SELECT "namespaces".*
             FROM "namespaces"
             WHERE "namespaces"."id" IN
                 (SELECT unnest(traversal_ids)
                  FROM "namespaces"
                  WHERE (id = namespaces_with_emails_disabled.id))) namespaces
          WHERE "namespaces"."emails_disabled" = TRUE
          LIMIT 1))

This is the query plan and the execution times:

Time: 117.777 ms
  - planning: 1.419 ms
  - execution: 116.358 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 115075 (~899.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

Example below:

  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 Ci::Runner linear ancestors scopes

    Feature.enable(:linear_group_ancestor_scopes)
  4. Execute the following in the Rails console with the proper id depending on the scope testing:

Group.ids_with_disabled_email(User.first.groups)

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 #339194 (closed)

Edited by Francisco Javier López

Merge request reports

Loading
Loading

Activity

Filter activity
  • Approvals
  • Assignees & reviewers
  • Comments (from bots)
  • Comments (from users)
  • Commits & branches
  • Edits
  • Labels
  • Lock status
  • Mentions
  • Merge request status
  • Tracking
Please register or sign in to reply
Loading