Batch rolled-up counts by type for work items

What does this MR do and why?

rolledUpCountsByType on the work item hierarchy widget ran one recursive query per work item. Work item trees select it for every child, and API clients can select it on every node of a list, so the query count grew with the number of work items returned.

This MR loads the counts for all work items in the response with one recursive query over work_item_parent_links that keeps the root id, and resolves the field through BatchLoader. With the flag on, the widget model uses the same query for the single work item case.

  • The query uses UNION ALL. A work item has at most one parent (unique index on work_item_parent_links.work_item_id), so the UNION de-duplication only added cost.
  • A tree_path guard, the same pattern Gitlab::ObjectHierarchy uses, stops at corrupt cycles and never counts a work item as its own descendant, like the previous query.

Output is unchanged. The widgets and features paths both resolve through the same type, so both are covered.

Feature flag

The batching is behind the batch_work_item_rolled_up_counts flag (gitlab_com_derisk, default off), checked against the work item's top-level group. With the flag off, the field runs the previous per-item query, as on master. On a page that mixes top-level groups, only the work items under enabled groups are batched. Rollout: #639976

Queries

Before (once per work item):

WITH RECURSIVE "base_and_descendants" AS ((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"."sprint_id", "issues"."blocking_issues_count", "issues"."upvotes_count", "issues"."work_item_type_id", "issues"."namespace_id", "issues"."start_date", "issues"."imported_from", "issues"."namespace_traversal_ids" FROM "issues" WHERE "issues"."id" = <work_item_id>)
UNION
(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"."sprint_id", "issues"."blocking_issues_count", "issues"."upvotes_count", "issues"."work_item_type_id", "issues"."namespace_id", "issues"."start_date", "issues"."imported_from", "issues"."namespace_traversal_ids" FROM "issues", "base_and_descendants", "work_item_parent_links" WHERE "work_item_parent_links"."work_item_id" = "issues"."id" AND "work_item_parent_links"."work_item_parent_id" = "base_and_descendants"."id")) SELECT COUNT(*) AS "count_all", "work_item_type_id" AS "work_item_type_id", "state_id" AS "state_id" FROM "base_and_descendants" AS "issues" WHERE "issues"."id" NOT IN (SELECT "issues"."id" FROM "issues" WHERE "issues"."id" = <work_item_id>) GROUP BY "work_item_type_id", "state_id"

After (once per response):

WITH RECURSIVE "descendant_links" AS ((SELECT "work_item_parent_links"."work_item_parent_id" AS root_id, "work_item_parent_links"."work_item_id", ARRAY[work_item_parent_links.work_item_parent_id, work_item_parent_links.work_item_id] AS tree_path, work_item_parent_links.work_item_id = work_item_parent_links.work_item_parent_id AS tree_cycle FROM "work_item_parent_links" WHERE "work_item_parent_links"."work_item_parent_id" IN (<work_item_ids>))
UNION ALL
(SELECT "descendant_links"."root_id", "work_item_parent_links"."work_item_id", descendant_links.tree_path || work_item_parent_links.work_item_id, work_item_parent_links.work_item_id = ANY(descendant_links.tree_path) FROM "work_item_parent_links", "descendant_links" WHERE "work_item_parent_links"."work_item_parent_id" = "descendant_links"."work_item_id" AND "descendant_links"."tree_cycle" = FALSE)) SELECT COUNT(*) AS "count_all", "descendant_links"."root_id" AS "descendant_links_root_id", "issues"."work_item_type_id" AS "issues_work_item_type_id", "issues"."state_id" AS "issues_state_id" FROM "issues" INNER JOIN "descendant_links" ON "descendant_links"."work_item_id" = "issues"."id" WHERE "descendant_links"."tree_cycle" = FALSE GROUP BY "descendant_links"."root_id", "issues"."work_item_type_id", "issues"."state_id"

Query plans on gitlab-production-main (Database Lab, warm, epic with 638 descendants). Both versions count the same descendants:

Before (once per work item) After (once per response)
Plan https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/83094/commands/190417 https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/83094/commands/190419
Execution 23.7 ms 8.1 ms
Buffers 8,523 hit 5,678 hit

With a page of work items, the before query runs once per item, and the after query runs once for the whole page. For the 26 children of the same epic (one tree page), the after query takes 7.5 ms and 5,509 buffers in one statement.

On a cold cache, the new version reads fewer pages than the old one for the same tree, and adding the children to the parent's statement adds no disk reads, only cache hits. The worst cold case I measured was a tree page with 3,203 descendants: 10.6 s, almost all disk reads, in one statement instead of one per child. That is under the 15 s statement timeout. A small number of workItemTreeQuery requests already spend 15 s or more in the database in total, across all their statements.

The batched statement shares one 15 s statement timeout. On the tree page it costs less than the parent's own statement did before. API clients that select rolledUpCountsByType for many large, unrelated work items on one page can now hit the timeout sooner. That is intended, because it caps the database cost of one request.

How to set up and validate locally

  1. Create a work item with a few child work items, some open and some closed.
  2. Open it and note the child counts in the tree header and the counts shown on each child.
  3. In the Rails console, enable the flag for its top-level group with Feature.enable(:batch_work_item_rolled_up_counts, WorkItem.last.namespace.root_ancestor) (the parent you just created is WorkItem.last only if you created it last), then reload. The counts are the same.

References

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.

Edited by Agnes Slota

Merge request reports

Loading
Loading