Issue suggestion through GraphQL creates slow database queries that time out

Summary

GraphQL controller generates long running DB queries that timeout when the user types more than a few words into the title input when creating a new issue.

• Sentry: https://sentry.gitlab.net/gitlab/gitlabcom/issues/958072/
• Example in Kibana: https://log.gitlab.net/app/kibana#/doc/AWOSvARQwig0Nc2UGcr2/pubsub-rails-inf-gprd-2019.10.16-000008/doc?id=AW3TlSjUDkDQm8v5dp5-&_g=h@91bd11d

Since this request is scoped by project and limited to 5 results it seems the mostly likely reason is multiple ILIKE clauses across issues.title and issues.description.

Here's an example of the SQL run for a request I made:

SELECT  "issues".* FROM "issues" 
  WHERE "issues"."project_id" = 278964 
    AND (
      "issues"."title" ILIKE '%Give%' AND "issues"."title" ILIKE '%issue%' 
        AND "issues"."title" ILIKE '%new%' AND "issues"."title" ILIKE '%title%'
        AND "issues"."title" ILIKE '%now%'
        OR "issues"."description" ILIKE '%Give%' AND "issues"."description" ILIKE '%issue%'
        AND "issues"."description" ILIKE '%new%' AND "issues"."description" ILIKE '%title%'
        AND "issues"."description" ILIKE '%now%'
    )
  ORDER BY "issues"."updated_at" DESC, "issues"."id" DESC 
  LIMIT 5

Steps to reproduce

1. Open Developer tools > Network (or equivalent)
2. Go to project with a lot of issues (e.g. gitlab-org/gitlab)
3. Open a new issue
4. Start typing the title, giving it more than four or five words
5. Observe /api/graphql request timing out after ~15s

Example Project

https://gitlab.com/gitlab-org/gitlab/issues/new

What is the current bug behavior?

The database is tied up on a request that ultimately times out anyway. The user's browser handles the 500 silently, but the experience is that suggested issues aren't as relevant as they could be and the list stops updating after 4 words are entered.

What is the expected correct behavior?

Request should return in a timely manner so that the list of suggested issues appears to be almost real-time with text entered.

Relevant logs and/or screenshots

In the screenshot below you can see how the requests for the first two words work OK, the third is slow and the fourth (and subsequent) 500.

Output of checks

This bug happens on GitLab.com

Possible fixes

There are a number of possible fixes/mitigations:

• Pass attempt_project_search_optimizations: true to the finder from the GraphQL issues resolver;
• Shorter timeout (separate issue for timeouts here #31225 (closed));
• Limit searches to three words (though this maintains the poor user experience);
• Test the performance of alternative, case-insensitive search expressions; e.g. LOWER() vs ILIKE;
• Investigate Postgres Full Text search, or
• Use Elasticsearch instead (!).

/cc @cablett @digitalmoksha Would appreciate your views on this!