{"action":"opened","issue":{"url":"https://api.github.com/repos/cloudfoundry/cloud_controller_ng/issues/678","repository_url":"https://api.github.com/repos/cloudfoundry/cloud_controller_ng","labels_url":"https://api.github.com/repos/cloudfoundry/cloud_controller_ng/issues/678/labels{/name}","comments_url":"https://api.github.com/repos/cloudfoundry/cloud_controller_ng/issues/678/comments","events_url":"https://api.github.com/repos/cloudfoundry/cloud_controller_ng/issues/678/events","html_url":"https://github.com/cloudfoundry/cloud_controller_ng/issues/678","id":175985182,"number":678,"title":"Slow api calls for routes and apps endpoints ","user":{"login":"claudio-benfatto","id":7601067,"avatar_url":"https://avatars.githubusercontent.com/u/7601067?v=3","gravatar_id":"","url":"https://api.github.com/users/claudio-benfatto","html_url":"https://github.com/claudio-benfatto","followers_url":"https://api.github.com/users/claudio-benfatto/followers","following_url":"https://api.github.com/users/claudio-benfatto/following{/other_user}","gists_url":"https://api.github.com/users/claudio-benfatto/gists{/gist_id}","starred_url":"https://api.github.com/users/claudio-benfatto/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/claudio-benfatto/subscriptions","organizations_url":"https://api.github.com/users/claudio-benfatto/orgs","repos_url":"https://api.github.com/users/claudio-benfatto/repos","events_url":"https://api.github.com/users/claudio-benfatto/events{/privacy}","received_events_url":"https://api.github.com/users/claudio-benfatto/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2016-09-09T11:12:30Z","updated_at":"2016-09-09T11:12:30Z","closed_at":null,"body":"## Issue\r\n\r\nIn production we experience very slow api calls when hitting certain endpoints\r\n\r\n## Context\r\n\r\nWe are running an on-premise CF installation, and using the following versions:\r\n\r\n- CF version is `237`\r\n- Diego version is `0.1472.0`\r\n- The version of the cf cli command is: `cf version 6.21.0+dff2cf8-2016-07-27`\r\n- We have enabled the newrelic Cloud Controller integration\r\n\r\n## Steps to Reproduce\r\n\r\nhitting the `routes` and `apps` endpoints via cli ie. `cf routes` and `cf apps` or just curling them, runs very slowly for target organizations with many apps deployed and many routes and produce a huge resultset (around *76MB* for an organization with ~300 apps deployed and ~500 routes) \r\n\r\nie:\r\n\r\n```\r\nclaudio@claudio-ThinkPad-T450s:~$ time cf routes\r\nGetting routes for org oscar / space live as admin ...\r\n\r\n....\r\n\r\nreal    2m49.855s\r\nuser    0m2.868s\r\nsys    0m0.232s\r\n```\r\n \r\n## Expected result\r\n\r\nThose api calls should perform much better\r\n\r\n## Current result\r\n\r\nWe noticed the following query is executed examining NewRelic and Postgresql:\r\n\r\n`\r\nSELECT DISTINCT \"apps\".*, \"apps_routes\".\"route_id\" AS \"x_foreign_key_x\" FROM \"apps\" INNER JOIN \"apps_routes\" ON (\"apps_routes\".\"app_id\" = \"apps\".\"id\") WHERE (\"apps_routes\".\"route_id\" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) ORDER BY \"id\" ASC\r\n`\r\n\r\nwith a running time of: 122,000 ms\r\n\r\nMoreover while analysing the same query in postgresql we noticed that an ordering step is involved (as expected) :\r\n` Sort (cost=5049.25..5055.61 rows=2545 width=1454)`\r\n\r\nbut that this ordering operation is using as **Sort Key** all the columns in the SELECT . This involves a quicksort on a very huge resultset against many text columns and seems to be utterly inefficient.\r\n\r\nRunning the same query with : \r\n`SELECT DISTINCT ON (apps.id) ... ` solves the problem, as well as eliminating the `ORDER BY` clause in the end (because postgresql in this case uses a more efficient `GROUP BY` operations behind the curtains)\r\n![slow_query](https://cloud.githubusercontent.com/assets/7601067/18384990/b4fd2bfc-7685-11e6-88d9-39ca7f1e2db0.png)\r\n\r\nLet me know if you need more data. I can produce it on request.\r\n\r\nThank you for your help,\r\nClaudio"}}
{"actor":{"display_login":"claudio-benfatto"}}