Revenge of the N+1 in Group::AnalyticsController
In this commit Stan says he removed an N+1 query in Group::AnalyticsController (which since then has moved to EE-only it appears):
commit d757dbc45aedd7519e60aa8c7e5a0e1f58adbb19
Author: Stan Hu <stanhu@gmail.com>
Date: Sun Mar 26 21:21:11 2017 -0700
Remove N+1 queries for Groups::AnalyticsController
Closes gitlab-org/gitlab-ce#22940, #1886
However in the latest profiling at http://profiler.gitlap.com/20170322/900051b7-a235-4c19-b71f-92e116b0be19.txt.gz it seems this code is in fact still doing (or is again doing) n+1 database queries. In fact it's executing 108 repetitions of 3 queries and 216 repetitions of 3 more queries. And just for good measure the individual queries themselves are already fairly slow at 150-250ms each.
108 SELECT COUNT(*) FROM "events" WHERE ("events"."author_id" IS NOT NULL) AND (action = 5 OR (target_type IN ('MergeRequest','Issue') AND action IN (1,3,7)) OR (target_type = 'Note' AND action = 6)) AND (created_at > '2017-03-15') AND "events"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."pending_delete" = 'f' AND "projects"."namespace_id" = 9970 ORDER BY "projects"."id" DESC) AND "events"."author_id" = 9999 [["pending_delete", false], ["namespace_id", 9970]]
108 SELECT COUNT(*) FROM "events" WHERE ("events"."author_id" IS NOT NULL) AND (action = 5 OR (target_type IN ('MergeRequest','Issue') AND action IN (1,3,7)) OR (target_type = 'Note' AND action = 6)) AND (created_at > '2017-03-15') AND "events"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."pending_delete" = 'f' AND "projects"."namespace_id" = 9970 ORDER BY "projects"."id" DESC) AND "events"."target_type" = 'Issue' AND "events"."action" = 1 AND "events"."author_id" = 9999 [["pending_delete", false], ["namespace_id", 9970], ["target_type", "Issue"], ["action", 1]]
108 SELECT COUNT(*) FROM "events" WHERE ("events"."author_id" IS NOT NULL) AND (action = 5 OR (target_type IN ('MergeRequest','Issue') AND action IN (1,3,7)) OR (target_type = 'Note' AND action = 6)) AND (created_at > '2017-03-15') AND "events"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."pending_delete" = 'f' AND "projects"."namespace_id" = 9970 ORDER BY "projects"."id" DESC) AND "events"."target_type" = 'MergeRequest' AND "events"."action" = 7 AND "events"."author_id" = 9999 [["pending_delete", false], ["namespace_id", 9970], ["target_type", "MergeRequest"], ["action", 7]]
216 SELECT COUNT(*) FROM "events" WHERE ("events"."author_id" IS NOT NULL) AND (action = 5 OR (target_type IN ('MergeRequest','Issue') AND action IN (1,3,7)) OR (target_type = 'Note' AND action = 6)) AND (created_at > '2017-03-15') AND "events"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."pending_delete" = 'f' AND "projects"."namespace_id" = 9970 ORDER BY "projects"."id" DESC) AND "events"."action" = 5 AND "events"."author_id" = 9999 [["pending_delete", false], ["namespace_id", 9970], ["action", 5]]
216 SELECT COUNT(*) FROM "events" WHERE ("events"."author_id" IS NOT NULL) AND (action = 5 OR (target_type IN ('MergeRequest','Issue') AND action IN (1,3,7)) OR (target_type = 'Note' AND action = 6)) AND (created_at > '2017-03-15') AND "events"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."pending_delete" = 'f' AND "projects"."namespace_id" = 9970 ORDER BY "projects"."id" DESC) AND "events"."target_type" = 'Issue' AND "events"."action" = 3 AND "events"."author_id" = 9999 [["pending_delete", false], ["namespace_id", 9970], ["target_type", "Issue"], ["action", 3]]
216 SELECT COUNT(*) FROM "events" WHERE ("events"."author_id" IS NOT NULL) AND (action = 5 OR (target_type IN ('MergeRequest','Issue') AND action IN (1,3,7)) OR (target_type = 'Note' AND action = 6)) AND (created_at > '2017-03-15') AND "events"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."pending_delete" = 'f' AND "projects"."namespace_id" = 9970 ORDER BY "projects"."id" DESC) AND "events"."target_type" = 'MergeRequest' AND "events"."action" = 1 AND "events"."author_id" = 9999 [["pending_delete", false], ["namespace_id", 9970], ["target_type", "MergeRequest"], ["action", 1]]
Edited by Gregory Stark