Add issues with health status to usage data

What does this MR do?

Adds issues with health status count to usage data.

Query executed:

SELECT COUNT(*) FROM "issues" WHERE "issues"."health_status" IS NOT NULL

An index needed to be created to support the query.

Migration output

Click this to expand.

Up

== 20200406193427 AddIndexToIssuesHealthStatus: migrating =====================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:issues, :health_status, {:where=>"health_status IS NOT NULL", :name=>"idx_issues_on_health_status_not_null", :algorithm=>:concurrently})
   -> 0.0074s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:issues, :health_status, {:where=>"health_status IS NOT NULL", :name=>"idx_issues_on_health_status_not_null", :algorithm=>:concurrently})
   -> 0.0080s
-- execute("RESET ALL")
   -> 0.0005s
== 20200406193427 AddIndexToIssuesHealthStatus: migrated (0.0166s) ============

Down

== 20200406193427 AddIndexToIssuesHealthStatus: reverting =====================
-- transaction_open?()
   -> 0.0000s
-- indexes(:issues)
   -> 0.0072s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- remove_index(:issues, {:algorithm=>:concurrently, :name=>"idx_issues_on_health_status_not_null"})
   -> 0.0021s
-- execute("RESET ALL")
   -> 0.0005s
== 20200406193427 AddIndexToIssuesHealthStatus: reverted (0.0103s) ============

Timings

Click this to expand.

Without index

The query took 45 minutes. There is no need to run the others because they will probably take the same time:

exec SELECT MIN("issues"."id") FROM "issues" WHERE "issues"."health_status" IS NOT NULL

The query has been executed. Duration: 45.097 min

Result  (cost=6564734.11..6564734.12 rows=1 width=4)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.56..6564734.11 rows=1 width=4)
          ->  Index Scan using issues_pkey on issues  (cost=0.56..6564734.11 rows=1 width=4)
                Index Cond: (id IS NOT NULL)
                Filter: (health_status IS NOT NULL)

With index

exec CREATE INDEX idx_issues_on_health_status_not_null
ON public.issues
USING btree
(health_status)
WHERE health_status IS NOT NULL;

The query has been executed. Duration: 14.786 min


exec SELECT MIN("issues"."id") FROM "issues" WHERE "issues"."health_status" IS NOT NULL

The query has been executed. Duration: 160.000 ms

Aggregate  (cost=4.16..4.17 rows=1 width=4) (actual time=0.265..0.265 rows=1 loops=1)
   Buffers: shared hit=109
   ->  Index Scan using idx_issues_on_health_status_not_null on public.issues  (cost=0.14..4.16 rows=1 width=4) (actual time=0.012..0.243 rows=108 loops=1)
         Buffers: shared hit=109



exec SELECT MAX("issues"."id") FROM "issues" WHERE "issues"."health_status" IS NOT NULL

The query has been executed. Duration: 10.000 ms

 Aggregate  (cost=4.16..4.17 rows=1 width=4) (actual time=0.220..0.220 rows=1 loops=1)
   Buffers: shared hit=109
   ->  Index Scan using idx_issues_on_health_status_not_null on public.issues  (cost=0.14..4.16 rows=1 width=4) (actual time=0.011..0.205 rows=108 loops=1)
         Buffers: shared hit=109



exec SELECT COUNT("issues"."id") FROM "issues" WHERE "issues"."health_status" IS NOT NULL AND "issues"."id" BETWEEN 0 AND 99999

The query has been executed. Duration: 134.000 ms

Aggregate  (cost=4.17..4.17 rows=1 width=8) (actual time=0.367..0.367 rows=1 loops=1)
   Buffers: shared hit=109
   ->  Index Scan using idx_issues_on_health_status_not_null on public.issues  (cost=0.14..4.16 rows=1 width=4) (actual time=0.356..0.356 rows=0 loops=1)
         Filter: ((issues.id >= 0) AND (issues.id <= 99999))
         Rows Removed by Filter: 108
         Buffers: shared hit=109

Related to #211837 (closed)