Issue and merge request counts on nav pages are slow due to poorly indexed queries

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

• Close this issue

Loading the issue and MR counts in the nav bar below for GitLab CE takes about a 100 ms:

D, [2017-02-06T05:57:58.148330 #42656] DEBUG -- :    (71.2ms)  SELECT COUNT(*) FROM "issues" WHERE "issues"."deleted_at" IS NULL AND (issues.confidential IS NULL OR issues.confidential IS FALSE) AND "issues"."project_id" = 13083 AND ("issues"."state" IN ('opened','reopened'))
D, [2017-02-06T05:57:58.223922 #42656] DEBUG -- :    (23.4ms)  SELECT COUNT(*) FROM "merge_requests" WHERE "merge_requests"."deleted_at" IS NULL AND "merge_requests"."target_project_id" = 13083 AND ("merge_requests"."state" IN ('opened','reopened'))

The bitmap heap scan for this first query is still a bit slow: https://explain.depesz.com/s/xyi

I see a number of options:

1. Omit the counts entirely
2. Cache the issue count and MR counts per user. Invalidation is a bit tricky because we would have to invalidate this anytime any permissions were changed, confidential status were updated, etc.
3. Create a full index that covers the WHERE clause:

add_concurrent_index :issues, [:project_id, :deleted_at, :state]
add_concurrent_index :merge_requests, [:target_project_id, :deleted_at, :state]

This seems simpler.

What do you think, @yorickpeterse?