Fix performance problem of tags query
What does this MR do?
This fixes a problem described in https://gitlab.com/gitlab-com/infrastructure/issues/4550#note_87232992.
Accessing /api/v4/users/:user_id/projects
results in having this query:
SELECT "taggings".* FROM "taggings" WHERE "taggings"."tag_id" IN (15755, 4060)
It happens because of using Project.preload(tags: :taggings)
. This causes Rails to go crazy with query preloading:
SQL (0.4ms) SELECT "taggings".*, "taggings"."id" AS t0_r0, "taggings"."tag_id" AS t0_r1, "taggings"."taggable_id" AS t0_r2, "taggings"."taggable_type" AS t0_r3, "taggings"."tagger_id" AS t0_r4, "taggings"."tagger_type" AS t0_r5, "taggings"."context" AS t0_r6, "taggings"."created_at" AS t0_r7, "tags"."id" AS t1_r0, "tags"."name" AS t1_r1, "tags"."taggings_count" AS t1_r2 FROM "taggings" LEFT OUTER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."context" = $1 AND "taggings"."taggable_type" = 'Project' AND "taggings"."taggable_id" IN (15, 14) [["context", "tags"]]
ActsAsTaggableOn::Tagging Load (0.3ms) SELECT "taggings".* FROM "taggings" WHERE "taggings"."tag_id" IN (5)
It makes the second query fully inefficient, and not needed at all.
Fixing it to look like Project.preload(:tags)
. This makes Rails to work correctly:
SQL (0.3ms) SELECT "taggings".*, "taggings"."id" AS t0_r0, "taggings"."tag_id" AS t0_r1, "taggings"."taggable_id" AS t0_r2, "taggings"."taggable_type" AS t0_r3, "taggings"."tagger_id" AS t0_r4, "taggings"."tagger_type" AS t0_r5, "taggings"."context" AS t0_r6, "taggings"."created_at" AS t0_r7, "tags"."id" AS t1_r0, "tags"."name" AS t1_r1, "tags"."taggings_count" AS t1_r2 FROM "taggings" LEFT OUTER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."context" = $1 AND "taggings"."taggable_type" = 'Project' AND "taggings"."taggable_id" IN (15, 14) [["context", "tags"]]
We have the information that we need, which is tags.name
that is required to solve the N+1 problem:
https://gitlab.com/gitlab-org/gitlab-ce/blob/master/lib/api/entities.rb#L126
Does this MR meet the acceptance criteria?
-
Changelog entry added, if necessary -
Tests added for this feature/bug - Conform by the code review guidelines
-
Has been reviewed by a Backend maintainer -
Has been reviewed by a Database specialist
-
-
Conform by the merge request performance guides -
Conform by the style guides -
Conform by the database guides -
End-to-end tests pass ( package-and-qa
manual pipeline job)
What are the relevant issue numbers?
Closes https://gitlab.com/gitlab-com/infrastructure/issues/4550
Edited by Yorick Peterse