Remove `[analyzer]_pipeline` metrics join on ci_builds"

What does this MR do and why?

[analyzer]_pipeline service ping metrics are sent for every CI job that contains a scan, not just for those that are part of successful non-retried jobs. This is an attempt to reduce the load on the database.

Related to #338010 (closed)

We took similar approach in this MR.

Impact to the database

Query

Before:

SELECT 
  "security_scans".* 
FROM 
  "security_scans" 
  INNER JOIN "ci_builds" ON "ci_builds"."id" = "security_scans"."build_id" 
  AND "ci_builds"."type" = 'Ci::Build' 
WHERE 
  "ci_builds"."status" = 'success' 
  AND (
    "ci_builds"."retried" = FALSE 
    OR "ci_builds"."retried" IS NULL
  ) 
  AND "security_scans"."scan_type" = 1 
  AND "security_scans"."created_at" BETWEEN '2021-09-27 19:55:50.275002' 
  AND '2021-09-29 19:55:45.806409'

The explain plan generated from #database_lab can be found at the following location https://explain.depesz.com/s/yE1T

After

A query like the following will be made for each scan type (of which there are nine).

SELECT 
  "security_scans".* 
FROM 
  "security_scans" 
WHERE 
  "security_scans"."scan_type" = 1 
  AND "security_scans"."created_at" BETWEEN '2021-09-27 19:55:50.275002' 
  AND '2021-09-29 19:55:45.806409'

This is an expected improvement on the previous query as it doesn't have to join with the ci_builds table and filter by the latest successful build.

The new query searches using the index on scan_type. This query is run against scan_type 1 (SAST), which accounts for the vast majority of Security Scans.

The explain plan generated from #database_lab can be found at the following location https://explain.depesz.com/s/pzXV.

changed milestone to %14.4

added auto updated backend blocked ci-decomposition devopssecure sectionsec typefeature typemaintenance workflowrefinement labels

assigned to @subashis

added 1 commit

• a553d63c - Remove metrics join to ci_builds

Compare with previous version

changed title from Draft: {-Resolve "Fix Security features tables cross-joining ci_builds -> -}Remove [analyzer]_pipeline metrics join to ci_builds" to Draft: Remove [analyzer]_pipeline metrics join on ci_builds"

added databasereview pending label

added product intelligence product intelligencereview pending labels

added database documentation labels

	1 Warning
	For the following files, a review from the Data team and Product Intelligence team is recommended Please check the product intelligence guide. ee/lib/ee/gitlab/usage_data.rb ee/spec/lib/ee/gitlab/usage_data_spec.rb

	1 Message
	This merge request adds or changes documentation files. A review from the Technical Writing team before you merge is recommended. Reviews can happen after you merge.

Documentation review

The following files require a review from a technical writer:

• doc/development/service_ping/implement.md

The review does not need to block merging this merge request. See the:

• Metadata for the *.md files that you've changed. The first few lines of each *.md file identify the stage and group most closely associated with your docs change.
• The Technical Writer assigned for that stage and group.
• Documentation workflows for information on when to assign a merge request for review.

Reviewer roulette

Changes that require review have been detected!

Please refer to the table below for assigning reviewers and maintainers suggested by Danger in the specified category:

Category	Reviewer	Maintainer
backend	Marius Bobin (@mbobin) (UTC+3, 9 hours ahead of @subashis)	Aleksei Lipniagov (@alipniagov) (UTC+3, 9 hours ahead of @subashis)
database	Simon Tomlinson (@stomlinson) (UTC-5, 1 hour ahead of @subashis)	Mayra Cabrera (@mayra-cabrera) (UTC-5, 1 hour ahead of @subashis)

To spread load more evenly across eligible reviewers, Danger has picked a candidate for each review slot, based on their timezone. Feel free to override these selections if you think someone else would be better-suited or use the GitLab Review Workload Dashboard to find other available reviewers.

To read more on how to use the reviewer roulette, please take a look at the Engineering workflow and code review guidelines. Please consider assigning a reviewer or maintainer who is a domain expert in the area of the merge request.

Once you've decided who will review this merge request, assign them as a reviewer! Danger does not automatically notify them for you.

If needed, you can retry the danger-review job that generated this comment.

Generated by Danger

mentioned in commit 3813221e

added 1 commit

• 3813221e - Remove analyzer_pipeline metrics join to ci_builds

Compare with previous version

mentioned in issue #338010 (closed)

Setting label groupthreat insights based on @subashis's group.

added groupthreat insights label

mentioned in commit 2c5d4ef1

added 1 commit

• 2c5d4ef1 - Remove analyzer_pipeline metrics join to ci_builds

Compare with previous version

changed the description

mentioned in commit 608cdf9d

added 1 commit

• 608cdf9d - Remove analyzer_pipeline metrics join to ci_builds

Compare with previous version

mentioned in commit 198e627d