Filter issues / merge request fuzzy search those comments

What does this MR do?

Filter issues / merge requests fuzzy search those comments.

Are there points in the code the reviewer needs to double check?

  • SQL performance

Test data

Data
gitlabhq_development=# SELECT COUNT(*) FROM issues;
 count
-------
 12333

gitlabhq_development=# SELECT COUNT(*) FROM notes; count

999

Query without project

The following is EXPLAIN ANALYSE output made by Issue.full_search_with_comments("foo bar")

Query
EXPLAIN ANALYZE SELECT "issues".*
FROM "issues"
WHERE "issues"."deleted_at" IS NULL
  AND (issues.id IN
         (SELECT "issues"."id"
          FROM "issues"
          WHERE "issues"."deleted_at" IS NULL
            AND ("issues"."title" ILIKE '%foo%'
                 AND "issues"."title" ILIKE '%bar%'
                 OR "issues"."description" ILIKE '%foo%'
                 AND "issues"."description" ILIKE '%bar%')
          UNION SELECT "notes"."noteable_id"
          FROM "notes"
          WHERE "notes"."noteable_type" = 'Issue'
            AND ("notes"."note" ILIKE '%foo%'
                 AND "notes"."note" ILIKE '%bar%')))
ORDER BY "issues"."id" DESC;
Query plan
                                                                                             QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=108.73..108.74 rows=2 width=347) (actual time=0.105..0.105 rows=0 loops=1)
   Sort Key: issues.id DESC
   Sort Method: quicksort  Memory: 25kB
   ->  Nested Loop  (cost=92.35..108.72 rows=2 width=347) (actual time=0.097..0.097 rows=0 loops=1)
         ->  Unique  (cost=92.07..92.08 rows=2 width=4) (actual time=0.097..0.097 rows=0 loops=1)
               ->  Sort  (cost=92.07..92.07 rows=2 width=4) (actual time=0.097..0.097 rows=0 loops=1)
                     Sort Key: issues_1.id
                     Sort Method: quicksort  Memory: 25kB
                     ->  Append  (cost=48.00..92.06 rows=2 width=4) (actual time=0.089..0.089 rows=0 loops=1)
                           ->  Bitmap Heap Scan on issues issues_1  (cost=48.00..52.02 rows=1 width=4) (actual time=0.040..0.040 rows=0 loops=1)
                                 Recheck Cond: ((((title)::text ~~* '%foo%'::text) AND ((title)::text ~~* '%bar%'::text)) OR ((description ~~* '%foo%'::text) AND (description ~~* '%bar%'::text)))
                                 Filter: (deleted_at IS NULL)
                                 ->  BitmapOr  (cost=48.00..48.00 rows=1 width=0) (actual time=0.039..0.039 rows=0 loops=1)
                                       ->  Bitmap Index Scan on index_issues_on_title_trigram  (cost=0.00..24.00 rows=1 width=0) (actual time=0.022..0.022 rows=0 loops=1)
                                             Index Cond: (((title)::text ~~* '%foo%'::text) AND ((title)::text ~~* '%bar%'::text))
                                       ->  Bitmap Index Scan on index_issues_on_description_trigram  (cost=0.00..24.00 rows=1 width=0) (actual time=0.014..0.014 rows=0 loops=1)
                                             Index Cond: ((description ~~* '%foo%'::text) AND (description ~~* '%bar%'::text))
                           ->  Bitmap Heap Scan on notes  (cost=36.00..40.02 rows=1 width=4) (actual time=0.049..0.049 rows=0 loops=1)
                                 Recheck Cond: ((note ~~* '%foo%'::text) AND (note ~~* '%bar%'::text))
                                 Filter: ((noteable_type)::text = 'Issue'::text)
                                 Rows Removed by Filter: 1
                                 Heap Blocks: exact=1
                                 ->  Bitmap Index Scan on index_notes_on_note_trigram  (cost=0.00..36.00 rows=1 width=0) (actual time=0.034..0.034 rows=1 loops=1)
                                       Index Cond: ((note ~~* '%foo%'::text) AND (note ~~* '%bar%'::text))
         ->  Index Scan using issues_pkey on issues  (cost=0.29..8.30 rows=1 width=347) (never executed)
               Index Cond: (id = issues_1.id)
               Filter: (deleted_at IS NULL)
 Planning time: 1.222 ms
 Execution time: 0.480 ms

Query with project

The following is EXPLAIN ANALYSE output made by IssuesFinder.new(user, params).execute

=> #<IssuesFinder:0x007f9a9ea0c248
 @current_user=#<User id:1 @root>,
 @params={"project_id"=>1, "search"=>"foo bar"}>
Query
SELECT "issues".* FROM "issues" WHERE "issues"."deleted_at" IS NULL AND (issues.id IN (SELECT "issues"."id" FROM "issues" WHERE "issues"."deleted_at" IS NULL AND ("issues"."title" ILIKE '%foo%' AND "issues"."title" ILIKE '%bar%' OR "issues"."description" ILIKE '%foo%' AND "issues"."description" ILIKE '%bar%')
UNION
SELECT "notes"."noteable_id" FROM "notes" WHERE "notes"."noteable_type" = 'Issue' AND ("notes"."note" ILIKE '%foo%' AND "notes"."note" ILIKE '%bar%'))) AND "issues"."project_id" = 1  ORDER BY "issues"."id" DESC
Query plan
                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=120.74..120.74 rows=1 width=347) (actual time=0.204..0.205 rows=4 loops=1)
   Sort Key: issues.id DESC
   Sort Method: quicksort  Memory: 26kB
   ->  Nested Loop  (cost=104.35..120.73 rows=1 width=347) (actual time=0.169..0.186 rows=4 loops=1)
         ->  Unique  (cost=104.07..104.08 rows=2 width=4) (actual time=0.156..0.158 rows=4 loops=1)
               ->  Sort  (cost=104.07..104.07 rows=2 width=4) (actual time=0.155..0.155 rows=4 loops=1)
                     Sort Key: issues_1.id
                     Sort Method: quicksort  Memory: 25kB
                     ->  Append  (cost=52.00..104.06 rows=2 width=4) (actual time=0.075..0.142 rows=4 loops=1)
                           ->  Bitmap Heap Scan on issues issues_1  (cost=52.00..56.02 rows=1 width=4) (actual time=0.075..0.083 rows=4 loops=1)
                                 Recheck Cond: ((((title)::text ~~* '%foo%'::text) AND ((title)::text ~~* '%bar%'::text)) OR ((description ~~* '%foo%'::text) AND (description ~~* '%bar%'::text)))
                                 Filter: (deleted_at IS NULL)
                                 Heap Blocks: exact=2
                                 ->  BitmapOr  (cost=52.00..52.00 rows=1 width=0) (actual time=0.059..0.059 rows=0 loops=1)
                                       ->  Bitmap Index Scan on index_issues_on_title_trigram  (cost=0.00..28.00 rows=1 width=0) (actual time=0.045..0.045 rows=4 loops=1)
                                             Index Cond: (((title)::text ~~* '%foo%'::text) AND ((title)::text ~~* '%bar%'::text))
                                       ->  Bitmap Index Scan on index_issues_on_description_trigram  (cost=0.00..24.00 rows=1 width=0) (actual time=0.014..0.014 rows=0 loops=1)
                                             Index Cond: ((description ~~* '%foo%'::text) AND (description ~~* '%bar%'::text))
                           ->  Bitmap Heap Scan on notes  (cost=44.00..48.02 rows=1 width=4) (actual time=0.058..0.058 rows=0 loops=1)
                                 Recheck Cond: ((note ~~* '%foo%'::text) AND (note ~~* '%bar%'::text))
                                 Filter: ((noteable_type)::text = 'Issue'::text)
                                 Rows Removed by Filter: 1
                                 Heap Blocks: exact=1
                                 ->  Bitmap Index Scan on index_notes_on_note_trigram  (cost=0.00..44.00 rows=1 width=0) (actual time=0.044..0.044 rows=1 loops=1)
                                       Index Cond: ((note ~~* '%foo%'::text) AND (note ~~* '%bar%'::text))
         ->  Index Scan using issues_pkey on issues  (cost=0.29..8.30 rows=1 width=347) (actual time=0.004..0.004 rows=1 loops=4)
               Index Cond: (id = issues_1.id)
               Filter: ((deleted_at IS NULL) AND (project_id = 1))
 Planning time: 1.816 ms
 Execution time: 0.330 ms

Why was this MR needed?

Currently those searches the issue title and description, but not comments.

Does this MR meet the acceptance criteria?

What are the relevant issue numbers?

Closes #20872 (moved), #2201 (moved)

Edited by username-removed-7736