Skip to content

GitLab Next

Merged
Opened by charlie ablett@cablett🛠Maintainer0 of 11 tasks completed0/11 tasks

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.

  1. The schema receives the query and calls Loaders::LazyEpicAggregate
  2. LazyEpicAggregate adds to the list of pending ids until the query is ready to resolve
  3. On resolution, LazyEpicAggregate either retrieves the epic from the tree or loads everything from the db by calling BulkEpicAggregateLoader and creates the tree structure, which is a collection of EpicNode objects which hold the state of each epic.
  4. 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.
SQL for &2688 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 ms
SQL 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:

image

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

Availability and Testing

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 charlie ablett