Use PG full-text search for searching issues
What does this MR do and why?
We have pretty frequent DB timeouts when searching issues in large groups. This MR attempts to fix that by using PG full-text search.
This is cheaper / faster than trigram searches because there are far less words / lexemes in a blob of text compared to trigrams. Storing them in a single column with different weights also simplifies the query because it won't have to do multiple OR clauses.
It's difficult to get sample queries for this because data has to be backfilled. Samples are in #273784 (comment 600401233) and I'll try to recreate the dblab instance again for this review.
Downside of full-text search is that it is language dependent. Right now, I'm using the english dictionary which works reasonably well even with non-english languages as long as the language uses space-delimited words.
This does not work well with languages like Chinese or Japanese because words aren't separated by spaces. For this reason, when the search term contains a non-Latin character, we fall back to the current search behavior.
Sample query plans
-
ILIKE: https://explain.depesz.com/s/Ej2n
Planning time: 6.148 ms Execution time: 5,148.261 ms
-
PG FTS: https://explain.depesz.com/s/nsIa
Planning time: 6.710 ms Execution time: 747.904 ms
How to set up and validate locally
- Checkout this branch and run the migrations. (It should run the background migration inline)
- Enable the
issues_full_text_search
feature flag - Search for issues in the project / group / dashboard issue list.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #273784 (closed)