Issues count is slow due to a missing index
When loading issue 1, I see a long SQL query such as the following:
D, [2017-02-04T15:35:21.227217 #10781] DEBUG -- : (306.4ms) SELECT COUNT(*) FROM "labels" WHERE (labels.id IN (SELECT "labels"."id" FROM "labels" WHERE "labels"."type" IN ('GroupLabel') AND "labels"."group_id" = 9970
UNION
SELECT "labels"."id" FROM "labels" WHERE "labels"."type" IN ('ProjectLabel') AND "labels"."project_id" = 13083))
EXPLAIN ANALYZE shows that the second item is slow due a missing index with type
and project_id
. See: https://explain.depesz.com/s/uLbp
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=32967.83..32967.84 rows=1 width=8) (actual time=284.528..284.529 rows=1 loops=1)
-> Nested Loop (cost=30116.14..31604.18 rows=545462 width=0) (actual time=283.606..284.487 rows=140 loops=1)
-> HashAggregate (cost=30115.71..30118.62 rows=291 width=4) (actual time=283.573..283.629 rows=140 loops=1)
Group Key: labels_1.id
-> Append (cost=0.43..30114.99 rows=291 width=4) (actual time=0.027..283.375 rows=140 loops=1)
-> Index Scan using index_labels_on_group_id_and_project_id_and_title on labels labels_1 (cost=0.43..269.22 rows=1 width=4) (actual time=0.026..0.033 rows=4 loops=1)
Index Cond: (group_id = 9970)
Filter: ((type)::text = 'GroupLabel'::text)
Rows Removed by Filter: 7
-> Seq Scan on labels labels_2 (cost=0.00..29842.86 rows=290 width=4) (actual time=0.497..283.236 rows=136 loops=1)
Filter: (((type)::text = 'ProjectLabel'::text) AND (project_id = 13083))
Rows Removed by Filter: 1092114
-> Index Only Scan using labels_pkey on labels (cost=0.43..5.08 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=140)
Index Cond: (id = labels_1.id)
Heap Fetches: 37
Planning time: 0.177 ms
Execution time: 284.598 ms
(17 rows)