Skip to content

Use PG full-text search for searching issues

Heinrich Lee Yu requested to merge 273784-issues-full-text-search into master

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

  1. ILIKE: https://explain.depesz.com/s/Ej2n

    Planning time: 6.148 ms 
    Execution time: 5,148.261 ms
  2. 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

  1. Checkout this branch and run the migrations. (It should run the background migration inline)
  2. Enable the issues_full_text_search feature flag
  3. 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.

Related to #273784 (closed)

Edited by Heinrich Lee Yu

Merge request reports