{"action":"created","issue":{"url":"https://api.github.com/repos/brocaar/lora-app-server/issues/120","repository_url":"https://api.github.com/repos/brocaar/lora-app-server","labels_url":"https://api.github.com/repos/brocaar/lora-app-server/issues/120/labels{/name}","comments_url":"https://api.github.com/repos/brocaar/lora-app-server/issues/120/comments","events_url":"https://api.github.com/repos/brocaar/lora-app-server/issues/120/events","html_url":"https://github.com/brocaar/lora-app-server/issues/120","id":245394257,"number":120,"title":"search node","user":{"login":"gillespilloudwyres","id":16612839,"avatar_url":"https://avatars1.githubusercontent.com/u/16612839?v=4","gravatar_id":"","url":"https://api.github.com/users/gillespilloudwyres","html_url":"https://github.com/gillespilloudwyres","followers_url":"https://api.github.com/users/gillespilloudwyres/followers","following_url":"https://api.github.com/users/gillespilloudwyres/following{/other_user}","gists_url":"https://api.github.com/users/gillespilloudwyres/gists{/gist_id}","starred_url":"https://api.github.com/users/gillespilloudwyres/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gillespilloudwyres/subscriptions","organizations_url":"https://api.github.com/users/gillespilloudwyres/orgs","repos_url":"https://api.github.com/users/gillespilloudwyres/repos","events_url":"https://api.github.com/users/gillespilloudwyres/events{/privacy}","received_events_url":"https://api.github.com/users/gillespilloudwyres/received_events","type":"User","site_admin":false},"labels":[{"id":571157651,"url":"https://api.github.com/repos/brocaar/lora-app-server/labels/feature","name":"feature","color":"1d76db","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":20,"created_at":"2017-07-25T13:04:45Z","updated_at":"2017-09-21T21:00:38Z","closed_at":null,"author_association":"NONE","body":"Hi !\r\n\r\nI've loaded with the apis up to 400 devices... and I lost lot of time to search device in the device list : \r\n\r\nWill it be possible to imagine have search capabilities, or at least the possibility to change the number of devices displayed at each page, to improve the user experience ?\r\n\r\n![image](https://user-images.githubusercontent.com/16612839/28573313-92316122-714a-11e7-8a43-afdc30572442.png)\r\n\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/brocaar/lora-app-server/issues/comments/331280614","html_url":"https://github.com/brocaar/lora-app-server/issues/120#issuecomment-331280614","issue_url":"https://api.github.com/repos/brocaar/lora-app-server/issues/120","id":331280614,"user":{"login":"siscia","id":640555,"avatar_url":"https://avatars0.githubusercontent.com/u/640555?v=4","gravatar_id":"","url":"https://api.github.com/users/siscia","html_url":"https://github.com/siscia","followers_url":"https://api.github.com/users/siscia/followers","following_url":"https://api.github.com/users/siscia/following{/other_user}","gists_url":"https://api.github.com/users/siscia/gists{/gist_id}","starred_url":"https://api.github.com/users/siscia/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/siscia/subscriptions","organizations_url":"https://api.github.com/users/siscia/orgs","repos_url":"https://api.github.com/users/siscia/repos","events_url":"https://api.github.com/users/siscia/events{/privacy}","received_events_url":"https://api.github.com/users/siscia/received_events","type":"User","site_admin":false},"created_at":"2017-09-21T21:00:38Z","updated_at":"2017-09-21T21:00:38Z","author_association":"CONTRIBUTOR","body":"@iegomez  I believe that the problem is quite \"trivial\"...\r\n\r\n`pg_tgrm` is based on tri-grams, your query use a bi-gram and cannot use the index (seems a quite educate guess reading online, but honestly I am not so expert)\r\n\r\nWhat I would suggest is to try the exact same query using a bigger (more than 2, so at least 3) char in the query.\r\n\r\nLet's compare a query that use `ilike` with two chars:\r\n\r\n```\r\npostgres=# explain analyse\r\npostgres-# select * from node\r\npostgres-# where application_id = 1 and\r\npostgres-# encode(dev_eui, 'hex') ilike '%10%' order by name;\r\n                                                  QUERY PLAN                                                  \r\n--------------------------------------------------------------------------------------------------------------\r\n Sort  (cost=477.69..482.69 rows=2000 width=161) (actual time=61.312..62.040 rows=543 loops=1)\r\n   Sort Key: name\r\n   Sort Method: quicksort  Memory: 169kB\r\n   ->  Seq Scan on node  (cost=0.00..368.04 rows=2000 width=161) (actual time=0.137..57.276 rows=543 loops=1)\r\n         Filter: ((application_id = 1) AND (encode(dev_eui, 'hex'::text) ~~* '%10%'::text))\r\n         Rows Removed by Filter: 9459\r\n Planning time: 0.324 ms\r\n Execution time: 62.572 ms\r\n(8 rows)\r\n\r\nTime: 63,734 ms\r\n```\r\n\r\nAnd a similar query that use 3 char with `ilike`\r\n\r\n```\r\npostgres=# explain analyse\r\npostgres-# select * from node\r\npostgres-# where application_id = 1 and\r\npostgres-# encode(dev_eui, 'hex') ilike '%103%' order by name;\r\n                                                                  QUERY PLAN                                                                   \r\n-----------------------------------------------------------------------------------------------------------------------------------------------\r\n Sort  (cost=232.39..233.39 rows=400 width=161) (actual time=0.757..0.799 rows=43 loops=1)\r\n   Sort Key: name\r\n   Sort Method: quicksort  Memory: 36kB\r\n   ->  Bitmap Heap Scan on node  (cost=15.10..215.10 rows=400 width=161) (actual time=0.084..0.496 rows=43 loops=1)\r\n         Recheck Cond: (encode(dev_eui, 'hex'::text) ~~* '%103%'::text)\r\n         Filter: (application_id = 1)\r\n         Heap Blocks: exact=39\r\n         ->  Bitmap Index Scan on trgm_encode_idx_node_dev_eui  (cost=0.00..15.00 rows=400 width=0) (actual time=0.045..0.045 rows=43 loops=1)\r\n               Index Cond: (encode(dev_eui, 'hex'::text) ~~* '%103%'::text)\r\n Planning time: 0.273 ms\r\n Execution time: 0.967 ms\r\n(11 rows)\r\n```\r\n\r\nYou can see that with at least 3 chars the query use the index.\r\n\r\nIf it is possible, and reasonable, it could be worth to make a db call when there are at least 3 chars in query, or after that the user had waited a little bit. (It is a realtime search or press a button and then make the query?) "}}
{"actor":{"display_login":"siscia"}}