{"action":"created","issue":{"url":"https://api.github.com/repos/18F/midas/issues/797","labels_url":"https://api.github.com/repos/18F/midas/issues/797/labels{/name}","comments_url":"https://api.github.com/repos/18F/midas/issues/797/comments","events_url":"https://api.github.com/repos/18F/midas/issues/797/events","html_url":"https://github.com/18F/midas/issues/797","id":77729604,"number":797,"title":"All user query (new /api/user/all endpoint) slow on staging","user":{"login":"sefk","id":621565,"avatar_url":"https://avatars.githubusercontent.com/u/621565?v=3","gravatar_id":"","url":"https://api.github.com/users/sefk","html_url":"https://github.com/sefk","followers_url":"https://api.github.com/users/sefk/followers","following_url":"https://api.github.com/users/sefk/following{/other_user}","gists_url":"https://api.github.com/users/sefk/gists{/gist_id}","starred_url":"https://api.github.com/users/sefk/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sefk/subscriptions","organizations_url":"https://api.github.com/users/sefk/orgs","repos_url":"https://api.github.com/users/sefk/repos","events_url":"https://api.github.com/users/sefk/events{/privacy}","received_events_url":"https://api.github.com/users/sefk/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-05-18T17:05:39Z","updated_at":"2015-05-18T21:03:17Z","closed_at":null,"body":"Look at the \"network\" tab on chrome developer tools when looking at the [people page](https://midas-dev.18f.us/people) on staging. You see time to first byte (TTFB) for \"all\" in the 600ms - 1000ms range. Only 15KB of data is being returned!\r\n\r\nThis isn't what I see in my dev environment, of course. 60ms to return 19KB. But that's localhost, quiet postgres server, artificial (demo) data.\r\n\r\nTo debug I would either need better test data (see #712) or access to staging. "},"comment":{"url":"https://api.github.com/repos/18F/midas/issues/comments/103210276","html_url":"https://github.com/18F/midas/issues/797#issuecomment-103210276","issue_url":"https://api.github.com/repos/18F/midas/issues/797","id":103210276,"user":{"login":"sefk","id":621565,"avatar_url":"https://avatars.githubusercontent.com/u/621565?v=3","gravatar_id":"","url":"https://api.github.com/users/sefk","html_url":"https://github.com/sefk","followers_url":"https://api.github.com/users/sefk/followers","following_url":"https://api.github.com/users/sefk/following{/other_user}","gists_url":"https://api.github.com/users/sefk/gists{/gist_id}","starred_url":"https://api.github.com/users/sefk/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sefk/subscriptions","organizations_url":"https://api.github.com/users/sefk/orgs","repos_url":"https://api.github.com/users/sefk/repos","events_url":"https://api.github.com/users/sefk/events{/privacy}","received_events_url":"https://api.github.com/users/sefk/received_events","type":"User","site_admin":false},"created_at":"2015-05-18T21:03:17Z","updated_at":"2015-05-18T21:03:17Z","body":"While I can't say for sure, it's very likely that the problem could be the Sails ORM doing a dumb query. [This gist is the Postgres log][pglog] when hitting ```/api/user/all``` on my dev environment (13 users each with a tag or two). \r\n\r\n  [pglog]: https://gist.github.com/sefk/800236501cfd6990f78d\r\n\r\nWhile I haven't profiled this, line 6 of the gist certainly looks to be a potential culprit. Instead of an inner join, the sql generated does a subquery for every foreign key and then union's them all together. You could imagine that a query planner *might* be smart enough to notice and optimize this case but not only is that unlikely, but also unnecessary. \r\n\r\nThree options for proceeding \r\n\r\n1. **Waterline Fix.** I've searched the Waterline and Sails lists and there are several issues filed that may relate to this, but not sure. I'm wary of opening my own issue until I discuss with @dhcole and likely characterize this using their own schema / tests. My reading however makes me believe that this isn't some fix already made somewhere, not a quick & easy fix.\r\n\r\n2. **Hand Join.** User.find(), then do TagEntity.find() and join ourselves. Ugly to have to do this but a) two queries would be much more efficient, b) it's more data btw app server and DB, but we can winnow that down before sending to the client, and c) it would still work with the file-based backend used for testing\r\n\r\n3. **Hand query().** We can always write the sql ourselves and call query() on it, but that won't work on a non-sql backend, like one that we use while running unit tests. \r\n\r\nPersonally I think the \"hand join\" option is what I'd lean toward, but I wouldn't do it until I had the same problem repro'ed in my own dev environment (see #712 again).\r\n\r\nNote there's issue [#944](https://github.com/balderdashy/waterline/issues/944) that may be related but may not. That looks to be a memory issue at larger data sizes than we're seeing."}}