Skip to content

Fix performance problem of tags query

Kamil Trzciński requested to merge fix-performance-problem-of-tags-query into master

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?

What are the relevant issue numbers?

Closes https://gitlab.com/gitlab-com/infrastructure/issues/4550

Edited by Yorick Peterse

Merge request reports