Reduce number of queries on Users table (!59655) · Merge requests · GitLab.org / GitLab

What does this MR do?

The MR fixes a large number of lookups in the users table that occurs when a user is referenced next to a checkbox in an issue description. For example, the description of #321817 (closed) has a large number of checkboxes with labels and a reference to a user. If we look at the generated SQL when the update action is performed, we can see that there are over 203 select statements executed with the following pattern:

SELECT "users"."id" FROM "users" WHERE "users"."id" = $1

The reason for this duplicate query is because of the following line from cache_markdown_field.rb:

references[:mentioned_users_ids] = refs.mentioned_users&.pluck(:id).presence

Each time a checkbox is updated, a new note is created, and the above line of code is executed which eventually calls lib/banzai/reference_parser/base_parser.rb#referenced_by(nodes) which is where the query is triggered:

def referenced_by(nodes)
  ids = unique_attribute_values(nodes, self.class.data_attribute)

  if ids.empty?
    references_relation.none
  else
    // User DB lookup is triggered here
    references_relation.where(id: ids)
  end
end

We can remove these duplicate queries by passing in the user ids at a higher level, which is what this MR does.

TODO:

Add tests

What are the relevant issue numbers?

#4794 (closed)

Database

Before

1.  User Load   SELECT "users".* FROM "users" WHERE "users". LIMIT 1 
2.  User Load   SELECT "users".* FROM "users" WHERE "users"."id" = 3 
3.     SELECT "users"."id" FROM "users" WHERE "users"."id" = 2 
4.  User Load   SELECT "users".* FROM "users" WHERE "users"."id" = 2 
5.     SELECT "users"."id" FROM "users" WHERE "users"."id" = 1 
6.  User Load   SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 1) 
7.  User Load   SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1 
8.  User Load   SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1 
9.  User Load   SELECT "users".* FROM "users" WHERE "users"."id" = 3 
10. User Load   SELECT "users".* FROM "users" WHERE "users"."id" = 6 
11. User Load   SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 1) 
12. User Load   SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 1) 
13.    SELECT "users"."id" FROM "users" WHERE "users"."id" = 1 
14.    SELECT "users"."id" FROM "users" WHERE "users"."id" = 1 
15.    SELECT "users"."id" FROM "users" WHERE "users"."id" = 1 
16. User Load   SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 1)

After

1.  User Load   SELECT "users".* FROM "users" WHERE "users". LIMIT 1 
2.  User Load   SELECT "users".* FROM "users" WHERE "users"."id" = 3 
3.  User Load   SELECT "users".* FROM "users" WHERE "users"."id" = 2 
4.  User Load   SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 1) 
5.  User Load   SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1 
6.  User Load   SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1 
7.  User Load   SELECT "users".* FROM "users" WHERE "users"."id" = 3 
8.  User Load   SELECT "users".* FROM "users" WHERE "users"."id" = 6 
9.  User Load   SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 1) 
10. User Load   SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 1) 
11. User Load   SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 1)

Diff:

-   SELECT "users"."id" FROM "users" WHERE "users"."id" = 2 
-   SELECT "users"."id" FROM "users" WHERE "users"."id" = 1 
-   SELECT "users"."id" FROM "users" WHERE "users"."id" = 1 
-   SELECT "users"."id" FROM "users" WHERE "users"."id" = 1 
-   SELECT "users"."id" FROM "users" WHERE "users"."id" = 1

5 queries removed in this case, although the number of queries is related to how many user references/checkboxes there are in an issue description. This can result in hundreds of queries, such as the case in this issue

Does this MR meet the acceptance criteria?

Conformity

📋 Does this MR need a changelog?
- I have included a changelog entry.
- I have not included a changelog entry because _____.
Documentation (if required)
Code review guidelines
Merge request performance guidelines
Style guides
Database guides
Separation of EE specific content

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers
Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

Related to #4794 (closed)

Reduce number of queries on Users table