Filter issues / merge request fuzzy search those comments

Review changes
Download
Patches
Plain diff

Open Filter issues / merge request fuzzy search those comments

hiroponz/gitlab-ce:search-issuable-comments into master

Overview 48
Commits 5
Pipelines 7
Changes 8

Open username-removed-7736 requested to merge hiroponz/gitlab-ce:search-issuable-comments into master 7 years ago

Overview 48
Commits 5
Pipelines 7
Changes 8

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?

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 7 years ago by username-removed-7736

Merge request reports

Activity

Filter activity

Approvals
Assignees & reviewers
Comments (from bots)
Comments (from users)
Commits & branches
Edits
Labels
Lock status
Mentions
Merge request status
Tracking

Please register or sign in to reply

0 Assignees

0 Reviewers

Request review from

Labels

None

Select labels

Manage project labels

Milestone