{"action":"created","issue":{"url":"https://api.github.com/repos/dimagi/commcare-hq/issues/10374","repository_url":"https://api.github.com/repos/dimagi/commcare-hq","labels_url":"https://api.github.com/repos/dimagi/commcare-hq/issues/10374/labels{/name}","comments_url":"https://api.github.com/repos/dimagi/commcare-hq/issues/10374/comments","events_url":"https://api.github.com/repos/dimagi/commcare-hq/issues/10374/events","html_url":"https://github.com/dimagi/commcare-hq/pull/10374","id":134727188,"number":10374,"title":"Speed up device log","user":{"login":"dannyroberts","id":137212,"avatar_url":"https://avatars.githubusercontent.com/u/137212?v=3","gravatar_id":"","url":"https://api.github.com/users/dannyroberts","html_url":"https://github.com/dannyroberts","followers_url":"https://api.github.com/users/dannyroberts/followers","following_url":"https://api.github.com/users/dannyroberts/following{/other_user}","gists_url":"https://api.github.com/users/dannyroberts/gists{/gist_id}","starred_url":"https://api.github.com/users/dannyroberts/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dannyroberts/subscriptions","organizations_url":"https://api.github.com/users/dannyroberts/orgs","repos_url":"https://api.github.com/users/dannyroberts/repos","events_url":"https://api.github.com/users/dannyroberts/events{/privacy}","received_events_url":"https://api.github.com/users/dannyroberts/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/dimagi/commcare-hq/labels/No%20lint%20errors","name":"No lint errors","color":"bfe5bf"},{"url":"https://api.github.com/repos/dimagi/commcare-hq/labels/reindex/migration","name":"reindex/migration","color":"eb6420"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":22,"created_at":"2016-02-18T23:18:06Z","updated_at":"2016-02-25T17:53:57Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/dimagi/commcare-hq/pulls/10374","html_url":"https://github.com/dimagi/commcare-hq/pull/10374","diff_url":"https://github.com/dimagi/commcare-hq/pull/10374.diff","patch_url":"https://github.com/dimagi/commcare-hq/pull/10374.patch"},"body":"Should fix http://manage.dimagi.com/default.asp?217466\r\n\r\n## Note to deployer\r\nThe reindex/migration tag is set because this adds a new relatively large sql index. This is a situation in which it would make sense to setup_release this branch on prod, and then run `./manage migrate phonelog` there and wait for it to complete before deploying. This should not cause any interruption, and it won't hang on deploy, though the pre-migration itself will likely take quite a bit of time to complete.\r\n\r\n## Interesting backstory\r\n\r\nSomewhat unintuitively, the thing that takes the longest in loading the Device Log Details report on a domain with a lot of them isn't the actual data but rather the filters, which need to get a list of all distinct values to show.\r\n\r\nA while ago I made `fast_distinct` for this, using some code from the postgres wiki to implement a \"loose index scan\"—a technique for basically running distinct queries, but manually doing it in a way that's much faster than using the distinct keyword: finding the minimum value, then finding the smallest value less greater than it, and so on until you're done.\r\n\r\nFor this to be fast, the lookups have to be using an index. So when I first did this, I made an index for the 'type' column, and used `fast_distinct` to get a list of distinct types. But the code I was copying could only replace a full distinct on a column (i.e. across all domains). This worked fine for 'type' (which is a limited set regardless of the domain), but didn't work for username or device_id which are domain-specific, so those remained using bad ol' DISTINCT.\r\n\r\nWhen I started working on http://manage.dimagi.com/default.asp?217466 it quickly became apparent that these `SELECT DISTINCT ... WHERE domain = 'pradan-mis'` queries were taking _long_ time. So I looked back at this `fast_distinct` hack and realized it could probably be modified pretty easily to also filter by domain. So I tested that in the psql shell on staging (on its own largest domain) and it was indeed way faster. Now, in order for it to be faster, of course, we also have to add an index for (domain, username) and (domain, device_id). (Indexing just username without domain is actually useless, which is what we were doing before.)\r\n\r\nSo that's what this PR does. It cleans up the device log sql indexes (removing useless ones and adding useful ones) and then adds this pretty huge (if gnarly) optimization."},"comment":{"url":"https://api.github.com/repos/dimagi/commcare-hq/issues/comments/188903227","html_url":"https://github.com/dimagi/commcare-hq/pull/10374#issuecomment-188903227","issue_url":"https://api.github.com/repos/dimagi/commcare-hq/issues/10374","id":188903227,"user":{"login":"snopoke","id":249606,"avatar_url":"https://avatars.githubusercontent.com/u/249606?v=3","gravatar_id":"","url":"https://api.github.com/users/snopoke","html_url":"https://github.com/snopoke","followers_url":"https://api.github.com/users/snopoke/followers","following_url":"https://api.github.com/users/snopoke/following{/other_user}","gists_url":"https://api.github.com/users/snopoke/gists{/gist_id}","starred_url":"https://api.github.com/users/snopoke/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/snopoke/subscriptions","organizations_url":"https://api.github.com/users/snopoke/orgs","repos_url":"https://api.github.com/users/snopoke/repos","events_url":"https://api.github.com/users/snopoke/events{/privacy}","received_events_url":"https://api.github.com/users/snopoke/received_events","type":"User","site_admin":false},"created_at":"2016-02-25T17:53:57Z","updated_at":"2016-02-25T17:53:57Z","body":"Seems OK to me. not that much of a hack and with a known end of life plan.\n\nOn 25 February 2016 at 18:24, Daniel Roberts <notifications@github.com>\nwrote:\n\n> @snopoke <https://github.com/snopoke> there's good news and bad news. The\n> good news is I hacked around it to make it work for 9.4+. The bad news is\n> that this is what it looks like: c4b9f27\n> <https://github.com/dimagi/commcare-hq/commit/c4b9f27ddd6a4ae8fa5077a55668d0320d989b91>.\n> I think the good news is better than the bad news is bad.\n>\n> —\n> Reply to this email directly or view it on GitHub\n> <https://github.com/dimagi/commcare-hq/pull/10374#issuecomment-188862178>.\n>\n\n\n\n-- \nSimon Kelly\nSenior Engineer | Dimagi South Africa\n"}}