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
- Open Developer tools > Network (or equivalent)
- Go to project with a lot of issues (e.g. gitlab-org/gitlab)
- Open a new issue
- Start typing the title, giving it more than four or five words
- 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()
vsILIKE
; - Investigate Postgres Full Text search, or
- Use Elasticsearch instead (!).
/cc @cablett @digitalmoksha Would appreciate your views on this!