Improve performance of searches by lowering counters

Summary

At the moment, global searches without any project or group, time out most of the times (with only a search term as search criteria). We're trying different architectural options to fix this, like ElasticSearch, but, in the meantime, there are several things we can do to improve it.

Global search performs all these operations when we use it:

• Count all projects that match the search term (with a Limit value of 1001)
• Count all issues that match the search term (with a Limit value of 1001) (depending on the results we may perform 2 count operations here)
• Count all merge requests that match the search term (with a Limit value of 1001)
• Count all milestones that match the search term (with a Limit value of 1001)
• Count all users that match the search term (with a Limit value of 1001)

Then we need to perform a SELECT operation depending on the scope.

The time spent on each operation is:

• Projects: 67ms (https://explain.depesz.com/s/wRnU)
• Issues: 87ms + 6.2s (https://explain.depesz.com/s/zEIX and https://explain.depesz.com/s/QqCS)
• Merge requests: 355ms (https://explain.depesz.com/s/xVcU)
• Milestones: 13ms (https://explain.depesz.com/s/T8k)
• Users: 32ms (https://explain.depesz.com/s/uLlB)

The SELECT operation is quicker because the LIMIT value is 21 but, for extrapolation, let's say we use the same LIMIT as in counts (1001) and a 4 chars length term. This operation takes 6.349s (https://explain.depesz.com/s/bL49). The other scopes are quicker than this one.

Improvements

There are a couple of quick fixes that will help us to improve the response time:

• Lower the LIMIT value to 101, for example. With this value, we can cut almost in half the time spent in the issue count operation. From 6.2s to 3.849s (https://explain.depesz.com/s/fuNg).
• Set a min search term length. At the moment, we allow as a search term, a single char which, due to our DB size, is going to time out the request for sure. We should enforce, at least, a search term with 3 chars (I would even increase it to 4 or even 5). Here is a small comparison depending on the length of the search term:
  • 4 chars: 6.349s (https://explain.depesz.com/s/bL49)
  • 5 chars: 5.782s (https://explain.depesz.com/s/b4Pe)
  • 6 chars: 4.371s (https://explain.depesz.com/s/gIbPs)

In order to set the guard clause in the search term, we would need to return and show some kind of error to the user.

Update

We can't set a max length in the search term because we allow some resources to have a name length below that limit. For example, projects, users, they don't have a name limit. If we set the limit we won't ever be able to find those resources in searches.

Risks

Changes in the SearchController may break the global search.

Involved components

• SearchController
• SearchService
• The related views.

/cc @phikai @DouweM