{"action":"created","comment":{"url":"https://api.github.com/repos/almighty/almighty-core/pulls/comments/86702179","pull_request_review_id":7346498,"id":86702179,"diff_hunk":"@@ -0,0 +1,2 @@\n+CREATE INDEX IF NOT EXISTS fields_title ON work_items USING GIN (to_tsvector('english', fields->>'system.title'));","path":"migration/sql-files/005-add-search-index.sql","position":1,"original_position":1,"commit_id":"83911d4ada34356cb8970af12bcc2fb0807fd6eb","original_commit_id":"83911d4ada34356cb8970af12bcc2fb0807fd6eb","user":{"login":"aslakknutsen","id":132158,"avatar_url":"https://avatars.githubusercontent.com/u/132158?v=3","gravatar_id":"","url":"https://api.github.com/users/aslakknutsen","html_url":"https://github.com/aslakknutsen","followers_url":"https://api.github.com/users/aslakknutsen/followers","following_url":"https://api.github.com/users/aslakknutsen/following{/other_user}","gists_url":"https://api.github.com/users/aslakknutsen/gists{/gist_id}","starred_url":"https://api.github.com/users/aslakknutsen/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/aslakknutsen/subscriptions","organizations_url":"https://api.github.com/users/aslakknutsen/orgs","repos_url":"https://api.github.com/users/aslakknutsen/repos","events_url":"https://api.github.com/users/aslakknutsen/events{/privacy}","received_events_url":"https://api.github.com/users/aslakknutsen/received_events","type":"User","site_admin":false},"body":"None of these indexes are hit by the query that is generated. \r\n\r\n```sql\r\nEXPLAIN ANALYZE SELECT count(*) over () as cnt2 , * FROM \"work_items\"\r\nWHERE \"work_items\".deleted_at IS NULL\r\nAND ((\r\n\tsetweight(to_tsvector('english',coalesce(fields->>'system.title','')),'B')||\r\n\tsetweight(to_tsvector('english',coalesce(fields->>'system.description','')),'C')|| \r\n\tsetweight(to_tsvector('english', id::text),'A')\r\n\t@@ to_tsquery('english','\"aslak\"')\r\n)) LIMIT 100\r\n\r\n#\tQUERY PLAN\r\n1\tLimit  (cost=0.00..17565.50 rows=100 width=626) (actual time=12492.779..12492.789 rows=20 loops=1)\r\n2\t  ->  WindowAgg  (cost=0.00..115932.30 rows=660 width=626) (actual time=12492.778..12492.784 rows=20 loops=1)\r\n3\t        ->  Seq Scan on work_items  (cost=0.00..115924.05 rows=660 width=626) (actual time=2024.570..12492.751 rows=20 loops=1)\r\n4\t              Filter: ((deleted_at IS NULL) AND (((setweight(to_tsvector('english'::regconfig, COALESCE((fields ->> 'system.title'::text), ''::text)), 'B'::\"char\") || setweight(to_tsvector('english'::regconfig, COALESCE((fields ->> 'system.description'::text), ''::text)), 'C'::\"char\")) || setweight(to_tsvector('english'::regconfig, (id)::text), 'A'::\"char\")) @@ '''aslak'''::tsquery))\r\n5\t              Rows Removed by Filter: 132110\r\n6\tPlanning time: 0.136 ms\r\n7\tExecution time: 12492.825 ms\r\n``` \r\n\r\nThat is 12s execution time on my dataset, vs 5ms if it hit the index :)","created_at":"2016-11-07T00:55:51Z","updated_at":"2016-11-07T00:57:03Z","html_url":"https://github.com/almighty/almighty-core/pull/395#discussion_r86702179","pull_request_url":"https://api.github.com/repos/almighty/almighty-core/pulls/395","_links":{"self":{"href":"https://api.github.com/repos/almighty/almighty-core/pulls/comments/86702179"},"html":{"href":"https://github.com/almighty/almighty-core/pull/395#discussion_r86702179"},"pull_request":{"href":"https://api.github.com/repos/almighty/almighty-core/pulls/395"}}},"pull_request":{"url":"https://api.github.com/repos/almighty/almighty-core/pulls/395","id":90749809,"html_url":"https://github.com/almighty/almighty-core/pull/395","diff_url":"https://github.com/almighty/almighty-core/pull/395.diff","patch_url":"https://github.com/almighty/almighty-core/pull/395.patch","issue_url":"https://api.github.com/repos/almighty/almighty-core/issues/395","number":395,"state":"open","locked":false,"title":"Workitem search","user":{"login":"sbose78","id":545280,"avatar_url":"https://avatars.githubusercontent.com/u/545280?v=3","gravatar_id":"","url":"https://api.github.com/users/sbose78","html_url":"https://github.com/sbose78","followers_url":"https://api.github.com/users/sbose78/followers","following_url":"https://api.github.com/users/sbose78/following{/other_user}","gists_url":"https://api.github.com/users/sbose78/gists{/gist_id}","starred_url":"https://api.github.com/users/sbose78/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sbose78/subscriptions","organizations_url":"https://api.github.com/users/sbose78/orgs","repos_url":"https://api.github.com/users/sbose78/repos","events_url":"https://api.github.com/users/sbose78/events{/privacy}","received_events_url":"https://api.github.com/users/sbose78/received_events","type":"User","site_admin":false},"body":"compliance\\* Add search capabilities to workitems title,description and ID field(s\r\n- Used postresql full text search capabilities.\r\n- Supported search mechanisms:\r\n\r\nFor each search string\r\n\"ID:12345\" --> Search by ID 12345 only\r\n\"12345\" ---> Search by ID,title,description\r\n\"The cost of the device is 12345\" --> Search by ID,title,description\r\n\r\nURLs can be searched as -\r\n\"http://demo.almighty.io/#/detail/212\" ----> Understand that it's a URL , search full text by ID as startsWith(212) and the string startsWith(\"http://demo.almighty.io/#/detail/212\")\r\nSo effectively the sql parameter for searching this is :\r\n\"212\\* & http://demo.almighty.io/#/detail/212*\"\r\n\r\nEventually extend support for scenarios for work item linking where the workitem content has \r\n\"Hi this has been done in http://demo.almighty.io/#/detail/212\"\r\nThe code should parse out the URL and link the workitem with workitem 212.\r\n- [x] Pagination (Baiju)\r\n- [x] REST API endpoint (Pranav)\r\n- [x] search string parser (Pranav)\r\n","created_at":"2016-10-25T07:04:26Z","updated_at":"2016-11-07T00:57:03Z","closed_at":null,"merged_at":null,"merge_commit_sha":"ced75911eca2720b0d6acbd45924c2126cad8365","assignee":null,"assignees":[],"milestone":null,"commits_url":"https://api.github.com/repos/almighty/almighty-core/pulls/395/commits","review_comments_url":"https://api.github.com/repos/almighty/almighty-core/pulls/395/comments","review_comment_url":"https://api.github.com/repos/almighty/almighty-core/pulls/comments{/number}","comments_url":"https://api.github.com/repos/almighty/almighty-core/issues/395/comments","statuses_url":"https://api.github.com/repos/almighty/almighty-core/statuses/83911d4ada34356cb8970af12bcc2fb0807fd6eb","head":{"label":"almighty:workitem_search","ref":"workitem_search","sha":"83911d4ada34356cb8970af12bcc2fb0807fd6eb","user":{"login":"almighty","id":17783151,"avatar_url":"https://avatars.githubusercontent.com/u/17783151?v=3","gravatar_id":"","url":"https://api.github.com/users/almighty","html_url":"https://github.com/almighty","followers_url":"https://api.github.com/users/almighty/followers","following_url":"https://api.github.com/users/almighty/following{/other_user}","gists_url":"https://api.github.com/users/almighty/gists{/gist_id}","starred_url":"https://api.github.com/users/almighty/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/almighty/subscriptions","organizations_url":"https://api.github.com/users/almighty/orgs","repos_url":"https://api.github.com/users/almighty/repos","events_url":"https://api.github.com/users/almighty/events{/privacy}","received_events_url":"https://api.github.com/users/almighty/received_events","type":"Organization","site_admin":false},"repo":{"id":54260706,"name":"almighty-core","full_name":"almighty/almighty-core","owner":{"login":"almighty","id":17783151,"avatar_url":"https://avatars.githubusercontent.com/u/17783151?v=3","gravatar_id":"","url":"https://api.github.com/users/almighty","html_url":"https://github.com/almighty","followers_url":"https://api.github.com/users/almighty/followers","following_url":"https://api.github.com/users/almighty/following{/other_user}","gists_url":"https://api.github.com/users/almighty/gists{/gist_id}","starred_url":"https://api.github.com/users/almighty/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/almighty/subscriptions","organizations_url":"https://api.github.com/users/almighty/orgs","repos_url":"https://api.github.com/users/almighty/repos","events_url":"https://api.github.com/users/almighty/events{/privacy}","received_events_url":"https://api.github.com/users/almighty/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/almighty/almighty-core","description":null,"fork":false,"url":"https://api.github.com/repos/almighty/almighty-core","forks_url":"https://api.github.com/repos/almighty/almighty-core/forks","keys_url":"https://api.github.com/repos/almighty/almighty-core/keys{/key_id}","collaborators_url":"https://api.github.com/repos/almighty/almighty-core/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/almighty/almighty-core/teams","hooks_url":"https://api.github.com/repos/almighty/almighty-core/hooks","issue_events_url":"https://api.github.com/repos/almighty/almighty-core/issues/events{/number}","events_url":"https://api.github.com/repos/almighty/almighty-core/events","assignees_url":"https://api.github.com/repos/almighty/almighty-core/assignees{/user}","branches_url":"https://api.github.com/repos/almighty/almighty-core/branches{/branch}","tags_url":"https://api.github.com/repos/almighty/almighty-core/tags","blobs_url":"https://api.github.com/repos/almighty/almighty-core/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/almighty/almighty-core/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/almighty/almighty-core/git/refs{/sha}","trees_url":"https://api.github.com/repos/almighty/almighty-core/git/trees{/sha}","statuses_url":"https://api.github.com/repos/almighty/almighty-core/statuses/{sha}","languages_url":"https://api.github.com/repos/almighty/almighty-core/languages","stargazers_url":"https://api.github.com/repos/almighty/almighty-core/stargazers","contributors_url":"https://api.github.com/repos/almighty/almighty-core/contributors","subscribers_url":"https://api.github.com/repos/almighty/almighty-core/subscribers","subscription_url":"https://api.github.com/repos/almighty/almighty-core/subscription","commits_url":"https://api.github.com/repos/almighty/almighty-core/commits{/sha}","git_commits_url":"https://api.github.com/repos/almighty/almighty-core/git/commits{/sha}","comments_url":"https://api.github.com/repos/almighty/almighty-core/comments{/number}","issue_comment_url":"https://api.github.com/repos/almighty/almighty-core/issues/comments{/number}","contents_url":"https://api.github.com/repos/almighty/almighty-core/contents/{+path}","compare_url":"https://api.github.com/repos/almighty/almighty-core/compare/{base}...{head}","merges_url":"https://api.github.com/repos/almighty/almighty-core/merges","archive_url":"https://api.github.com/repos/almighty/almighty-core/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/almighty/almighty-core/downloads","issues_url":"https://api.github.com/repos/almighty/almighty-core/issues{/number}","pulls_url":"https://api.github.com/repos/almighty/almighty-core/pulls{/number}","milestones_url":"https://api.github.com/repos/almighty/almighty-core/milestones{/number}","notifications_url":"https://api.github.com/repos/almighty/almighty-core/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/almighty/almighty-core/labels{/name}","releases_url":"https://api.github.com/repos/almighty/almighty-core/releases{/id}","deployments_url":"https://api.github.com/repos/almighty/almighty-core/deployments","created_at":"2016-03-19T10:42:15Z","updated_at":"2016-10-24T10:38:59Z","pushed_at":"2016-11-06T20:35:00Z","git_url":"git://github.com/almighty/almighty-core.git","ssh_url":"git@github.com:almighty/almighty-core.git","clone_url":"https://github.com/almighty/almighty-core.git","svn_url":"https://github.com/almighty/almighty-core","homepage":"http://devdoc.almighty.io/","size":5149,"stargazers_count":14,"watchers_count":14,"language":"Go","has_issues":true,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":31,"mirror_url":null,"open_issues_count":143,"forks":31,"open_issues":143,"watchers":14,"default_branch":"master"}},"base":{"label":"almighty:master","ref":"master","sha":"f15e80f4f12a56595c3613833faa392aed2d8746","user":{"login":"almighty","id":17783151,"avatar_url":"https://avatars.githubusercontent.com/u/17783151?v=3","gravatar_id":"","url":"https://api.github.com/users/almighty","html_url":"https://github.com/almighty","followers_url":"https://api.github.com/users/almighty/followers","following_url":"https://api.github.com/users/almighty/following{/other_user}","gists_url":"https://api.github.com/users/almighty/gists{/gist_id}","starred_url":"https://api.github.com/users/almighty/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/almighty/subscriptions","organizations_url":"https://api.github.com/users/almighty/orgs","repos_url":"https://api.github.com/users/almighty/repos","events_url":"https://api.github.com/users/almighty/events{/privacy}","received_events_url":"https://api.github.com/users/almighty/received_events","type":"Organization","site_admin":false},"repo":{"id":54260706,"name":"almighty-core","full_name":"almighty/almighty-core","owner":{"login":"almighty","id":17783151,"avatar_url":"https://avatars.githubusercontent.com/u/17783151?v=3","gravatar_id":"","url":"https://api.github.com/users/almighty","html_url":"https://github.com/almighty","followers_url":"https://api.github.com/users/almighty/followers","following_url":"https://api.github.com/users/almighty/following{/other_user}","gists_url":"https://api.github.com/users/almighty/gists{/gist_id}","starred_url":"https://api.github.com/users/almighty/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/almighty/subscriptions","organizations_url":"https://api.github.com/users/almighty/orgs","repos_url":"https://api.github.com/users/almighty/repos","events_url":"https://api.github.com/users/almighty/events{/privacy}","received_events_url":"https://api.github.com/users/almighty/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/almighty/almighty-core","description":null,"fork":false,"url":"https://api.github.com/repos/almighty/almighty-core","forks_url":"https://api.github.com/repos/almighty/almighty-core/forks","keys_url":"https://api.github.com/repos/almighty/almighty-core/keys{/key_id}","collaborators_url":"https://api.github.com/repos/almighty/almighty-core/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/almighty/almighty-core/teams","hooks_url":"https://api.github.com/repos/almighty/almighty-core/hooks","issue_events_url":"https://api.github.com/repos/almighty/almighty-core/issues/events{/number}","events_url":"https://api.github.com/repos/almighty/almighty-core/events","assignees_url":"https://api.github.com/repos/almighty/almighty-core/assignees{/user}","branches_url":"https://api.github.com/repos/almighty/almighty-core/branches{/branch}","tags_url":"https://api.github.com/repos/almighty/almighty-core/tags","blobs_url":"https://api.github.com/repos/almighty/almighty-core/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/almighty/almighty-core/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/almighty/almighty-core/git/refs{/sha}","trees_url":"https://api.github.com/repos/almighty/almighty-core/git/trees{/sha}","statuses_url":"https://api.github.com/repos/almighty/almighty-core/statuses/{sha}","languages_url":"https://api.github.com/repos/almighty/almighty-core/languages","stargazers_url":"https://api.github.com/repos/almighty/almighty-core/stargazers","contributors_url":"https://api.github.com/repos/almighty/almighty-core/contributors","subscribers_url":"https://api.github.com/repos/almighty/almighty-core/subscribers","subscription_url":"https://api.github.com/repos/almighty/almighty-core/subscription","commits_url":"https://api.github.com/repos/almighty/almighty-core/commits{/sha}","git_commits_url":"https://api.github.com/repos/almighty/almighty-core/git/commits{/sha}","comments_url":"https://api.github.com/repos/almighty/almighty-core/comments{/number}","issue_comment_url":"https://api.github.com/repos/almighty/almighty-core/issues/comments{/number}","contents_url":"https://api.github.com/repos/almighty/almighty-core/contents/{+path}","compare_url":"https://api.github.com/repos/almighty/almighty-core/compare/{base}...{head}","merges_url":"https://api.github.com/repos/almighty/almighty-core/merges","archive_url":"https://api.github.com/repos/almighty/almighty-core/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/almighty/almighty-core/downloads","issues_url":"https://api.github.com/repos/almighty/almighty-core/issues{/number}","pulls_url":"https://api.github.com/repos/almighty/almighty-core/pulls{/number}","milestones_url":"https://api.github.com/repos/almighty/almighty-core/milestones{/number}","notifications_url":"https://api.github.com/repos/almighty/almighty-core/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/almighty/almighty-core/labels{/name}","releases_url":"https://api.github.com/repos/almighty/almighty-core/releases{/id}","deployments_url":"https://api.github.com/repos/almighty/almighty-core/deployments","created_at":"2016-03-19T10:42:15Z","updated_at":"2016-10-24T10:38:59Z","pushed_at":"2016-11-06T20:35:00Z","git_url":"git://github.com/almighty/almighty-core.git","ssh_url":"git@github.com:almighty/almighty-core.git","clone_url":"https://github.com/almighty/almighty-core.git","svn_url":"https://github.com/almighty/almighty-core","homepage":"http://devdoc.almighty.io/","size":5149,"stargazers_count":14,"watchers_count":14,"language":"Go","has_issues":true,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":31,"mirror_url":null,"open_issues_count":143,"forks":31,"open_issues":143,"watchers":14,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/almighty/almighty-core/pulls/395"},"html":{"href":"https://github.com/almighty/almighty-core/pull/395"},"issue":{"href":"https://api.github.com/repos/almighty/almighty-core/issues/395"},"comments":{"href":"https://api.github.com/repos/almighty/almighty-core/issues/395/comments"},"review_comments":{"href":"https://api.github.com/repos/almighty/almighty-core/pulls/395/comments"},"review_comment":{"href":"https://api.github.com/repos/almighty/almighty-core/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/almighty/almighty-core/pulls/395/commits"},"statuses":{"href":"https://api.github.com/repos/almighty/almighty-core/statuses/83911d4ada34356cb8970af12bcc2fb0807fd6eb"}}}}
{"actor":{"display_login":"aslakknutsen"}}