Skip to content

Use GroupPlansPreloader linear ancestor scopes

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

What does this MR do and why?

In this MR, we're switching the behavior of the method GroupPlansPreloader#groups_and_ancestors_for to use the linear version. The new behavior is behind the linear_group_plans_preloaded_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:

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",
       "namespaces"."parent_id",
       "gitlab_subscriptions"."hosted_plan_id"
FROM "base_and_ancestors" AS "namespaces"
LEFT OUTER JOIN gitlab_subscriptions ON gitlab_subscriptions.namespace_id=namespaces.id

This is the query plan and the times are:

Time: 6.501 ms
  - planning: 2.708 ms
  - execution: 3.793 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 740 (~5.80 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 "namespaces"."id",
       "namespaces"."parent_id",
       "gitlab_subscriptions"."hosted_plan_id"
FROM
  (SELECT "namespaces".*
   FROM "namespaces"
   WHERE "namespaces"."id" IN
       (SELECT unnest(traversal_ids)
        FROM "namespaces"
        WHERE "namespaces"."id" IN (1,2))) namespaces
LEFT OUTER JOIN gitlab_subscriptions ON gitlab_subscriptions.namespace_id=namespaces.id

This is the query plan and the times are:

Time: 2.699 ms
  - planning: 0.815 ms
  - execution: 1.884 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 763 (~6.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 GroupPlansPreloader linear ancestors scopes

    Feature.enable(:linear_group_plans_preloaded_ancestor_scopes)

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

Gitlab::GroupPlansPreloader.new.send(:groups_and_ancestors_for, 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 #339458 (closed)

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

Merge request reports