Skip to content

Use partial GIN index for non-latin issue searches

Heinrich Lee Yu requested to merge 364556-issues-partial-gin-index-attempt-2 into master

What does this MR do and why?

This is the 2nd attempt to introduce !92739 (merged). The regex has been updated to the correct one and these indexes have been created on GitLab.com with !102353 (merged).

Verified on dblab:

gitlabhq_dblab-# \d index_issues_on_title_trigram_non_latin
    Index "public.index_issues_on_title_trigram_non_latin"
 Column |  Type   | Key? | Definition | Storage | Stats target
--------+---------+------+------------+---------+--------------
 title  | integer | yes  | title      | plain   |
gin, for table "public.issues", predicate (title::text !~ similar_escape('[\u0000-\u02FF\u1E00-\u1EFF\u2070-\u218F]*'::text, NULL::text) OR description !~ similar_escape('[\u0000-\u02FF\u1E00-\u1EFF\u2070-\u218F]*'::text, NULL::text))

gitlabhq_dblab-# \d index_issues_on_description_trigram_non_latin
    Index "public.index_issues_on_description_trigram_non_latin"
   Column    |  Type   | Key? | Definition  | Storage | Stats target
-------------+---------+------+-------------+---------+--------------
 description | integer | yes  | description | plain   |
gin, for table "public.issues", predicate (title::text !~ similar_escape('[\u0000-\u02FF\u1E00-\u1EFF\u2070-\u218F]*'::text, NULL::text) OR description !~ similar_escape('[\u0000-\u02FF\u1E00-\u1EFF\u2070-\u218F]*'::text, NULL::text))

Sample query with non-english search terms

Before: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13090/commands/45911

After: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13090/commands/45908

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 #364556 (closed)

Edited by euko

Merge request reports