Create project is slow
Summary
Creating a project takes upwards of 60 seconds for me. The user who is creating the project already has several thousand projects spread across several thousand subgroups.
Steps to reproduce
Try to create a project in the above scenario.
Possible fixes
It looks like a fair amount of time is spent on two SQL queries. They are attempting to recompute the user's authorizations from scratch after a project has been created (as opposed to simply inserting a record corresponding to the newly-created project).
The queries:
SELECT project_id, MAX(access_level) AS access_level FROM (SELECT 15 AS user_id, projects.id AS project_id, 40 AS access_level FROM "projects" INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id" WHERE "projects"."pending_delete" = 'f' AND "namespaces"."deleted_at" IS NULL AND "namespaces"."owner_id" = 15 AND "namespaces"."type" IS NULL
UNION
SELECT members.user_id, projects.id AS project_id, members.access_level FROM "projects" INNER JOIN "members" ON "projects"."id" = "members"."source_id" WHERE "projects"."pending_delete" = 'f' AND "members"."source_type" = 'Project' AND "members"."user_id" = 15 AND "members"."type" IN ('ProjectMember') AND "members"."requested_at" IS NULL
UNION
SELECT members.user_id, projects.id AS project_id, members.access_level FROM "projects" INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id" INNER JOIN "members" ON "namespaces"."id" = "members"."source_id" WHERE "projects"."pending_delete" = 'f' AND "namespaces"."deleted_at" IS NULL AND "namespaces"."type" IN ('Group') AND "members"."source_type" = 'Namespace' AND "members"."user_id" = 15 AND "members"."type" IN ('GroupMember') AND "members"."requested_at" IS NULL
UNION
SELECT members.user_id, projects.id AS project_id, members.access_level FROM "projects" INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."deleted_at" IS NULL INNER JOIN "routes" ON "routes"."source_id" = "projects"."id" AND "routes"."source_type" = 'Project' INNER JOIN routes r2 ON routes.path LIKE CONCAT(r2.path, '/%')
INNER JOIN members ON members.source_id = r2.source_id
AND members.source_type = r2.source_type WHERE "projects"."pending_delete" = 'f' AND (namespaces.parent_id IS NOT NULL) AND (members.user_id = 15)
UNION
SELECT members.user_id, projects.id AS project_id, LEAST(project_group_links.group_access, members.access_level) AS access_level FROM "namespaces" INNER JOIN "project_group_links" ON "project_group_links"."group_id" = "namespaces"."id" INNER JOIN "projects" ON "projects"."id" = "project_group_links"."project_id" AND "projects"."pending_delete" = 'f' INNER JOIN "members" ON "namespaces"."id" = "members"."source_id" INNER JOIN namespaces project_namespace ON project_namespace.id = projects.namespace_id WHERE "namespaces"."deleted_at" IS NULL AND "members"."source_type" = 'Namespace' AND "namespaces"."type" IN ('Group') AND "members"."user_id" = 15 AND "members"."type" IN ('GroupMember') AND "members"."requested_at" IS NULL AND (project_namespace.share_with_group_lock = 'f')
UNION
SELECT members.user_id, projects.id AS project_id, LEAST(project_group_links.group_access, members.access_level) AS access_level FROM "namespaces" INNER JOIN "routes" ON "routes"."source_id" = "namespaces"."id" AND "routes"."source_type" = 'Namespace' INNER JOIN "project_group_links" ON "project_group_links"."group_id" = "namespaces"."id" INNER JOIN "projects" ON "projects"."id" = "project_group_links"."project_id" AND "projects"."pending_delete" = 'f' INNER JOIN routes r2 ON routes.path LIKE CONCAT(r2.path, '/%')
INNER JOIN members ON members.source_id = r2.source_id
AND members.source_type = r2.source_type INNER JOIN namespaces project_namespace ON project_namespace.id = projects.namespace_id WHERE "namespaces"."deleted_at" IS NULL AND "namespaces"."type" IN ('Group') AND (members.user_id = 15) AND (project_namespace.share_with_group_lock = 'f')) project_authorizations GROUP BY "project_id";and
SELECT members.user_id, projects.id AS project_id, members.access_level FROM "projects" INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."deleted_at" IS NULL INNER JOIN "routes" ON "routes"."source_id" = "projects"."id" AND "routes"."source_type" = 'Project' INNER JOIN routes r2 ON routes.path LIKE CONCAT(r2.path, '/%') INNER JOIN members ON members.source_id = r2.source_id AND members.source_type = r2.source_type WHERE "projects"."pending_delete" = 'f' AND (namespaces.parent_id IS NOT NULL) AND (members.user_id = 15);It appears that the second query, at least (I haven't looked at the first) is not correctly using the index index_routes_on_path_text_pattern_ops. Consider the following nearly-equivalent query:
SELECT members.user_id, projects.id AS project_id, members.access_level FROM "projects" INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."deleted_at" IS NULL INNER JOIN "routes" ON "routes"."source_id" = "projects"."id" AND "routes"."source_type" = 'Project' INNER JOIN routes r2 ON routes.path >= CONCAT(r2.path, '/') AND routes.path <= CONCAT(r2.path, '/\FFFF') INNER JOIN members ON members.source_id = r2.source_id AND members.source_type = r2.source_type WHERE "projects"."pending_delete" = 'f' AND (namespaces.parent_id IS NOT NULL) AND (members.user_id = 15);This runs much faster, and I think does about the same thing (in that a path component isn't going to start with an emoji). But it's also a dirty hack around the badness of postgresql's query optimizer (I'm using PostgreSQL 9.6.1). So, I guess it might be worth reporting upstream if it turns out that I'm not crazy that these are equivalent.
Edited by 🤖 GitLab Bot 🤖