{"action":"created","issue":{"url":"https://api.github.com/repos/ManageIQ/manageiq/issues/12560","repository_url":"https://api.github.com/repos/ManageIQ/manageiq","labels_url":"https://api.github.com/repos/ManageIQ/manageiq/issues/12560/labels{/name}","comments_url":"https://api.github.com/repos/ManageIQ/manageiq/issues/12560/comments","events_url":"https://api.github.com/repos/ManageIQ/manageiq/issues/12560/events","html_url":"https://github.com/ManageIQ/manageiq/pull/12560","id":188570345,"number":12560,"title":"For purging remaining records, use single query","user":{"login":"kbrock","id":1930,"avatar_url":"https://avatars.githubusercontent.com/u/1930?v=3","gravatar_id":"","url":"https://api.github.com/users/kbrock","html_url":"https://github.com/kbrock","followers_url":"https://api.github.com/users/kbrock/followers","following_url":"https://api.github.com/users/kbrock/following{/other_user}","gists_url":"https://api.github.com/users/kbrock/gists{/gist_id}","starred_url":"https://api.github.com/users/kbrock/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kbrock/subscriptions","organizations_url":"https://api.github.com/users/kbrock/orgs","repos_url":"https://api.github.com/users/kbrock/repos","events_url":"https://api.github.com/users/kbrock/events{/privacy}","received_events_url":"https://api.github.com/users/kbrock/received_events","type":"User","site_admin":false},"labels":[{"id":106615678,"url":"https://api.github.com/repos/ManageIQ/manageiq/labels/enhancement","name":"enhancement","color":"84b6eb","default":true},{"id":129042774,"url":"https://api.github.com/repos/ManageIQ/manageiq/labels/performance","name":"performance","color":"fad8c7","default":false}],"state":"open","locked":false,"assignee":{"login":"Fryguy","id":52120,"avatar_url":"https://avatars.githubusercontent.com/u/52120?v=3","gravatar_id":"","url":"https://api.github.com/users/Fryguy","html_url":"https://github.com/Fryguy","followers_url":"https://api.github.com/users/Fryguy/followers","following_url":"https://api.github.com/users/Fryguy/following{/other_user}","gists_url":"https://api.github.com/users/Fryguy/gists{/gist_id}","starred_url":"https://api.github.com/users/Fryguy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Fryguy/subscriptions","organizations_url":"https://api.github.com/users/Fryguy/orgs","repos_url":"https://api.github.com/users/Fryguy/repos","events_url":"https://api.github.com/users/Fryguy/events{/privacy}","received_events_url":"https://api.github.com/users/Fryguy/received_events","type":"User","site_admin":false},"assignees":[{"login":"Fryguy","id":52120,"avatar_url":"https://avatars.githubusercontent.com/u/52120?v=3","gravatar_id":"","url":"https://api.github.com/users/Fryguy","html_url":"https://github.com/Fryguy","followers_url":"https://api.github.com/users/Fryguy/followers","following_url":"https://api.github.com/users/Fryguy/following{/other_user}","gists_url":"https://api.github.com/users/Fryguy/gists{/gist_id}","starred_url":"https://api.github.com/users/Fryguy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Fryguy/subscriptions","organizations_url":"https://api.github.com/users/Fryguy/orgs","repos_url":"https://api.github.com/users/Fryguy/repos","events_url":"https://api.github.com/users/Fryguy/events{/privacy}","received_events_url":"https://api.github.com/users/Fryguy/received_events","type":"User","site_admin":false}],"milestone":null,"comments":4,"created_at":"2016-11-10T17:29:42Z","updated_at":"2016-11-14T15:23:35Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/ManageIQ/manageiq/pulls/12560","html_url":"https://github.com/ManageIQ/manageiq/pull/12560","diff_url":"https://github.com/ManageIQ/manageiq/pull/12560.diff","patch_url":"https://github.com/ManageIQ/manageiq/pull/12560.patch"},"body":"While purging report results by \"remaining\" we had a very bad N+1:\r\n\r\n|        ms |    bytes |  objects |queries | query (ms) |     rows |`comments`\r\n|       ---:|      ---:|      ---:|  ---:|      ---:|      ---:| ---\r\n|  19,377.2 | 33,471,380* | 7,968,800 |6,413 | 12,702.1 |   16,481 |before\r\n|   7,313.8 |  491,434 |    5,765 |   11 |  7,274.6 |          |after\r\n| 62.3% | 99% | 99.9% | 99.9%| 42.7% | 100% | |\r\n\r\n\\* Memory usage does not reflect 3,732,622 freed objects. \r\n\r\n\r\nbefore:\r\n-------\r\n\r\n```ruby\r\nremaining = 5 # keep 5 records\r\nids_to_delete = []\r\nselect(\"distinct(foreign_key)\").map(&:foreign_key).flat_map do |foreign_key|\r\n  last_id=where(:foreign_key=>foreign_key).order(\"id desc\").offset(remaining).limit(1)\r\n  ids_to_delete+=where(:foreign_key=>foreign_key).order(\"id DESC\").where('id<?',last_id)\r\nend\r\ndestroy_all(:id => ids_to_delete)\r\n```\r\n\r\nIt brings back every id in the table in multiple queries with sorting\r\nWindowing repeats many of these queries multiple times.\r\n\r\nafter\r\n-----\r\n\r\n```ruby\r\nremaining = 5\r\n# this is a sub query\r\n# not the real sql - The offset is performed within each grouping (aka `RANK, PARTITION`)\r\nids_to_delete = \"(SELECT id FROM table GROUP BY foreign_key OFFSET #{remaining})\"\r\ndestroy_all(:id => ids_to_delete)\r\n```\r\n\r\nit brings back no records\r\nthe db does the algorithm above\r\nthis may be ANSI SQL, but it is advanced.\r\n\r\n/cc @NickLaMuro \r\n"},"comment":{"url":"https://api.github.com/repos/ManageIQ/manageiq/issues/comments/260364612","html_url":"https://github.com/ManageIQ/manageiq/pull/12560#issuecomment-260364612","issue_url":"https://api.github.com/repos/ManageIQ/manageiq/issues/12560","id":260364612,"user":{"login":"miq-bot","id":7806257,"avatar_url":"https://avatars.githubusercontent.com/u/7806257?v=3","gravatar_id":"","url":"https://api.github.com/users/miq-bot","html_url":"https://github.com/miq-bot","followers_url":"https://api.github.com/users/miq-bot/followers","following_url":"https://api.github.com/users/miq-bot/following{/other_user}","gists_url":"https://api.github.com/users/miq-bot/gists{/gist_id}","starred_url":"https://api.github.com/users/miq-bot/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/miq-bot/subscriptions","organizations_url":"https://api.github.com/users/miq-bot/orgs","repos_url":"https://api.github.com/users/miq-bot/repos","events_url":"https://api.github.com/users/miq-bot/events{/privacy}","received_events_url":"https://api.github.com/users/miq-bot/received_events","type":"User","site_admin":false},"created_at":"2016-11-14T15:23:35Z","updated_at":"2016-11-14T15:23:35Z","body":"<rubocop />Checked commit https://github.com/kbrock/manageiq/commit/e68d1e5cdbfc136fb1865ffa82aaa57a55d521f6 with ruby 2.2.5, rubocop 0.37.2, and haml-lint 0.16.1\n5 files checked, 1 offense detected\n\n**app/models/mixins/purging_mixin.rb**\n- [ ] :grey_exclamation: - [Line 75](https://github.com/kbrock/manageiq/blob/e68d1e5cdbfc136fb1865ffa82aaa57a55d521f6/app/models/mixins/purging_mixin.rb#L75), Col 121 - [Metrics/LineLength](http://rubydoc.info/gems/rubocop/0.37.2/RuboCop/Cop/Metrics/LineLength) - Line is too long. [124/120]\n"}}
{"actor":{"display_login":"miq-bot"}}