Bulk lazy loader for epic aggregates
What does this MR do?
This MR is for quickly loading bulk epics and their subepics, and group issues by closed and open. It is a lazy load aggregator, and everything is as lazy as possible.
- The schema receives the query and calls
Loaders::LazyEpicAggregate -
LazyEpicAggregateadds to the list of pending ids until the query is ready to resolve - On resolution,
LazyEpicAggregateeither retrieves the epic from the tree or loads everything from the db by callingBulkEpicAggregateLoaderand creates the tree structure, which is a collection ofEpicNodeobjects which hold the state of each epic. - Resolution of the tree to get total is by facet (either count/weight_sum). Everything is loaded lazily, including recursively calculating count/weight sums - and these aren't done until they are asked for by facet.
- GraphQL structure here -> !25980 (merged)
- Original MR (rejected for non-performance) -> !22284 (closed)
SQL for &2688 (closed) on gitlab.com (id 22238)
HashAggregate (cost=6413.84..6417.84 rows=400 width=32) (actual time=0.745..0.749 rows=10 loops=1)
Group Key: issues.state_id, epics.id, epics.iid, epics.parent_id, epics.state_id
Buffers: shared hit=70
CTE base_and_descendants
-> Recursive Union (cost=0.29..640.84 rows=271 width=752) (actual time=0.069..0.341 rows=10 loops=1)
Buffers: shared hit=32
-> Index Scan using epics_pkey on epics epics_1 (cost=0.29..3.31 rows=1 width=752) (actual time=0.065..0.065 rows=1 loops=1)
Index Cond: (id = 22238)
Buffers: shared hit=3
-> Nested Loop (cost=0.29..63.21 rows=27 width=752) (actual time=0.034..0.085 rows=3 loops=3)
Buffers: shared hit=29
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=3 loops=3)
-> Index Scan using index_epics_on_parent_id on epics epics_2 (cost=0.29..6.27 rows=3 width=752) (actual time=0.012..0.025 rows=1 loops=10)
Index Cond: (parent_id = base_and_descendants.id)
Buffers: shared hit=29
-> Nested Loop Left Join (cost=0.85..5746.40 rows=1520 width=825) (actual time=0.205..0.736 rows=10 loops=1)
Buffers: shared hit=70
-> Nested Loop Left Join (cost=0.29..739.09 rows=1520 width=18) (actual time=0.133..0.519 rows=10 loops=1)
Buffers: shared hit=55
-> CTE Scan on base_and_descendants epics (cost=0.00..5.42 rows=271 width=14) (actual time=0.071..0.352 rows=10 loops=1)
Buffers: shared hit=32
-> Index Scan using index_epic_issues_on_epic_id on epic_issues (cost=0.29..2.65 rows=6 width=8) (actual time=0.016..0.016 rows=0 loops=10)
Index Cond: (epic_id = epics.id)
Buffers: shared hit=23
-> Index Scan using issues_pkey on issues (cost=0.56..3.28 rows=1 width=815) (actual time=0.021..0.021 rows=0 loops=10)
Index Cond: (id = epic_issues.issue_id)
Buffers: shared hit=15
Planning time: 12.871 ms
Execution time: 0.925 msSQL for epic id 43 on my local
WITH recursive "selected_epics" AS (
(
SELECT "epics".*
FROM "epics"
WHERE "epics"."id" = 43)
UNION
(
SELECT "epics".*
FROM "epics",
"selected_epics"
WHERE "epics"."parent_id" = "selected_epics"."id"))
SELECT epics.id,
epics.iid,
epics.parent_id,
issues.state_id,
count(issues) AS issues_count,
sum(COALESCE(issues.weight, 0)) AS issue_weight_state
FROM "selected_epics" AS "epics"
LEFT OUTER JOIN "epic_issues"
ON "epic_issues"."epic_id" = "epics"."id"
LEFT OUTER JOIN "issues"
ON "issues"."id" = "epic_issues"."issue_id"
GROUP BY issues.state_id,
epics.id,
epics.iid,
epics.parent_id
Sample output
My epic tree:
The output when I run the service, passing in epic 36.
[
{"id"=>43, "iid"=>8, "parent_id"=>41, "epic_state_id"=>1, "issues_state_id"=>2, "issues_count"=>2, "issues_weight_sum"=>14},
{"id"=>43, "iid"=>8, "parent_id"=>41, "epic_state_id"=>1, "issues_state_id"=>1, "issues_count"=>1, "issues_weight_sum"=>5},
{"id"=>42, "iid"=>7, "parent_id"=>39, "epic_state_id"=>1, "issues_state_id"=>nil, "issues_count"=>0, "issues_weight_sum"=>0},
{"id"=>44, "iid"=>9, "parent_id"=>41, "epic_state_id"=>1, "issues_state_id"=>nil, "issues_count"=>0, "issues_weight_sum"=>0},
{"id"=>37, "iid"=>2, "parent_id"=>36, "epic_state_id"=>1, "issues_state_id"=>1, "issues_count"=>1, "issues_weight_sum"=>2},
{"id"=>36, "iid"=>1, "parent_id"=>nil, "epic_state_id"=>1, "issues_state_id"=>1, "issues_count"=>1, "issues_weight_sum"=>1},
{"id"=>50, "iid"=>1, "parent_id"=>36, "epic_state_id"=>1, "issues_state_id"=>nil, "issues_count"=>0, "issues_weight_sum"=>0},
{"id"=>41, "iid"=>6, "parent_id"=>39, "epic_state_id"=>1, "issues_state_id"=>1, "issues_count"=>1, "issues_weight_sum"=>4},
{"id"=>39, "iid"=>4, "parent_id"=>37, "epic_state_id"=>1, "issues_state_id"=>1, "issues_count"=>1, "issues_weight_sum"=>3},
{"id"=>38, "iid"=>3, "parent_id"=>36, "epic_state_id"=>1, "issues_state_id"=>nil, "issues_count"=>0, "issues_weight_sum"=>0},
{"id"=>40, "iid"=>5, "parent_id"=>37, "epic_state_id"=>1, "issues_state_id"=>nil, "issues_count"=>0, "issues_weight_sum"=>0}
]Does this MR meet the acceptance criteria?
Conformity
- [-] Changelog entry
-
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Tested in all supported browsers
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
-
Label as security and @ mention @gitlab-com/gl-security/appsec -
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods -
Security reports checked/validated by a reviewer from the AppSec team
Feature flag: unfiltered_epic_aggregates
Related to #5164 (closed)
Edited by 🤖 GitLab Bot 🤖