Add total and closed counters to issues analytics (!46444) · Merge requests · GitLab.org / GitLab

What does this MR do?

Adds API support for accumulated open issues and closed issues data at Issues Analytics chart. To enable new behavior you must enable :new_issues_analytics_chart_data feature flag

Feature.enable(:new_issues_analytics_chart_data)

Migration output

== 20201103095752 AddIssuesClosedAtIndex: migrating ===========================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:issues, [:project_id, :closed_at], {:algorithm=>:concurrently})
   -> 0.0341s
-- add_index(:issues, [:project_id, :closed_at], {:algorithm=>:concurrently})
   -> 0.0218s
== 20201103095752 AddIssuesClosedAtIndex: migrated (0.0571s) ==================

== 20201103095752 AddIssuesClosedAtIndex: reverting ===========================
-- transaction_open?()
   -> 0.0000s
-- indexes(:issues)
   -> 0.0449s
-- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_issues_on_project_id_and_closed_at"})
   -> 0.0064s
== 20201103095752 AddIssuesClosedAtIndex: reverted (0.0528s) ==================

Queries explains

Explain for created_at

explain SELECT date_trunc('month', created_at)::date as month, count(*) as counter FROM "issues" WHERE "issues"."project_id" = 278964 AND (issues.created_at >= '2019-10-01') GROUP BY month

 HashAggregate  (cost=1988.72..2679.26 rows=46036 width=12) (actual time=50.764..50.995 rows=13 loops=1)
   Group Key: (date_trunc('month'::text, issues.created_at))::date
   Buffers: shared hit=38521
   ->  Index Only Scan using idx_issues_on_project_id_and_created_at_and_id_and_state_id on public.issues  (cost=0.56..1758.12 rows=46120 width=4) (actual time=0.030..39.473 rows=36829 loops=1)
         Index Cond: ((issues.project_id = 278964) AND (issues.created_at >= '2019-10-01 00:00:00+00'::timestamp with time zone))
         Heap Fetches: 1532
         Buffers: shared hit=38521

Time: 51.734 ms
  - planning: 0.413 ms
  - execution: 51.321 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

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

Explain for closed_at

explain SELECT date_trunc('month', closed_at)::date as month, count(*) as counter FROM "issues" WHERE "issues"."project_id" = 278964 AND (issues.closed_at >= '2019-10-01') GROUP BY month

HashAggregate  (cost=480.07..608.14 rows=8538 width=12) (actual time=1240.350..1240.432 rows=13 loops=1)
   Group Key: (date_trunc('month'::text, issues.closed_at))::date
   Buffers: shared hit=23205 read=765
   I/O Timings: read=1159.211
   ->  Index Only Scan using issues_project_id_closed_at_idx on public.issues  (cost=0.56..437.37 rows=8540 width=4) (actual time=1.118..1230.464 rows=24814 loops=1)
         Index Cond: ((issues.project_id = 278964) AND (issues.closed_at >= '2019-10-01 00:00:00+00'::timestamp with time zone))
         Heap Fetches: 825
         Buffers: shared hit=23205 read=765
         I/O Timings: read=1159.211

Time: 1.242 s
  - planning: 0.786 ms
  - execution: 1.241 s
    - I/O read: 1.159 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 23205 (~181.30 MiB) from the buffer pool
  - reads: 765 (~6.00 MiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Explain for accumulated open

explain SELECT COUNT(*) FROM "issues" WHERE "issues"."project_id" = 278964 AND ("issues"."state_id" IN (1)) AND (issues.created_at < '2019-11-01')

Aggregate  (cost=776.81..776.82 rows=1 width=8) (actual time=279.738..279.740 rows=1 loops=1)
   Buffers: shared hit=17930 read=339 dirtied=9
   I/O Timings: read=225.612
   ->  Index Only Scan using idx_issues_on_project_id_and_created_at_and_id_and_state_id on public.issues  (cost=0.56..739.30 rows=15007 width=0) (actual time=2.351..277.395 rows=18378 loops=1)
         Index Cond: ((issues.project_id = 278964) AND (issues.created_at < '2019-11-01 00:00:00+00'::timestamp with time zone) AND (issues.state_id = 1))
         Heap Fetches: 566
         Buffers: shared hit=17930 read=339 dirtied=9
         I/O Timings: read=225.612


Time: 11.589 ms
  - planning: 0.302 ms
  - execution: 11.287 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

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

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

Related to #247271 (closed)

Add total and closed counters to issues analytics