Skip to content

Use linear version GroupsWithTemplatesFinder#extended_group_search

What does this MR do?

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

How to setup and validate locally (strongly suggested)

  1. Enable the new method behavior feature flag
    Feature.enable(:linear_groups_template_finder_extended_group_search)
  2. In rails console enable the traversal id feature flag
    Feature.enable(:use_traversal_ids)

SQL Queries

The former sql query was:

WITH RECURSIVE base_and_descendants AS (
                                          (WITH RECURSIVE base_and_ancestors AS (
                                                                                   (SELECT namespaces.*
                                                                                    FROM namespaces
                                                                                    WHERE namespaces.type = 'Group'
                                                                                      AND namespaces.custom_project_templates_group_id IS NOT NULL)
                                                                                 UNION
                                                                                   (SELECT namespaces.*
                                                                                    FROM namespaces,
                                                                                         base_and_ancestors
                                                                                    WHERE namespaces.type = 'Group'
                                                                                      AND namespaces.id = base_and_ancestors.parent_id)) SELECT namespaces.*
                                           FROM base_and_ancestors AS namespaces
                                           WHERE (EXISTS
                                                    (SELECT 1
                                                     FROM plans
                                                     INNER JOIN gitlab_subscriptions ON gitlab_subscriptions.hosted_plan_id = plans.id
                                                     WHERE plans.name IN ('silver',
                                                                          'premium',
                                                                          'premium_trial',
                                                                          'gold',
                                                                          'ultimate',
                                                                          'ultimate_trial')
                                                       AND (gitlab_subscriptions.namespace_id = namespaces.id))))
                                        UNION
                                          (SELECT namespaces.*
                                           FROM namespaces,
                                                base_and_descendants
                                           WHERE namespaces.type = 'Group'
                                             AND namespaces.parent_id = base_and_descendants.id))
SELECT namespaces.*
FROM base_and_descendants AS namespaces

This is the query plan and the time summary:

Time: 47.530 s
  - planning: 7.751 ms
  - execution: 47.522 s
    - I/O read: 45.591 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 275198 (~2.10 GiB) from the buffer pool
  - reads: 40102 (~313.30 MiB) from the OS file cache, including disk I/O
  - dirtied: 969 (~7.60 MiB)
  - writes: 0

The new sql query is:

SELECT namespaces.*
FROM
  (SELECT DISTINCT on(namespaces.id) namespaces.*
   FROM namespaces,

     (WITH RECURSIVE base_and_ancestors AS (
                                              (SELECT namespaces.*
                                               FROM namespaces
                                               WHERE namespaces.type = 'Group'
                                                 AND namespaces.custom_project_templates_group_id IS NOT NULL)
                                            UNION
                                              (SELECT namespaces.*
                                               FROM namespaces,
                                                    base_and_ancestors
                                               WHERE namespaces.type = 'Group'
                                                 AND namespaces.id = base_and_ancestors.parent_id)) SELECT id
      FROM base_and_ancestors AS namespaces
      WHERE (EXISTS
               (SELECT 1
                FROM plans
                INNER JOIN gitlab_subscriptions ON gitlab_subscriptions.hosted_plan_id = plans.id
                WHERE plans.name IN ('silver',
                                     'premium',
                                     'premium_trial',
                                     'gold',
                                     'ultimate',
                                     'ultimate_trial')
                  AND (gitlab_subscriptions.namespace_id = namespaces.id)))) base
   WHERE (namespaces.traversal_ids @> ARRAY[base.id])) namespaces

This is the query plan and the time summary:

Time: 2.980 s
  - planning: 1.581 ms
  - execution: 2.979 s
    - I/O read: 2.164 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 70717 (~552.50 MiB) from the buffer pool
  - reads: 1080 (~8.40 MiB) from the OS file cache, including disk I/O
  - dirtied: 113 (~904.00 KiB)
  - writes: 0

Does this MR meet the acceptance criteria?

Conformity

Related to #339313 (closed)

Edited by Francisco Javier López

Merge request reports

Loading