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 onwork_item_parent_links.work_item_id), so theUNIONde-duplication only added cost. - A
tree_pathguard, the same patternGitlab::ObjectHierarchyuses, 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
- Create a work item with a few child work items, some open and some closed.
- Open it and note the child counts in the tree header and the counts shown on each child.
- 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 isWorkItem.lastonly 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.