Skip to content

Add issues_created_gitlab_alerts to usage ping

Allison Browne requested to merge ab-north-star-usage-ping-2 into master

What does this MR do?

Counts the number of issues created from alerts where the the issue was not automatically created by the alert bot (manually created by a user).

See explain plans below. The execution times look good but we are using a sequential scan.

explain SELECT MIN("issues"."id") FROM "issues" INNER JOIN "alert_management_alerts" ON "alert_management_alerts"."issue_id" = "issues"."id" WHERE "issues"."author_id" != 4002669;

  
Time: 0.427 ms
  - planning: 0.358 ms
  - execution: 0.069 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 2 (~16.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
  
  
Aggregate  (cost=1176.65..1176.66 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)
   Buffers: shared hit=2
   ->  Nested Loop  (cost=0.71..1176.02 rows=250 width=4) (actual time=0.012..0.012 rows=0 loops=1)
         Buffers: shared hit=2
         ->  Index Only Scan using index_alert_management_alerts_on_issue_id on public.alert_management_alerts  (cost=0.14..27.89 rows=250 width=8) (actual time=0.009..0.011 rows=2 loops=1)
               Heap Fetches: 2
               Buffers: shared hit=2
         ->  Index Scan using issues_pkey on public.issues  (cost=0.56..4.58 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=2)
               Index Cond: (issues.id = alert_management_alerts.issue_id)
               Filter: (issues.author_id <> 4002669)
               Rows Removed by Filter: 0

---  
  
explain SELECT MAX("issues"."id") FROM "issues" INNER JOIN "alert_management_alerts" ON "alert_management_alerts"."issue_id" = "issues"."id" WHERE "issues"."author_id" != 4002669

Time: 0.401 ms
  - planning: 0.348 ms
  - execution: 0.053 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 2 (~16.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

 Aggregate  (cost=1176.65..1176.66 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1)
   Buffers: shared hit=2
   ->  Nested Loop  (cost=0.71..1176.02 rows=250 width=4) (actual time=0.011..0.011 rows=0 loops=1)
         Buffers: shared hit=2
         ->  Index Only Scan using index_alert_management_alerts_on_issue_id on public.alert_management_alerts  (cost=0.14..27.89 rows=250 width=8) (actual time=0.007..0.008 rows=2 loops=1)
               Heap Fetches: 2
               Buffers: shared hit=2
         ->  Index Scan using issues_pkey on public.issues  (cost=0.56..4.58 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=2)
               Index Cond: (issues.id = alert_management_alerts.issue_id)
               Filter: (issues.author_id <> 4002669)
               Rows Removed by Filter: 0


---


explain SELECT COUNT("issues"."id") FROM "issues" INNER JOIN "alert_management_alerts" ON "alert_management_alerts"."issue_id" = "issues"."id" WHERE "issues"."author_id" != 4002669 AND "issues"."id" BETWEEN 0 AND 99999

Time: 0.527 ms
  - planning: 0.470 ms
  - execution: 0.057 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 2 (~16.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

 Aggregate  (cost=1176.65..1176.66 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1)
   Buffers: shared hit=2
   ->  Nested Loop  (cost=0.71..1176.02 rows=250 width=4) (actual time=0.011..0.011 rows=0 loops=1)
         Buffers: shared hit=2
         ->  Index Only Scan using index_alert_management_alerts_on_issue_id on public.alert_management_alerts  (cost=0.14..27.89 rows=250 width=8) (actual time=0.007..0.008 rows=2 loops=1)
               Heap Fetches: 2
               Buffers: shared hit=2
         ->  Index Scan using issues_pkey on public.issues  (cost=0.56..4.58 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=2)
               Index Cond: (issues.id = alert_management_alerts.issue_id)
               Filter: (issues.author_id <> 4002669)
               Rows Removed by Filter: 0

Screenshots

Screen_Shot_2020-05-15_at_10.45.39_AM

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 Peter Leitzen

Merge request reports