Implement iteration cadences resolver (!55422) · Merge requests · GitLab.org / GitLab

Alexandru Croitor requested to merge fetch_iterations_cadences_for_group into master Mar 01, 2021

What does this MR do?

Examples:

Enabling/Disabling feature flag

::Feature.enable(:iteration_cadences)

::Feature.disable(:iteration_cadences)

With feature flag enabled

Graphql Query

 query {
   group(fullPath: "top-group/sub-group") {
     id
 iterationCadences {
       nodes {
         id
         title
         durationInWeeks
       }
     }
   }
 }

Response

   {
     "data": {
       "group": {
         "id": "gid://gitlab/Group/108",
         "iterationCadences": {
           "nodes": [
             {
               "id": "gid://gitlab/Iterations::Cadence/7",
               "title": "Second cadence",
               "durationInWeeks": 2
             },
             {
               "id": "gid://gitlab/Iterations::Cadence/6",
               "title": "Second cadence",
               "durationInWeeks": 2
             },
             {
               "id": "gid://gitlab/Iterations::Cadence/5",
               "title": "Second cadence",
               "durationInWeeks": 2
             },
             {
               "id": "gid://gitlab/Iterations::Cadence/2",
               "title": "Sub Group Iterations",
               "durationInWeeks": null
             }
           ]
         }
       }
     }
   }

With feature flag disabled

Graphql Query

 query {
   group(fullPath: "top-group/sub-group") {
     id
 iterationCadences {
       nodes {
         id
         title
         durationInWeeks
       }
     }
   }
 }

Response

 {
   "data": {
     "group": {
       "id": "gid://gitlab/Group/108",
       "iterationCadences": null
     }
   },
   "errors": [
     {
       "message": "Resource not found",
       "locations": [
         {
           "line": 4,
           "column": 3
         }
       ],
       "path": [
         "group",
         "iterationCadences"
       ]
     }
   ]
 }

Database

Mainly the query on iterations_cadences table, with different filters

Plans are generated from local data as we do not have enough data on production yet

 --Total count of records on localhost: 15607
 SELECT count(*) FROM "iterations_cadences" 


 --Total count of records on database-lab: 1957
 explain select id from iterations_cadences
 Index Only Scan using iterations_cadences_pkey on public.iterations_cadences  (cost=0.28..47.48 rows=1947 width=8) (actual 
 time=4.119..19.564 rows=1957 loops=1)

iteration cadences in a group: https://explain.depesz.com/s/ClXPw

  explain analyze

  SELECT "iterations_cadences".*
  FROM "iterations_cadences"
  WHERE "iterations_cadences"."group_id" = 107
  ORDER BY "iterations_cadences"."title" ASC LIMIT 100

  "Limit  (cost=251.12..251.37 rows=100 width=111) (actual time=0.569..0.588 rows=100 loops=1)"
  "  ->  Sort  (cost=251.12..254.38 rows=1301 width=111) (actual time=0.568..0.576 rows=100 loops=1)"
  "        Sort Key: title"
  "        Sort Method: top-N heapsort  Memory: 62kB"
  "        ->  Index Scan using index_iterations_cadences_on_group_id on iterations_cadences  (cost=0.29..201.40 rows=1301 width=111) (actual time=0.024..0.245 rows=1301 loops=1)"
  "              Index Cond: (group_id = 107)"
  "Planning Time: 0.069 ms"
  "Execution Time: 0.610 ms"

within a group filtered by id: https://explain.depesz.com/s/VgnQ

  explain
  
  SELECT "iterations_cadences".*
  FROM "iterations_cadences"
  WHERE "iterations_cadences"."group_id" = 107
  AND "iterations_cadences"."id" = 1001
  ORDER BY "iterations_cadences"."title" ASC LIMIT 100
  
  "Limit  (cost=2.31..2.32 rows=1 width=111) (actual time=0.024..0.025 rows=0 loops=1)"
  "  ->  Sort  (cost=2.31..2.32 rows=1 width=111) (actual time=0.024..0.024 rows=0 loops=1)"
  "        Sort Key: title"
  "        Sort Method: quicksort  Memory: 25kB"
  "        ->  Index Scan using iterations_cadences_pkey on iterations_cadences  (cost=0.29..2.31 rows=1 width=111) (actual time=0.020..0.020 rows=0 loops=1)"
  "              Index Cond: (id = 1001)"
  "              Filter: (group_id = 107)"
  "              Rows Removed by Filter: 1"
  "Planning Time: 0.083 ms"
  "Execution Time: 0.051 ms"

within a group filtered by title: https://explain.depesz.com/s/zCgI

  explain analyze
  
  SELECT "iterations_cadences".*
  FROM "iterations_cadences"
  WHERE "iterations_cadences"."group_id" = 107
  AND title LIKE '%max%'
  ORDER BY "iterations_cadences"."title" ASC LIMIT 100
  
  "Limit  (cost=205.26..205.33 rows=26 width=111) (actual time=0.582..0.592 rows=40 loops=1)"
  "  ->  Sort  (cost=205.26..205.33 rows=26 width=111) (actual time=0.581..0.585 rows=40 loops=1)"
  "        Sort Key: title"
  "        Sort Method: quicksort  Memory: 33kB"
  "        ->  Index Scan using index_iterations_cadences_on_group_id on iterations_cadences  (cost=0.29..204.65 rows=26 width=111) (actual time=0.029..0.557 rows=40 loops=1)"
  "              Index Cond: (group_id = 107)"
  "              Filter: (title ~~ '%max%'::text)"
  "              Rows Removed by Filter: 1261"
  "Planning Time: 0.122 ms"
  "Execution Time: 0.615 ms"

within a group filtered by duration in weeks: https://explain.depesz.com/s/6Ljd

  explain analyze
  
  SELECT "iterations_cadences".*
  FROM "iterations_cadences"
  WHERE "iterations_cadences"."group_id" = 107
  AND duration_in_weeks = 2
  ORDER BY "iterations_cadences"."title" ASC LIMIT 100
  
  "Limit  (cost=214.97..215.22 rows=100 width=111) (actual time=0.353..0.373 rows=100 loops=1)"
  "  ->  Sort  (cost=214.97..215.65 rows=270 width=111) (actual time=0.353..0.360 rows=100 loops=1)"
  "        Sort Key: title"
  "        Sort Method: top-N heapsort  Memory: 60kB"
  "        ->  Index Scan using index_iterations_cadences_on_group_id on iterations_cadences  (cost=0.29..204.65 rows=270 width=111) (actual time=0.018..0.244 rows=281 loops=1)"
  "              Index Cond: (group_id = 107)"
  "              Filter: (duration_in_weeks = 2)"
  "              Rows Removed by Filter: 1020"
  "Planning Time: 0.075 ms"
  "Execution Time: 0.398 ms"

within a group filtered by active flag: https://explain.depesz.com/s/sltH

  explain analyze
  
  SELECT "iterations_cadences".*
  FROM "iterations_cadences"
  WHERE "iterations_cadences"."group_id" = 107
  AND active = false
  ORDER BY "iterations_cadences"."title" ASC LIMIT 100
  
  "Limit  (cost=226.09..226.34 rows=100 width=111) (actual time=0.478..0.498 rows=100 loops=1)"
  "  ->  Sort  (cost=226.09..227.70 rows=646 width=111) (actual time=0.477..0.485 rows=100 loops=1)"
  "        Sort Key: title"
  "        Sort Method: top-N heapsort  Memory: 62kB"
  "        ->  Index Scan using index_iterations_cadences_on_group_id on iterations_cadences  (cost=0.29..201.40 rows=646 width=111) (actual time=0.018..0.284 rows=634 loops=1)"
  "              Index Cond: (group_id = 107)"
  "              Filter: (NOT active)"
  "              Rows Removed by Filter: 667"
  "Planning Time: 0.079 ms"
  "Execution Time: 0.521 ms"

within a group filtered by automatic flag: https://explain.depesz.com/s/d4Wu

  explain analyze
  
  SELECT "iterations_cadences".*
  FROM "iterations_cadences"
  WHERE "iterations_cadences"."group_id" = 107
  AND automatic = false
  ORDER BY "iterations_cadences"."title" ASC LIMIT 100
  
  "Limit  (cost=225.97..226.22 rows=100 width=111) (actual time=0.479..0.498 rows=100 loops=1)"
  "  ->  Sort  (cost=225.97..227.58 rows=643 width=111) (actual time=0.479..0.486 rows=100 loops=1)"
  "        Sort Key: title"
  "        Sort Method: top-N heapsort  Memory: 59kB"
  "        ->  Index Scan using index_iterations_cadences_on_group_id on iterations_cadences  (cost=0.29..201.40 rows=643 width=111) (actual time=0.017..0.286 rows=641 loops=1)"
  "              Index Cond: (group_id = 107)"
  "              Filter: (NOT automatic)"
  "              Rows Removed by Filter: 660"
  "Planning Time: 0.075 ms"
  "Execution Time: 0.522 ms"

with all filters: https://explain.depesz.com/s/iwGI

  explain analyze
  
  SELECT "iterations_cadences".*
  FROM "iterations_cadences"
  WHERE "iterations_cadences"."group_id" IN (107, 108, 109, 110)
  AND active = false
  AND automatic = false
  AND duration_in_weeks = 2
  AND title LIKE '%max%'
  ORDER BY "iterations_cadences"."title" ASC LIMIT 100
  
  "Limit  (cost=339.93..339.94 rows=5 width=111) (actual time=1.282..1.285 rows=11 loops=1)"
  "  ->  Sort  (cost=339.93..339.94 rows=5 width=111) (actual time=1.281..1.283 rows=11 loops=1)"
  "        Sort Key: title"
  "        Sort Method: quicksort  Memory: 27kB"
  "        ->  Index Scan using index_iterations_cadences_on_group_id on iterations_cadences  (cost=0.29..339.87 rows=5 width=111) (actual time=0.066..1.271 rows=11 loops=1)"
  "              Index Cond: (group_id = ANY ('{107,108,109,110}'::bigint[]))"
  "              Filter: ((NOT active) AND (NOT automatic) AND (title ~~ '%max%'::text) AND (duration_in_weeks = 2))"
  "              Rows Removed by Filter: 5196"
  "Planning Time: 0.215 ms"
  "Execution Time: 1.304 ms"

re #323074 (closed)

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

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
Informed Infrastructure department of a default or new setting change, if applicable per definition of done

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

Edited Mar 16, 2021 by Alexandru Croitor

Implement iteration cadences resolver