Skip to content

WIP: Refactor Group#all_projects to use recursive CTE

Krasimir Angelov requested to merge all-projects-recursive-cte into master

What does this MR do?

Currently Group#all_projects will run the following query for the gitlab-org group

SELECT projects.*
FROM projects
INNER JOIN routes rs ON rs.source_id = projects.id
AND rs.source_type = Project
WHERE (rs.path LIKE gitlab-org/%)

Plans - (somewhat) cold cache and hot cache.

This MR changes the implementation to use recursive CTE (given since 12.1 we no longer have to support MySQL). The new query is this:

SELECT "projects".*
FROM
  (WITH RECURSIVE groups AS
     (SELECT id,
             parent_id
      FROM namespaces
      WHERE id = 9970
      UNION SELECT namespaces.id,
                   namespaces.parent_id
      FROM namespaces
      INNER JOIN groups ON groups.id = namespaces.parent_id) SELECT projects.*
   FROM groups
   JOIN projects ON projects.namespace_id = groups.id) AS projects

Plans - cold cache and hot cache.

Edited by Krasimir Angelov

Merge request reports