{"action":"created","issue":{"url":"https://api.github.com/repos/Inboxen/Inboxen/issues/94","labels_url":"https://api.github.com/repos/Inboxen/Inboxen/issues/94/labels{/name}","comments_url":"https://api.github.com/repos/Inboxen/Inboxen/issues/94/comments","events_url":"https://api.github.com/repos/Inboxen/Inboxen/issues/94/events","html_url":"https://github.com/Inboxen/Inboxen/issues/94","id":107597294,"number":94,"title":"Search doesn't scale?","user":{"login":"moggers87","id":1490673,"avatar_url":"https://avatars.githubusercontent.com/u/1490673?v=3","gravatar_id":"","url":"https://api.github.com/users/moggers87","html_url":"https://github.com/moggers87","followers_url":"https://api.github.com/users/moggers87/followers","following_url":"https://api.github.com/users/moggers87/following{/other_user}","gists_url":"https://api.github.com/users/moggers87/gists{/gist_id}","starred_url":"https://api.github.com/users/moggers87/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/moggers87/subscriptions","organizations_url":"https://api.github.com/users/moggers87/orgs","repos_url":"https://api.github.com/users/moggers87/repos","events_url":"https://api.github.com/users/moggers87/events{/privacy}","received_events_url":"https://api.github.com/users/moggers87/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/Inboxen/Inboxen/labels/app:inboxen","name":"app:inboxen","color":"000000"},{"url":"https://api.github.com/repos/Inboxen/Inboxen/labels/enhancement","name":"enhancement","color":"84b6eb"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":2,"created_at":"2015-09-21T20:59:20Z","updated_at":"2015-09-21T21:25:34Z","closed_at":null,"body":"Below are the query plans from Postges for searching, first as Watson does it (with a subquery) and then doing a `JOIN` (I thought that might help?). There are ~100K emails in this database, with something like 30K-40K emails belonging to my test user.\r\n\r\nThe query, in either form, takes well over a minute to complete.\r\n\r\n```\r\ninboxen=> explain SELECT \"watson_searchentry\".\"id\" FROM \"watson_searchentry\" WHERE (\"watson_searchentry\".\"engine_slug\" = 'default' AND (\r\n\"watson_searchentry\".\"object_id_int\" IN (SELECT U0.\"id\" FROM \"inboxen_email\" U0 INNER JOIN \"inboxen_inbox\" U1 ON ( U0.\"inbox_id\" = U1.\"i\r\nd\" ) WHERE (U1.\"user_id\" = 2 AND NOT ((U0.\"flags\" & 1 = 1 OR U1.\"flags\" & 1 = 1)))) AND \"watson_searchentry\".\"content_type_id\" = 14) AND\r\n (search_tsv @@ to_tsquery('pg_catalog.english', 'mail:*'))) ORDER BY (ts_rank_cd(watson_searchentry.search_tsv, to_tsquery('pg_catalog.\r\nenglish', 'mail:*'))) DESC LIMIT 10                                                                                                     \r\n;\r\n                                                                 QUERY PLAN                                                             \r\n    \r\n----------------------------------------------------------------------------------------------------------------------------------------\r\n----\r\n Limit  (cost=103190.09..103190.11 rows=10 width=350)\r\n   ->  Sort  (cost=103190.09..103229.76 rows=15867 width=350)\r\n         Sort Key: (ts_rank_cd(watson_searchentry.search_tsv, '''mail'':*'::tsquery))\r\n         ->  Nested Loop  (cost=4031.45..102847.21 rows=15867 width=350)\r\n               ->  HashAggregate  (cost=4031.02..4226.47 rows=19545 width=4)\r\n                     ->  Hash Join  (cost=19.28..3982.16 rows=19545 width=4)\r\n                           Hash Cond: (u0.inbox_id = u1.id)\r\n                           ->  Seq Scan on inboxen_email u0  (cost=0.00..3316.55 rows=120233 width=8)\r\n                                 Filter: ((flags & 1::bigint) <> 1)\r\n                           ->  Hash  (cost=17.63..17.63 rows=132 width=4)\r\n                                 ->  Bitmap Heap Scan on inboxen_inbox u1  (cost=5.31..17.63 rows=132 width=4)\r\n                                       Recheck Cond: (user_id = 2)\r\n                                       Filter: ((flags & 1::bigint) <> 1)\r\n                                       ->  Bitmap Index Scan on inboxen_inbox_user_id_idx  (cost=0.00..5.27 rows=133 width=0)\r\n                                             Index Cond: (user_id = 2)\r\n               ->  Index Scan using watson_searchentry_object_id_int on watson_searchentry  (cost=0.42..5.03 rows=1 width=354)\r\n                     Index Cond: (object_id_int = u0.id)\r\n                     Filter: ((search_tsv @@ '''mail'':*'::tsquery) AND ((engine_slug)::text = 'default'::text) AND (content_type_id = 1\r\n4))\r\n(18 rows)\r\n\r\n```\r\n\r\n```\r\ninboxen=> explain SELECT W0.\"id\"                                                                                                        \r\nFROM\r\n    \"watson_searchentry\" W0\r\n     INNER JOIN \"inboxen_email\" U0 ON (W0.\"object_id_int\" = U0.\"id\")\r\n     INNER JOIN \"inboxen_inbox\" U1 ON ( U0.\"inbox_id\" = U1.\"id\" )         \r\nWHERE (\r\n    W0.\"engine_slug\" = 'default' AND\r\n    (U1.\"user_id\" = 2 AND NOT ((U0.\"flags\" & 1 = 1 OR U1.\"flags\" & 1 = 1))) AND                                                         \r\n    W0.\"content_type_id\" = 14 AND                                                                                                       \r\n    (search_tsv @@ to_tsquery('pg_catalog.english', 'mail:*'))\r\n)                                                                                              \r\nORDER BY (ts_rank_cd(W0.search_tsv, to_tsquery('pg_catalog.english', 'mail:*'))) DESC LIMIT 10;\r\n                                                                 QUERY PLAN                                                             \r\n    \r\n----------------------------------------------------------------------------------------------------------------------------------------\r\n----\r\n Limit  (cost=102945.78..102945.80 rows=10 width=350)\r\n   ->  Sort  (cost=102945.78..102985.44 rows=15867 width=350)\r\n         Sort Key: (ts_rank_cd(w0.search_tsv, '''mail'':*'::tsquery))\r\n         ->  Nested Loop  (cost=19.71..102602.90 rows=15867 width=350)\r\n               ->  Hash Join  (cost=19.28..3982.16 rows=19545 width=4)\r\n                     Hash Cond: (u0.inbox_id = u1.id)\r\n                     ->  Seq Scan on inboxen_email u0  (cost=0.00..3316.55 rows=120233 width=8)\r\n                           Filter: ((flags & 1::bigint) <> 1)\r\n                     ->  Hash  (cost=17.63..17.63 rows=132 width=4)\r\n                           ->  Bitmap Heap Scan on inboxen_inbox u1  (cost=5.31..17.63 rows=132 width=4)\r\n                                 Recheck Cond: (user_id = 2)\r\n                                 Filter: ((flags & 1::bigint) <> 1)\r\n                                 ->  Bitmap Index Scan on inboxen_inbox_user_id_idx  (cost=0.00..5.27 rows=133 width=0)\r\n                                       Index Cond: (user_id = 2)\r\n               ->  Index Scan using watson_searchentry_object_id_int on watson_searchentry w0  (cost=0.42..5.03 rows=1 width=354)\r\n                     Index Cond: (object_id_int = u0.id)\r\n                     Filter: ((search_tsv @@ '''mail'':*'::tsquery) AND ((engine_slug)::text = 'default'::text) AND (content_type_id = 1\r\n4))\r\n(17 rows)\r\n```"},"comment":{"url":"https://api.github.com/repos/Inboxen/Inboxen/issues/comments/142112379","html_url":"https://github.com/Inboxen/Inboxen/issues/94#issuecomment-142112379","issue_url":"https://api.github.com/repos/Inboxen/Inboxen/issues/94","id":142112379,"user":{"login":"moggers87","id":1490673,"avatar_url":"https://avatars.githubusercontent.com/u/1490673?v=3","gravatar_id":"","url":"https://api.github.com/users/moggers87","html_url":"https://github.com/moggers87","followers_url":"https://api.github.com/users/moggers87/followers","following_url":"https://api.github.com/users/moggers87/following{/other_user}","gists_url":"https://api.github.com/users/moggers87/gists{/gist_id}","starred_url":"https://api.github.com/users/moggers87/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/moggers87/subscriptions","organizations_url":"https://api.github.com/users/moggers87/orgs","repos_url":"https://api.github.com/users/moggers87/repos","events_url":"https://api.github.com/users/moggers87/events{/privacy}","received_events_url":"https://api.github.com/users/moggers87/received_events","type":"User","site_admin":false},"created_at":"2015-09-21T21:25:34Z","updated_at":"2015-09-21T21:25:34Z","body":"OK, so we can get rid of ranking by doing `ranking=False` in our calls to `watson.search()` - need to see if ordering on a subquery will guarantee order in the parent query (probably not, but maybe?) "}}