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 ------- 12333gitlabhq_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?
-
Changelog entry added, if necessary -
Documentation created/updated -
API support added -
Tests added for this feature/bug - Review
-
Has been reviewed by UX -
Has been reviewed by Frontend -
Has been reviewed by Backend -
Has been reviewed by Database
-
-
Conform by the merge request performance guides -
Conform by the style guides -
Squashed related commits together
What are the relevant issue numbers?
Closes #20872 (moved), #2201 (moved)
Edited by Hiroyuki Sato