Skip to content

Fetch iterations by cadence id

Alexandru Croitor requested to merge fetch_iterations_by_cadence into master

What does this MR do?

Allow filtering iterations by a given cadence: #326185 (closed)

GraphQL query:

  query {
    group(fullPath: "top-group/sub-group") {
      iterations(iterationsCadenceId: "gid://gitlab/Iterations::Cadence/2") {
        nodes {
          id
          title
        }
      }
    }
  }
  • response:
      {
        "data": {
          "group": {
            "iterations": {
              "nodes": [
                {
                  "id": "gid://gitlab/Iteration/8",
                  "title": "Iteration 1 Jan"
                },
                {
                  "id": "gid://gitlab/Iteration/9",
                  "title": "Iteration 2 Jan"
                },
                {
                  "id": "gid://gitlab/Iteration/10",
                  "title": "Iteration 3 Jan"
                },
                {
                  "id": "gid://gitlab/Iteration/27",
                  "title": "March 20, 2021 - March 26, 2021"
                },
                {
                  "id": "gid://gitlab/Iteration/28",
                  "title": "March 27, 2021 - April 02, 2021"
                }
              ]
            }
          }
        }
      }

Screenshots (strongly suggested)

Database

We are basically adding filtering of iterations by iterations_cadence_id which has an index

NOTE: The cadences are not yet enabled on production so here is a staging query example that checks for iterations for a given cadence by looking up in ancestor groups as well:

  • Lookup iterations by cadence
    • in project

      details
      
        query {
          project(fullPath: "gitlab-org/build/omnibus-mirror/config_guess") {
            iterations(includeAncestors: false, iterationsCadenceId: "gid://gitlab/Iterations::Cadence/2") {
              nodes {
                id
                title
              }
            }
          }
        }
      
        explain (buffers, analyze) SELECT "sprints".* FROM ((SELECT "sprints".* FROM "sprints" WHERE "sprints"."project_id" = 2645585)
        UNION ALL
        (SELECT "sprints".* FROM "sprints" WHERE 1=0)) sprints
        WHERE "sprints"."iterations_cadence_id" = 277 ORDER BY due_date ASC NULLS LAST, "sprints"."title" ASC LIMIT 100;
      
                                                                               QUERY PLAN
        ---------------------------------------------------------------------------------------------------------------------
         Limit  (cost=3.17..3.17 rows=1 width=217) (actual time=0.028..0.028 rows=0 loops=1)
           Buffers: shared hit=1
           ->  Sort  (cost=3.17..3.17 rows=1 width=217) (actual time=0.027..0.028 rows=0 loops=1)
                 Sort Key: sprints.due_date, sprints.title
                 Sort Method: quicksort  Memory: 25kB
                 Buffers: shared hit=1
                 ->  Append  (cost=0.12..3.16 rows=1 width=217) (actual time=0.018..0.019 rows=0 loops=1)
                       Buffers: shared hit=1
                       ->  Index Scan using iteration_start_and_due_daterange_project_id_constraint on sprints  (cost=0.12..3.15 rows=1 width=217) (actual time=0.017..0.017 rows=0 loops=1)
                             Filter: ((project_id = 2645585) AND (iterations_cadence_id = 277))
                             Buffers: shared hit=1
         Planning Time: 0.349 ms
         Execution Time: 0.061 ms

      • including ancestors

        details
        
          query {
            project(fullPath: "gitlab-org/build/omnibus-mirror/config_guess") {
              iterations(includeAncestors: true, iterationsCadenceId: "gid://gitlab/Iterations::Cadence/2") {
                nodes {
                  id
                  title
                }
              }
            }
          }
        
          explain (buffers, analyze) SELECT "sprints".* FROM ((SELECT "sprints".* FROM "sprints" WHERE "sprints"."project_id" = 2645585)
          UNION ALL
          (SELECT "sprints".* FROM "sprints" WHERE "sprints"."group_id" IN (1920510, 1920469, 9970))) sprints
          WHERE "sprints"."iterations_cadence_id" = 277 ORDER BY due_date ASC NULLS LAST, "sprints"."title" ASC LIMIT 100;
        
                                                                                                       QUERY PLAN
          -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
           Limit  (cost=6.33..6.34 rows=2 width=217) (actual time=1.198..1.201 rows=1 loops=1)
             Buffers: shared hit=60
             ->  Sort  (cost=6.33..6.34 rows=2 width=217) (actual time=1.198..1.199 rows=1 loops=1)
                   Sort Key: sprints.due_date, sprints.title
                   Sort Method: quicksort  Memory: 25kB
                   Buffers: shared hit=60
                   ->  Append  (cost=0.12..6.32 rows=2 width=217) (actual time=1.165..1.167 rows=1 loops=1)
                         Buffers: shared hit=54
                         ->  Index Scan using iteration_start_and_due_daterange_project_id_constraint on sprints  (cost=0.12..3.15 rows=1 width=217) (actual time=0.949..0.950 rows=0 loops=1)
                               Filter: ((project_id = 2645585) AND (iterations_cadence_id = 277))
                               Buffers: shared hit=28
                         ->  Index Scan using iteration_start_and_due_date_iterations_cadence_id_constraint on sprints sprints_1  (cost=0.12..3.15 rows=1 width=217) (actual time=0.214..0.215 rows=1 loops=1)
                               Index Cond: (iterations_cadence_id = 277)
                               Filter: (group_id = ANY ('{1920510,1920469,9970}'::bigint[]))
                               Buffers: shared hit=26
           Planning Time: 2.238 ms
           Execution Time: 1.297 ms

        • by opened state

          details
          
            query {
              project(fullPath: "gitlab-org/build/omnibus-mirror/config_guess") {
                iterations(includeAncestors: false, state: opened, iterationsCadenceId: "gid://gitlab/Iterations::Cadence/2") {
                  nodes {
                    id
                    title
                  }
                }
              }
            }
          
            explain (buffers, analyze) SELECT "sprints".* FROM ((SELECT "sprints".* FROM "sprints" WHERE "sprints"."project_id" = 2645585)
            UNION ALL
            (SELECT "sprints".* FROM "sprints" WHERE "sprints"."group_id" IN (1920510, 1920469, 9970))) sprints
            WHERE (("sprints"."state_enum" IN (2)) OR ("sprints"."state_enum" IN (1))) AND "sprints"."iterations_cadence_id" = 277 ORDER BY due_date ASC NULLS LAST, "sprints"."title" ASC LIMIT 100;
          
                                                                                                        QUERY PLAN
            -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
             Limit  (cost=6.34..6.35 rows=2 width=217) (actual time=0.044..0.045 rows=0 loops=1)
               Buffers: shared hit=3
               ->  Sort  (cost=6.34..6.35 rows=2 width=217) (actual time=0.043..0.043 rows=0 loops=1)
                     Sort Key: sprints.due_date, sprints.title
                     Sort Method: quicksort  Memory: 25kB
                     Buffers: shared hit=3
                     ->  Append  (cost=0.12..6.33 rows=2 width=217) (actual time=0.036..0.036 rows=0 loops=1)
                           Buffers: shared hit=3
                           ->  Index Scan using iteration_start_and_due_daterange_project_id_constraint on sprints  (cost=0.12..3.15 rows=1 width=217) (actual time=0.011..0.011 rows=0 loops=1)
                                 Filter: ((project_id = 2645585) AND (iterations_cadence_id = 277) AND ((state_enum = 2) OR (state_enum = 1)))
                                 Buffers: shared hit=1
                           ->  Index Scan using iteration_start_and_due_date_iterations_cadence_id_constraint on sprints sprints_1  (cost=0.12..3.15 rows=1 width=217) (actual time=0.024..0.024 rows=0 loops=1)
                                 Index Cond: (iterations_cadence_id = 277)
                                 Filter: ((group_id = ANY ('{1920510,1920469,9970}'::bigint[])) AND ((state_enum = 2) OR (state_enum = 1)))
                                 Rows Removed by Filter: 1
                                 Buffers: shared hit=2
             Planning Time: 0.330 ms
             Execution Time: 0.076 ms

          • by timeframe

            details
            
              query {
                project(fullPath: "gitlab-org/build/omnibus-mirror/config_guess") {
                  iterations(includeAncestors: false, state: opened, timeframe: {start: "2019-01-01", end: "2020-01-01"}, iterationsCadenceId: "gid://gitlab/Iterations::Cadence/2") {
                    nodes {
                      id
                      title
                    }
                  }
                }
              }
            
              explain (buffers, analyze) SELECT "sprints".* FROM ((SELECT "sprints".* FROM "sprints" WHERE "sprints"."project_id" = 2645585)
              UNION ALL
              (SELECT "sprints".* FROM "sprints" WHERE "sprints"."group_id" IN (1920510, 1920469, 9970))) sprints
              WHERE (("sprints"."state_enum" IN (2)) OR ("sprints"."state_enum" IN (1))) AND
              (start_date <= '2020-01-01') AND (due_date >= '2019-01-01') AND
              "sprints"."iterations_cadence_id" = 277 ORDER BY due_date ASC NULLS LAST, "sprints"."title" ASC LIMIT 100;
            
                                                                                                                QUERY PLAN
              ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
               Limit  (cost=6.35..6.36 rows=2 width=217) (actual time=0.096..0.097 rows=0 loops=1)
                 Buffers: shared hit=3
                 ->  Sort  (cost=6.35..6.36 rows=2 width=217) (actual time=0.095..0.096 rows=0 loops=1)
                       Sort Key: sprints.due_date, sprints.title
                       Sort Method: quicksort  Memory: 25kB
                       Buffers: shared hit=3
                       ->  Append  (cost=0.12..6.34 rows=2 width=217) (actual time=0.090..0.091 rows=0 loops=1)
                             Buffers: shared hit=3
                             ->  Index Scan using iteration_start_and_due_daterange_project_id_constraint on sprints  (cost=0.12..3.15 rows=1 width=217) (actual time=0.014..0.014 rows=0 loops=1)
                                   Filter: ((start_date <= '2020-01-01'::date) AND (due_date >= '2019-01-01'::date) AND (project_id = 2645585) AND (iterations_cadence_id = 277) AND ((state_enum = 2) OR (state_enum = 1)
              ))
                                   Buffers: shared hit=1
                             ->  Index Scan using iteration_start_and_due_date_iterations_cadence_id_constraint on sprints sprints_1  (cost=0.12..3.16 rows=1 width=217) (actual time=0.075..0.075 rows=0 loops=1)
                                   Index Cond: (iterations_cadence_id = 277)
                                   Filter: ((start_date <= '2020-01-01'::date) AND (due_date >= '2019-01-01'::date) AND (group_id = ANY ('{1920510,1920469,9970}'::bigint[])) AND ((state_enum = 2) OR (state_enum = 1)))
                                   Rows Removed by Filter: 1
                                   Buffers: shared hit=2
               Planning Time: 0.954 ms
               Execution Time: 0.207 ms

    • in group

      details
      
        query {
          group(fullPath: "gitlab-org/build/omnibus-mirror") {
            iterations(includeAncestors: false, iterationsCadenceId: "gid://gitlab/Iterations::Cadence/2") {
              nodes {
                id
                title
              }
            }
          }
        }
      
        explain (buffers, analyze) SELECT "sprints".* FROM ((SELECT "sprints".* FROM "sprints" WHERE 1=0)
        UNION ALL
        (SELECT "sprints".* FROM "sprints" WHERE "sprints"."group_id" = 1920510)) sprints
        WHERE "sprints"."iterations_cadence_id" = 277 ORDER BY due_date ASC NULLS LAST, "sprints"."title" ASC LIMIT 100;
      
                                                                                                QUERY PLAN
        -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         Limit  (cost=3.17..3.17 rows=1 width=217) (actual time=0.083..0.084 rows=1 loops=1)
           Buffers: shared hit=2
           ->  Sort  (cost=3.17..3.17 rows=1 width=217) (actual time=0.082..0.083 rows=1 loops=1)
                 Sort Key: sprints.due_date, sprints.title
                 Sort Method: quicksort  Memory: 25kB
                 Buffers: shared hit=2
                 ->  Append  (cost=0.12..3.16 rows=1 width=217) (actual time=0.070..0.071 rows=1 loops=1)
                       Buffers: shared hit=2
                       ->  Index Scan using iteration_start_and_due_date_iterations_cadence_id_constraint on sprints  (cost=0.12..3.15 rows=1 width=217) (actual time=0.069..0.069 rows=1 loops=1)
                             Index Cond: (iterations_cadence_id = 277)
                             Filter: (group_id = 1920510)
                             Buffers: shared hit=2
         Planning Time: 0.266 ms
         Execution Time: 0.114 ms

      • including ancestors

        details
        
          query {
            group(fullPath: "gitlab-org/build/omnibus-mirror") {
              iterations(includeAncestors: true, iterationsCadenceId: "gid://gitlab/Iterations::Cadence/2") {
                nodes {
                  id
                  title
                }
              }
            }
          }
        
          explain (buffers, analyze) SELECT "sprints".* FROM ((SELECT "sprints".* FROM "sprints" WHERE 1=0)
          UNION ALL
          (SELECT "sprints".* FROM "sprints" WHERE "sprints"."group_id" IN (1920510, 1920469, 9970))) sprints
          WHERE "sprints"."iterations_cadence_id" = 277 ORDER BY due_date ASC NULLS LAST, "sprints"."title" ASC LIMIT 100;
        
                                                                                                  QUERY PLAN
          -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
           Limit  (cost=3.17..3.18 rows=1 width=221) (actual time=0.062..0.063 rows=1 loops=1)
             Buffers: shared hit=2
             ->  Sort  (cost=3.17..3.18 rows=1 width=221) (actual time=0.060..0.061 rows=1 loops=1)
                   Sort Key: sprints.due_date, sprints.title
                   Sort Method: quicksort  Memory: 25kB
                   Buffers: shared hit=2
                   ->  Append  (cost=0.12..3.16 rows=1 width=221) (actual time=0.051..0.052 rows=1 loops=1)
                         Buffers: shared hit=2
                         ->  Index Scan using iteration_start_and_due_date_iterations_cadence_id_constraint on sprints  (cost=0.12..3.15 rows=1 width=221) (actual time=0.050..0.051 rows=1 loops=1)
                               Index Cond: (iterations_cadence_id = 277)
                               Filter: (group_id = ANY ('{1920510,1920469,9970}'::bigint[]))
                               Buffers: shared hit=2
           Planning Time: 0.310 ms
           Execution Time: 0.115 ms

        • by timeframe

          details
          
            query {
              group(fullPath: "gitlab-org/build/omnibus-mirror") {
                iterations(includeAncestors: false, state: opened, timeframe: {start: "2019-01-01", end: "2020-01-01"}, iterationsCadenceId: "gid://gitlab/Iterations::Cadence/2") {
                  nodes {
                    id
                    title
                  }
                }
              }
            }
          
            explain (buffers, analyze) SELECT "sprints".* FROM ((SELECT "sprints".* FROM "sprints" WHERE 1=0)
            UNION ALL
            (SELECT "sprints".* FROM "sprints" WHERE "sprints"."group_id" IN (1920510, 1920469, 9970))) sprints
            WHERE (("sprints"."state_enum" IN (2)) OR ("sprints"."state_enum" IN (1))) AND
            (start_date <= '2020-01-01') AND (due_date >= '2019-01-01') AND
            "sprints"."iterations_cadence_id" = 277 ORDER BY due_date ASC NULLS LAST, "sprints"."title" ASC LIMIT 100;
          
                                                                                                             QUERY PLAN
            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
             Limit  (cost=3.18..3.19 rows=1 width=217) (actual time=0.062..0.063 rows=0 loops=1)
               Buffers: shared hit=2
               ->  Sort  (cost=3.18..3.19 rows=1 width=217) (actual time=0.061..0.062 rows=0 loops=1)
                     Sort Key: sprints.due_date, sprints.title
                     Sort Method: quicksort  Memory: 25kB
                     Buffers: shared hit=2
                     ->  Append  (cost=0.12..3.17 rows=1 width=217) (actual time=0.040..0.040 rows=0 loops=1)
                           Buffers: shared hit=2
                           ->  Index Scan using iteration_start_and_due_date_iterations_cadence_id_constraint on sprints  (cost=0.12..3.16 rows=1 width=217) (actual time=0.039..0.039 rows=0 loops=1)
                                 Index Cond: (iterations_cadence_id = 277)
                                 Filter: ((start_date <= '2020-01-01'::date) AND (due_date >= '2019-01-01'::date) AND (group_id = ANY ('{1920510,1920469,9970}'::bigint[])) AND ((state_enum = 2) OR (state_enum = 1)))
                                 Rows Removed by Filter: 1
                                 Buffers: shared hit=2
             Planning Time: 0.425 ms
             Execution Time: 0.091 ms

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
Edited by Alexandru Croitor

Merge request reports

Loading