Projects::ProjectMembersController#index is slow due to SQL
Seems to be due primarily to two queries:
8795.042ms SELECT "members".* FROM "members" LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id" WHERE (members.id IN (SELECT "members"."id" FROM "members" WHERE "members"."source_type" = 'Project' AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = 13083 AND "members"."source_type" = 'Project' AND "members"."type" IN ('ProjectMember') AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL) OR members.id IN (SELECT "members"."id" FROM "members" WHERE "members"."source_type" = 'Namespace' AND "members"."type" IN ('GroupMember') AND "members"."source_id" = 9970 AND "members"."source_type" = 'Namespace' AND "members"."type" IN ('GroupMember') AND "members"."requested_at" IS NULL AND ("members"."user_id" NOT IN (SELECT "members"."user_id" FROM "members" WHERE "members"."source_type" = 'Project' AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = 13083 AND "members"."source_type" = 'Project' AND "members"."type" IN ('ProjectMember') AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL AND ("members"."user_id" IS NOT NULL))) AND "members"."invite_token" IS NULL)) ORDER BY users.name ASC NULLS LAST LIMIT 20 OFFSET 0
1350.696ms SELECT COUNT(DISTINCT "members"."id") FROM "members" LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id" WHERE (members.id IN (SELECT "members"."id" FROM "members" WHERE "members"."source_type" = 'Project' AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = 13083 AND "members"."source_type" = 'Project' AND "members"."type" IN ('ProjectMember') AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL) OR members.id IN (SELECT "members"."id" FROM "members" WHERE "members"."source_type" = 'Namespace' AND "members"."type" IN ('GroupMember') AND "members"."source_id" = 9970 AND "members"."source_type" = 'Namespace' AND "members"."type" IN ('GroupMember') AND "members"."requested_at" IS NULL AND ("members"."user_id" NOT IN (SELECT "members"."user_id" FROM "members" WHERE "members"."source_type" = 'Project' AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = 13083 AND "members"."source_type" = 'Project' AND "members"."type" IN ('ProjectMember') AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL AND ("members"."user_id" IS NOT NULL))) AND "members"."invite_token" IS NULL))
In addition to those two queries there's this query which is executed repeatedly for each of the user ids listed:
SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
And even worse, there are these two queries which are executed repeatedly verbatim for each of the users listed:
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 9970 LIMIT 1
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 9970 AND "routes"."source_type" = 'Namespace' LIMIT 1
That is, the exact same query is being executed 18 times for each page load.
Edited by Gregory Stark