Update code stage calculation in VSA (!47215) · Merge requests · GitLab.org / GitLab

Adam Hegyi requested to merge add-first-commit-at-to-code-stage into master Nov 09, 2020

What does this MR do?

This change updates the calculation in value stream analytics. For the code stage, the start event timestamp can be first_commit_at (new) or first_mentioned_in_commit_at (existing).

The code stage duration calculation now based on this formula:

Time between:
(issue_first_mentioned_in_commit OR first_commit_at) - merge_request_created_at

The change also introduces a separate stage where we can use the first_commit_at date. This can be used for creating customized stages.

Value stream analytics

How to test the stage:

Make sure you have ultimate license.
Seed VSA: SEED_CYCLE_ANALYTICS=true FILTER=cycle_analytics rake db:seed_fu
Copy the printed link and navigate to the group level.
Go to Analytics -> Value Stream
Create a new stage.
Start event: merge request first commit at.
End event: merge request merged.
Probably it will show "no data"

Fake some data (rails console):

mr = MergeRequest.last # assuming that you just ran the fixture
mr.metrics.update(latest_closed_at: Date.yesterday)
mr.metrics.update(first_commit_at: 1.week.ago)

After refreshing the page you should see one Item showing up when selecting the new stage.

DB query

Before: https://explain.depesz.com/s/U1yQ3

SELECT EXTRACT(EPOCH
               FROM percentile_cont(0.5) WITHIN GROUP(
                                                      ORDER BY "merge_requests"."created_at" - "issue_metrics"."first_mentioned_in_commit_at")) AS median
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
INNER JOIN "merge_requests_closing_issues" ON "merge_requests_closing_issues"."merge_request_id" = "merge_requests"."id"
INNER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
LEFT JOIN project_features ON projects.id = project_features.project_id
INNER JOIN "issue_metrics" ON "merge_requests_closing_issues"."issue_id" = "issue_metrics"."issue_id"
WHERE "projects"."namespace_id" IN
    (WITH RECURSIVE "base_and_descendants" AS (
                                                 (SELECT "namespaces".*
                                                  FROM "namespaces"
                                                  WHERE "namespaces"."type" = 'Group'
                                                    AND "namespaces"."id" = 9970)
                                               UNION
                                                 (SELECT "namespaces".*
                                                  FROM "namespaces",
                                                       "base_and_descendants"
                                                  WHERE "namespaces"."type" = 'Group'
                                                    AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id"
     FROM "base_and_descendants" AS "namespaces")
  AND (EXISTS
         (SELECT 1
          FROM "project_authorizations"
          WHERE "project_authorizations"."user_id" = 4156052
            AND (project_authorizations.project_id = projects.id))
       OR projects.visibility_level IN (0,
                                        10,
                                        20))
  AND ("project_features"."merge_requests_access_level" > 0
       OR "project_features"."merge_requests_access_level" IS NULL)
  AND "merge_requests"."created_at" <= '2020-11-10 23:59:59.999999'
  AND "merge_requests"."created_at" >= '2020-10-12 00:00:00'
  AND "merge_requests"."created_at" >= "issue_metrics"."first_mentioned_in_commit_at"

After: https://explain.depesz.com/s/rRoo

SELECT EXTRACT(EPOCH
               FROM percentile_cont(0.5) WITHIN GROUP(
                                                      ORDER BY "merge_requests"."created_at" - COALESCE("issue_metrics"."first_mentioned_in_commit_at", "merge_request_metrics"."first_commit_at"))) AS median
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
LEFT OUTER JOIN "merge_requests_closing_issues" ON "merge_requests"."id" = "merge_requests_closing_issues"."merge_request_id"
LEFT OUTER JOIN "issue_metrics" ON "merge_requests_closing_issues"."issue_id" = "issue_metrics"."issue_id"
LEFT OUTER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE "projects"."namespace_id" IN
    (WITH RECURSIVE "base_and_descendants" AS (
                                                 (SELECT "namespaces".*
                                                  FROM "namespaces"
                                                  WHERE "namespaces"."type" = 'Group'
                                                    AND "namespaces"."id" = 9970)
                                               UNION
                                                 (SELECT "namespaces".*
                                                  FROM "namespaces",
                                                       "base_and_descendants"
                                                  WHERE "namespaces"."type" = 'Group'
                                                    AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id"
     FROM "base_and_descendants" AS "namespaces")
  AND (EXISTS
         (SELECT 1
          FROM "project_authorizations"
          WHERE "project_authorizations"."user_id" = 4156052
            AND (project_authorizations.project_id = projects.id))
       OR projects.visibility_level IN (0,
                                        10,
                                        20))
  AND ("project_features"."merge_requests_access_level" > 0
       OR "project_features"."merge_requests_access_level" IS NULL)
  AND "merge_requests"."created_at" <= '2020-11-12 23:59:59.999999'
  AND "merge_requests"."created_at" >= '2020-10-14 00:00:00'
  AND "merge_requests"."created_at" >= COALESCE("issue_metrics"."first_mentioned_in_commit_at", "merge_request_metrics"."first_commit_at")

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 Nov 12, 2020 by Adam Hegyi

Update code stage calculation in VSA