{"action":"created","issue":{"url":"https://api.github.com/repos/mattermost/platform/issues/2622","repository_url":"https://api.github.com/repos/mattermost/platform","labels_url":"https://api.github.com/repos/mattermost/platform/issues/2622/labels{/name}","comments_url":"https://api.github.com/repos/mattermost/platform/issues/2622/comments","events_url":"https://api.github.com/repos/mattermost/platform/issues/2622/events","html_url":"https://github.com/mattermost/platform/issues/2622","id":145508011,"number":2622,"title":"GIN index does not work when searching posted messages on PostgreSQL","user":{"login":"yukihane","id":827785,"avatar_url":"https://avatars.githubusercontent.com/u/827785?v=3","gravatar_id":"","url":"https://api.github.com/users/yukihane","html_url":"https://github.com/yukihane","followers_url":"https://api.github.com/users/yukihane/followers","following_url":"https://api.github.com/users/yukihane/following{/other_user}","gists_url":"https://api.github.com/users/yukihane/gists{/gist_id}","starred_url":"https://api.github.com/users/yukihane/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/yukihane/subscriptions","organizations_url":"https://api.github.com/users/yukihane/orgs","repos_url":"https://api.github.com/users/yukihane/repos","events_url":"https://api.github.com/users/yukihane/events{/privacy}","received_events_url":"https://api.github.com/users/yukihane/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2016-04-03T14:48:36Z","updated_at":"2016-04-09T05:58:57Z","closed_at":null,"body":"I guess that  when searching posted messages, [idx_posts_message_txt](https://github.com/mattermost/platform/blob/v2.1.0/store/sql_store.go#L444) index would be used.\r\n>  ```\"CREATE INDEX \" + indexName + \" ON \" + tableName + \" USING gin(to_tsvector('english', \" + columnName + \"))\"```\r\n\r\nHowever, because of [search query without 2-argument version to_tsvector](https://github.com/mattermost/platform/blob/v2.1.0/store/sql_post_store.go#L731), it does not work.\r\n> ```AND **%s** @@  to_tsquery(:Terms)```\r\n\r\n[PostgreSQL docs](http://www.postgresql.org/docs/9.5/static/textsearch-tables.html#TEXTSEARCH-TABLES-INDEX):\r\n> Chapter 12. Full Text Search\r\n> 12.2.2. Creating Indexes\r\n> Because the two-argument version of to_tsvector was used in the index above, only a query reference that uses the 2-argument version of to_tsvector with the same configuration name will use that index. That is, WHERE to_tsvector('english', body) @@ 'a & b' can use the index, but WHERE to_tsvector(body) @@ 'a & b' cannot.\r\n\r\n----\r\nI don't hope to fix this problem by putting 'english' on first-argument.\r\nI want search other language, Japanese."},"comment":{"url":"https://api.github.com/repos/mattermost/platform/issues/comments/207715650","html_url":"https://github.com/mattermost/platform/issues/2622#issuecomment-207715650","issue_url":"https://api.github.com/repos/mattermost/platform/issues/2622","id":207715650,"user":{"login":"terukizm","id":1755900,"avatar_url":"https://avatars.githubusercontent.com/u/1755900?v=3","gravatar_id":"","url":"https://api.github.com/users/terukizm","html_url":"https://github.com/terukizm","followers_url":"https://api.github.com/users/terukizm/followers","following_url":"https://api.github.com/users/terukizm/following{/other_user}","gists_url":"https://api.github.com/users/terukizm/gists{/gist_id}","starred_url":"https://api.github.com/users/terukizm/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/terukizm/subscriptions","organizations_url":"https://api.github.com/users/terukizm/orgs","repos_url":"https://api.github.com/users/terukizm/repos","events_url":"https://api.github.com/users/terukizm/events{/privacy}","received_events_url":"https://api.github.com/users/terukizm/received_events","type":"User","site_admin":false},"created_at":"2016-04-09T05:58:57Z","updated_at":"2016-04-09T05:58:57Z","body":"Hi, @it33.\r\n\r\nMy English is very very poor. I know.\r\nBut please let me do the supplementary explanation of that.\r\n\r\n## Problems:\r\n\r\n###  `idx_posts_message_txt` does not work now\r\n\r\n[Create-FULLTEXT-Index-Query](https://github.com/mattermost/platform/blob/v1.2.1/store/sql_store.go#L410) have `'english'` option. \r\nBut [Search-Query](https://github.com/mattermost/platform/blob/v1.2.1/store/sql_post_store.go#L648) dose not have `'english'` option.\r\n\r\nI guess @yukihane want to say \"This query will not be index-scan\". \r\nHis fix plan is:\r\n\r\n### 1. Remove 'english' option from create-index-query\r\n\r\nIn this case, [default_text_search_config](http://www.postgresql.org/docs/9.4/static/runtime-config-client.html#GUC-DEFAULT-TEXT-SEARCH-CONFIG) will become effective.\r\n\r\ne.g. \r\n\r\npostgres.conf\r\n\r\n``` \r\ndefault_text_search_config = 'pg_catalog.english'\r\n```\r\n\r\ncreate index:\r\n\r\n```\r\nCREATE INDEX `idx_posts_message_txt` ON POSTSUSING gin(to_tsvector(MESSAGE))`\r\n```\r\n\r\nsearch query:\r\n\r\n```\r\n  to_tsquery(:Terms)\r\n```\r\n\r\nIn this fix, `idx_posts_message_txt` will be valid index-scan.\r\nBut, This approach  influences other software to using postgresql.  (e.g. GitLab )\r\n\r\n### 2. Add 'english' option to search-query\r\n\r\n@yukihane guess this approach is more better.\r\n\r\ncreate index:\r\n\r\n```\r\nCREATE INDEX `idx_posts_message_txt` ON POSTSUSING gin(to_tsvector('english', MESSAGE))\r\n```\r\n\r\nsearch query:\r\n\r\n```\r\n  to_tsquery('english', :Terms)\r\n```\r\n\r\nIn this fix, `idx_posts_message_txt` will be valid index-scan.\r\n\r\n#### 2.1 Move 'english' option to config file\r\n\r\nThis is my suggest.\r\nThis apporach based on 2, but It will be support [other language in postgresql.](http://www.postgresql.org/docs/9.4/static/textsearch-psql.html).\r\n\r\ne.g. \r\n\r\nAdd to config.json (ofcourse this is sample)\r\n\r\n```\r\n    \"PostgresqlSettings\": {\r\n        \"TextSearchConfig\": \"english\"\r\n    }\r\n```\r\n\r\n```\r\nCREATE INDEX `idx_posts_message_txt` ON POSTSUSING gin(to_tsvector(utils.Cfg.PostgresqlSettings.TextSearchConfig, MESSAGE))\r\n```\r\n\r\n```\r\nto_tsquery(utils.Cfg.PostgresqlSettings.TextSearchConfig, :Terms)\r\n```\r\n\r\nI'm sorry if I'm mistaken."}}