Skip to content

Fetching all projects of group and applying conditions in audit events

Hitesh Raghuvanshi requested to merge 437282-handle-projects-timeout into master

What does this MR do and why?

Currently we are fetching the list of all non deleted projects of group in audit events controller by using group.all_projects_except_soft_deleted, which is triggering an inefficient query(check query plan section) and leading to statement timeouts. The main reason for inefficiency is that there is a where clause on 2 non-indexed fields marked_for_deletion_at and pending_delete.

So to fix this problem, we will be fetching all projects of the group by running group.all_projects and then iterate on them and do not include those projects which are pending for deletion or marked for deletion by checking in ruby code. You can observe the performance improvement in the query plan. Proper fix would be deployed as part of #438351 (closed).

Query plan

Old

Joe bot got stuck for groups with huge no. of projects.

SELECT 
  "projects"."id" 
FROM 
  "projects" 
WHERE 
  "projects"."namespace_id" IN (
    SELECT 
      namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1) ] AS id 
    FROM 
      "namespaces" 
    WHERE 
      "namespaces"."type" = 'Group' 
      AND (
        traversal_ids @ > ('{2769844}')
      )
  ) 
  AND "projects"."marked_for_deletion_at" IS NULL 
  AND "projects"."pending_delete" = FALSE 
ORDER BY 
  "projects"."id" ASC 
LIMIT 
  1;

New

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/25401/commands/80566

SELECT 
  "projects"."id" 
FROM 
  "projects" 
WHERE 
  "projects"."namespace_id" IN (
    SELECT 
      namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1) ] AS id 
    FROM 
      "namespaces" 
    WHERE 
      "namespaces"."type" = 'Group' 
      AND (
        traversal_ids @ > ('{2769844}')
      )
  ) 
ORDER BY 
  "projects"."id" ASC 
LIMIT 
  1;

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before After

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

Related to #437282 (closed)

Edited by Hitesh Raghuvanshi

Merge request reports