Comparison of timings of query used to find Projects
To verify the improvements made in gitlab-org/gitlab-ce!11666 are actually improvements. I'd like to see timings on these queries:
The user_id
= 660180
used, is my own.
a) Old query used in /api/v4/projects?visibility=private
:
SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = FALSE AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 660180
UNION
SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND "projects"."visibility_level" = 0 AND "projects"."archived" = FALSE ORDER BY "projects"."created_at" DESC LIMIT 20 OFFSET 0
b) New query used in /api/v4/projects?visibility=private
:
SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = FALSE AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 660180 AND "projects"."visibility_level" = 0 AND "projects"."archived" = 'f'
UNION
SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10) AND "projects"."visibility_level" = 0 AND "projects"."archived" = 'f')) ORDER BY "projects"."created_at" DESC LIMIT 20 OFFSET 0
c) Old query used in /api/v4/projects?membership=true
:
SELECT "projects".* FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = 'f' AND "project_authorizations"."user_id" = 660180
UNION
SELECT "projects"."id" FROM "projects" WHERE "projects"."visibility_level" IN (20, 10))) AND "projects"."pending_delete" = FALSE AND "project_authorizations"."user_id" = 660180 AND "projects"."archived" = FALSE ORDER BY "projects"."created_at" DESC LIMIT 20 OFFSET 0
d) New query used in /api/v4/projects?membership=true
:
SELECT "projects".* FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "projects"."pending_delete" = FALSE AND "project_authorizations"."user_id" = 660180 AND "projects"."archived" = FALSE ORDER BY "projects"."created_at" DESC LIMIT 20 OFFSET 0
Edited by Toon Claes