Skip to content

Update IssuesFinder and WorkItemsFinder to query by namespace_id

Matt D'Angelorequested to merge
536351-update-issuesfinder-and-workitemsfinder-to-query-by-namespace_id into master

What does this MR do and why?

This MR introduces a feature flag which updates the logic of the Issues / WorkItems finders to query by namespace_id instead of project_id when filtering down the scope of work items. This change is behind the use_namespace_id_for_issue_and_work_item_finders feature flag, scoped to a specific user. This will enable us to switch to using this column on a per user basis to assess performance when paired with the new index(es), in progress here

References

Screenshots or screen recordings

Not Provided

How to set up and validate locally

While the changes here should be transparent, it's possible to validate this change by looking at the query that's produced using the performance bar

  1. Enable the use_namespace_id_for_issue_and_work_item_finders feature flag
  2. With the performance bar enabled, navigate to the issues page for a given project (e.g. http://localhost:3000/flightjs/Flight/-/issues)
  3. Select the call to POST graphql (getIssuesEE)
  4. Observe that the database query filters work items by namespace_id. An example of the query is posted below. Note the "issues"."namespace_id" = 34:
Database Query 

SELECT "issues"."id",
       "issues"."title",
       "issues"."author_id",
       "issues"."project_id",
       "issues"."created_at",
       "issues"."updated_at",
       "issues"."description",
       "issues"."milestone_id",
       "issues"."iid",
       "issues"."updated_by_id",
       "issues"."weight",
       "issues"."confidential",
       "issues"."due_date",
       "issues"."moved_to_id",
       "issues"."lock_version",
       "issues"."title_html",
       "issues"."description_html",
       "issues"."time_estimate",
       "issues"."relative_position",
       "issues"."service_desk_reply_to",
       "issues"."cached_markdown_version",
       "issues"."last_edited_at",
       "issues"."last_edited_by_id",
       "issues"."discussion_locked",
       "issues"."closed_at",
       "issues"."closed_by_id",
       "issues"."state_id",
       "issues"."duplicated_to_id",
       "issues"."promoted_to_epic_id",
       "issues"."health_status",
       "issues"."external_key",
       "issues"."sprint_id",
       "issues"."blocking_issues_count",
       "issues"."upvotes_count",
       "issues"."work_item_type_id",
       "issues"."namespace_id",
       "issues"."start_date",
       "issues"."imported_from"
FROM "issues"
WHERE "issues"."namespace_id" = 34
  AND "issues"."state_id" = 1
  AND "issues"."work_item_type_id" IN (1, 2, 5, 7, 6, 3)
ORDER BY "issues"."created_at" DESC, "issues"."id" DESC
LIMIT 21
5. Disable the feature flag and repeat the steps above. Observe that the `namespace_id` filter reverts to `project_id` 6. Repeat the steps above with the "Your work", group level issues and consolidated work items page, and observe the change in the join structure

MR acceptance checklist

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

Database

Project Level Raw SQL Before Change 

SELECT "issues"."id",
       "issues"."title",
       "issues"."author_id",
       "issues"."project_id",
       "issues"."created_at",
       "issues"."updated_at",
       "issues"."description",
       "issues"."milestone_id",
       "issues"."iid",
       "issues"."updated_by_id",
       "issues"."weight",
       "issues"."confidential",
       "issues"."due_date",
       "issues"."moved_to_id",
       "issues"."lock_version",
       "issues"."title_html",
       "issues"."description_html",
       "issues"."time_estimate",
       "issues"."relative_position",
       "issues"."service_desk_reply_to",
       "issues"."cached_markdown_version",
       "issues"."last_edited_at",
       "issues"."last_edited_by_id",
       "issues"."discussion_locked",
       "issues"."closed_at",
       "issues"."closed_by_id",
       "issues"."state_id",
       "issues"."duplicated_to_id",
       "issues"."promoted_to_epic_id",
       "issues"."health_status",
       "issues"."external_key",
       "issues"."sprint_id",
       "issues"."blocking_issues_count",
       "issues"."upvotes_count",
       "issues"."work_item_type_id",
       "issues"."namespace_id",
       "issues"."start_date",
       "issues"."imported_from"
FROM "issues"
WHERE "issues"."project_id" = 7
  AND "issues"."state_id" = 1
  AND "issues"."work_item_type_id" IN (1, 2, 5, 7, 6, 3)
ORDER BY "issues"."created_at" DESC, "issues"."id" DESC
LIMIT 21
Project Level Raw SQL After Change 

SELECT "issues"."id",
       "issues"."title",
       "issues"."author_id",
       "issues"."project_id",
       "issues"."created_at",
       "issues"."updated_at",
       "issues"."description",
       "issues"."milestone_id",
       "issues"."iid",
       "issues"."updated_by_id",
       "issues"."weight",
       "issues"."confidential",
       "issues"."due_date",
       "issues"."moved_to_id",
       "issues"."lock_version",
       "issues"."title_html",
       "issues"."description_html",
       "issues"."time_estimate",
       "issues"."relative_position",
       "issues"."service_desk_reply_to",
       "issues"."cached_markdown_version",
       "issues"."last_edited_at",
       "issues"."last_edited_by_id",
       "issues"."discussion_locked",
       "issues"."closed_at",
       "issues"."closed_by_id",
       "issues"."state_id",
       "issues"."duplicated_to_id",
       "issues"."promoted_to_epic_id",
       "issues"."health_status",
       "issues"."external_key",
       "issues"."sprint_id",
       "issues"."blocking_issues_count",
       "issues"."upvotes_count",
       "issues"."work_item_type_id",
       "issues"."namespace_id",
       "issues"."start_date",
       "issues"."imported_from"
FROM "issues"
WHERE "issues"."namespace_id" = 34
  AND "issues"."state_id" = 1
  AND "issues"."work_item_type_id" IN (1, 2, 5, 7, 6, 3)
ORDER BY "issues"."created_at" DESC, "issues"."id" DESC
LIMIT 21
Group Level Raw SQL Before Change 

SELECT "issues"."id",
       "issues"."title",
       "issues"."author_id",
       "issues"."project_id",
       "issues"."created_at",
       "issues"."updated_at",
       "issues"."description",
       "issues"."milestone_id",
       "issues"."iid",
       "issues"."updated_by_id",
       "issues"."weight",
       "issues"."confidential",
       "issues"."due_date",
       "issues"."moved_to_id",
       "issues"."lock_version",
       "issues"."title_html",
       "issues"."description_html",
       "issues"."time_estimate",
       "issues"."relative_position",
       "issues"."service_desk_reply_to",
       "issues"."cached_markdown_version",
       "issues"."last_edited_at",
       "issues"."last_edited_by_id",
       "issues"."discussion_locked",
       "issues"."closed_at",
       "issues"."closed_by_id",
       "issues"."state_id",
       "issues"."duplicated_to_id",
       "issues"."promoted_to_epic_id",
       "issues"."health_status",
       "issues"."external_key",
       "issues"."sprint_id",
       "issues"."blocking_issues_count",
       "issues"."upvotes_count",
       "issues"."work_item_type_id",
       "issues"."namespace_id",
       "issues"."start_date",
       "issues"."imported_from"
FROM "issues"
         INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
         LEFT JOIN project_features ON projects.id = project_features.project_id
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 @> ('{33}')))
  AND (EXISTS (SELECT 1
               FROM "project_authorizations"
               WHERE "project_authorizations"."user_id" = 1
                 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0, 10, 20))
  AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL)
  AND "issues"."state_id" = 1
  AND ("issues"."project_id" IS NULL OR "projects"."archived" = FALSE)
  AND "issues"."work_item_type_id" IN (1, 2, 5, 7, 6, 3)
ORDER BY "issues"."created_at" DESC, "issues"."id" DESC
LIMIT 21
Group Level Raw SQL After Change 

SELECT "issues"."id",
       "issues"."title",
       "issues"."author_id",
       "issues"."project_id",
       "issues"."created_at",
       "issues"."updated_at",
       "issues"."description",
       "issues"."milestone_id",
       "issues"."iid",
       "issues"."updated_by_id",
       "issues"."weight",
       "issues"."confidential",
       "issues"."due_date",
       "issues"."moved_to_id",
       "issues"."lock_version",
       "issues"."title_html",
       "issues"."description_html",
       "issues"."time_estimate",
       "issues"."relative_position",
       "issues"."service_desk_reply_to",
       "issues"."cached_markdown_version",
       "issues"."last_edited_at",
       "issues"."last_edited_by_id",
       "issues"."discussion_locked",
       "issues"."closed_at",
       "issues"."closed_by_id",
       "issues"."state_id",
       "issues"."duplicated_to_id",
       "issues"."promoted_to_epic_id",
       "issues"."health_status",
       "issues"."external_key",
       "issues"."sprint_id",
       "issues"."blocking_issues_count",
       "issues"."upvotes_count",
       "issues"."work_item_type_id",
       "issues"."namespace_id",
       "issues"."start_date",
       "issues"."imported_from"
FROM "issues"
         JOIN projects ON projects.project_namespace_id = issues.namespace_id
         LEFT JOIN project_features ON projects.id = project_features.project_id
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 @> ('{33}')))
  AND (EXISTS (SELECT 1
               FROM "project_authorizations"
               WHERE "project_authorizations"."user_id" = 1
                 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0, 10, 20))
  AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL)
  AND "issues"."state_id" = 1
  AND ("issues"."project_id" IS NULL OR "projects"."archived" = FALSE)
  AND "issues"."work_item_type_id" IN (1, 2, 5, 7, 6, 3)
ORDER BY "issues"."created_at" DESC, "issues"."id" DESC
LIMIT 21
Dashboard Query Raw SQL Before Change 

SELECT "issues"."id",
       "issues"."title",
       "issues"."author_id",
       "issues"."project_id",
       "issues"."created_at",
       "issues"."updated_at",
       "issues"."description",
       "issues"."milestone_id",
       "issues"."iid",
       "issues"."updated_by_id",
       "issues"."weight",
       "issues"."confidential",
       "issues"."due_date",
       "issues"."moved_to_id",
       "issues"."lock_version",
       "issues"."title_html",
       "issues"."description_html",
       "issues"."time_estimate",
       "issues"."relative_position",
       "issues"."service_desk_reply_to",
       "issues"."cached_markdown_version",
       "issues"."last_edited_at",
       "issues"."last_edited_by_id",
       "issues"."discussion_locked",
       "issues"."closed_at",
       "issues"."closed_by_id",
       "issues"."state_id",
       "issues"."duplicated_to_id",
       "issues"."promoted_to_epic_id",
       "issues"."health_status",
       "issues"."external_key",
       "issues"."sprint_id",
       "issues"."blocking_issues_count",
       "issues"."upvotes_count",
       "issues"."work_item_type_id",
       "issues"."namespace_id",
       "issues"."start_date",
       "issues"."imported_from"
FROM "issues"
         INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
         LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS (SELECT 1
               FROM "project_authorizations"
               WHERE "project_authorizations"."user_id" = 1
                 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0, 10, 20))
  AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL)
  AND "issues"."state_id" = 1
  AND EXISTS (SELECT "issue_assignees".*
              FROM "issue_assignees"
              WHERE "issue_assignees"."user_id" = 1 AND (issue_id = issues.id))
  AND ("issues"."project_id" IS NULL OR "projects"."archived" = FALSE)
  AND "issues"."work_item_type_id" IN (1, 2, 5, 7, 6, 3)
ORDER BY "issues"."created_at" DESC, "issues"."id" DESC
LIMIT 21
Dashboard Query Raw SQL After Change 

SELECT "issues"."id",
       "issues"."title",
       "issues"."author_id",
       "issues"."project_id",
       "issues"."created_at",
       "issues"."updated_at",
       "issues"."description",
       "issues"."milestone_id",
       "issues"."iid",
       "issues"."updated_by_id",
       "issues"."weight",
       "issues"."confidential",
       "issues"."due_date",
       "issues"."moved_to_id",
       "issues"."lock_version",
       "issues"."title_html",
       "issues"."description_html",
       "issues"."time_estimate",
       "issues"."relative_position",
       "issues"."service_desk_reply_to",
       "issues"."cached_markdown_version",
       "issues"."last_edited_at",
       "issues"."last_edited_by_id",
       "issues"."discussion_locked",
       "issues"."closed_at",
       "issues"."closed_by_id",
       "issues"."state_id",
       "issues"."duplicated_to_id",
       "issues"."promoted_to_epic_id",
       "issues"."health_status",
       "issues"."external_key",
       "issues"."sprint_id",
       "issues"."blocking_issues_count",
       "issues"."upvotes_count",
       "issues"."work_item_type_id",
       "issues"."namespace_id",
       "issues"."start_date",
       "issues"."imported_from"
FROM "issues"
         JOIN projects ON projects.project_namespace_id = issues.namespace_id
         LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS (SELECT 1
               FROM "project_authorizations"
               WHERE "project_authorizations"."user_id" = 1
                 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0, 10, 20))
  AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL)
  AND "issues"."state_id" = 1
  AND EXISTS (SELECT "issue_assignees".*
              FROM "issue_assignees"
              WHERE "issue_assignees"."user_id" = 1 AND (issue_id = issues.id))
  AND ("issues"."project_id" IS NULL OR "projects"."archived" = FALSE)
  AND "issues"."work_item_type_id" IN (1, 2, 5, 7, 6, 3)
ORDER BY "issues"."created_at" DESC, "issues"."id" DESC
LIMIT 21
Query Plans for Consolidated List
Scenario pg.ai link
Existing Query pg.ai
New Query without index pg.ai
New Query with index pg.ai
Query Plans for Project Issues page (`gitlab-org/gitlab`)
Scenario pg.ai link
Existing Query pg.ai
New Query with index pg.ai
Query Plans for Group Issues page (`gitlab-org`)
Scenario pg.ai link
Existing Query pg.ai
New Query with index pg.ai
Query Plans for Dashboard Query (`gitlab-qa`)
Scenario pg.ai link
Existing Query pg.ai
New Query with index pg.ai

Related to #536351 (closed)

Edited by Matt D'Angelo

Merge request reports