Skip to content

Use ApplicationSetting ancestors linear scopes

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

What does this MR do and why?

In this MR, we're switching the behavior of the method ApplicationSetting#elasticsearch_limited_project_exists? to use the linear version. The new behavior is behind the linear_application_setting_ancestor_scopes feature flag.

Database queries

The query has been tested using a group with 21 parents.

The original query was:

SELECT 1 AS one
FROM
  (SELECT) AS projects
WHERE (EXISTS
         (WITH RECURSIVE "base_and_ancestors" AS (
                                                    (SELECT "namespaces".*
                                                     FROM "namespaces"
                                                     WHERE "namespaces"."id" = 22)
                                                  UNION
                                                    (SELECT "namespaces".*
                                                     FROM "namespaces",
                                                          "base_and_ancestors"
                                                     WHERE "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT "namespaces".*
          FROM "base_and_ancestors" AS "namespaces"
          INNER JOIN "elasticsearch_indexed_namespaces" ON "elasticsearch_indexed_namespaces"."namespace_id" = "namespaces"."id")
       OR EXISTS
         (SELECT "elasticsearch_indexed_projects".*
          FROM "elasticsearch_indexed_projects"
          WHERE "elasticsearch_indexed_projects"."project_id" = 1))
LIMIT 1

This is the query plan and the times are:

Time: 1.835 ms
  - planning: 1.270 ms
  - execution: 0.565 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 128 (~1.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 1 AS one
FROM
  (SELECT) AS projects
WHERE (EXISTS
         (SELECT "namespaces".*
          FROM
            (SELECT "namespaces".*
             FROM "namespaces"
             WHERE "namespaces"."id" IN
                 (SELECT unnest(traversal_ids)
                  FROM "namespaces"
                  WHERE "namespaces"."id" = 30)) namespaces
          INNER JOIN "elasticsearch_indexed_namespaces" ON "elasticsearch_indexed_namespaces"."namespace_id" = "namespaces"."id")
       OR EXISTS
         (SELECT "elasticsearch_indexed_projects".*
          FROM "elasticsearch_indexed_projects"
          WHERE "elasticsearch_indexed_projects"."project_id" = 10))
LIMIT 1

This is the query plan and the times are:

Time: 1.148 ms
  - planning: 0.884 ms
  - execution: 0.264 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 120 (~960.00 KiB) 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

    Feature.enable(:use_traversal_ids_for_ancestor_scopes)

  3. Enable the feature flag for ApplicationSetting linear ancestors scopes

    Feature.enable(:linear_application_setting_ancestor_scopes)

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

ApplicationSetting.first.send(:elasticsearch_limited_project_exists?, Project.first)

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

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

Merge request reports