{"action":"created","issue":{"url":"https://api.github.com/repos/cyverse/DE/issues/329","repository_url":"https://api.github.com/repos/cyverse/DE","labels_url":"https://api.github.com/repos/cyverse/DE/issues/329/labels{/name}","comments_url":"https://api.github.com/repos/cyverse/DE/issues/329/comments","events_url":"https://api.github.com/repos/cyverse/DE/issues/329/events","html_url":"https://github.com/cyverse/DE/pull/329","id":173368447,"number":329,"title":"Use temporary tables rather than CTEs to speed up certain paged directory listing queries","user":{"login":"ianmcorvidae","id":106004,"avatar_url":"https://avatars.githubusercontent.com/u/106004?v=3","gravatar_id":"","url":"https://api.github.com/users/ianmcorvidae","html_url":"https://github.com/ianmcorvidae","followers_url":"https://api.github.com/users/ianmcorvidae/followers","following_url":"https://api.github.com/users/ianmcorvidae/following{/other_user}","gists_url":"https://api.github.com/users/ianmcorvidae/gists{/gist_id}","starred_url":"https://api.github.com/users/ianmcorvidae/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ianmcorvidae/subscriptions","organizations_url":"https://api.github.com/users/ianmcorvidae/orgs","repos_url":"https://api.github.com/users/ianmcorvidae/repos","events_url":"https://api.github.com/users/ianmcorvidae/events{/privacy}","received_events_url":"https://api.github.com/users/ianmcorvidae/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"dennisroberts71","id":415143,"avatar_url":"https://avatars.githubusercontent.com/u/415143?v=3","gravatar_id":"","url":"https://api.github.com/users/dennisroberts71","html_url":"https://github.com/dennisroberts71","followers_url":"https://api.github.com/users/dennisroberts71/followers","following_url":"https://api.github.com/users/dennisroberts71/following{/other_user}","gists_url":"https://api.github.com/users/dennisroberts71/gists{/gist_id}","starred_url":"https://api.github.com/users/dennisroberts71/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dennisroberts71/subscriptions","organizations_url":"https://api.github.com/users/dennisroberts71/orgs","repos_url":"https://api.github.com/users/dennisroberts71/repos","events_url":"https://api.github.com/users/dennisroberts71/events{/privacy}","received_events_url":"https://api.github.com/users/dennisroberts71/received_events","type":"User","site_admin":false},"assignees":[{"login":"dennisroberts71","id":415143,"avatar_url":"https://avatars.githubusercontent.com/u/415143?v=3","gravatar_id":"","url":"https://api.github.com/users/dennisroberts71","html_url":"https://github.com/dennisroberts71","followers_url":"https://api.github.com/users/dennisroberts71/followers","following_url":"https://api.github.com/users/dennisroberts71/following{/other_user}","gists_url":"https://api.github.com/users/dennisroberts71/gists{/gist_id}","starred_url":"https://api.github.com/users/dennisroberts71/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dennisroberts71/subscriptions","organizations_url":"https://api.github.com/users/dennisroberts71/orgs","repos_url":"https://api.github.com/users/dennisroberts71/repos","events_url":"https://api.github.com/users/dennisroberts71/events{/privacy}","received_events_url":"https://api.github.com/users/dennisroberts71/received_events","type":"User","site_admin":false}],"milestone":null,"comments":5,"created_at":"2016-08-26T04:35:59Z","updated_at":"2016-08-26T19:24:54Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/cyverse/DE/pulls/329","html_url":"https://github.com/cyverse/DE/pull/329","diff_url":"https://github.com/cyverse/DE/pull/329.diff","patch_url":"https://github.com/cyverse/DE/pull/329.patch"},"body":"PostgreSQL is rubbish at predicting the number of rows for CTE Scans, which goes really badly when the answer is 16000 and it guesses 25.\r\n\r\nIt doesn't have this issue with temporary tables being created instead, so this does that."},"comment":{"url":"https://api.github.com/repos/cyverse/DE/issues/comments/242828466","html_url":"https://github.com/cyverse/DE/pull/329#issuecomment-242828466","issue_url":"https://api.github.com/repos/cyverse/DE/issues/329","id":242828466,"user":{"login":"ianmcorvidae","id":106004,"avatar_url":"https://avatars.githubusercontent.com/u/106004?v=3","gravatar_id":"","url":"https://api.github.com/users/ianmcorvidae","html_url":"https://github.com/ianmcorvidae","followers_url":"https://api.github.com/users/ianmcorvidae/followers","following_url":"https://api.github.com/users/ianmcorvidae/following{/other_user}","gists_url":"https://api.github.com/users/ianmcorvidae/gists{/gist_id}","starred_url":"https://api.github.com/users/ianmcorvidae/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ianmcorvidae/subscriptions","organizations_url":"https://api.github.com/users/ianmcorvidae/orgs","repos_url":"https://api.github.com/users/ianmcorvidae/repos","events_url":"https://api.github.com/users/ianmcorvidae/events{/privacy}","received_events_url":"https://api.github.com/users/ianmcorvidae/received_events","type":"User","site_admin":false},"created_at":"2016-08-26T19:24:54Z","updated_at":"2016-08-26T19:24:54Z","body":"@tedgin kind of a mix, mostly observation/experience. I've seen a decent number of queries in the past which have had bad misestimations due to it choosing either 1 or 25 (I've never seen any other value, that I can think of) for the size of a CTE. I tried to find an article or something to cite about this, but couldn't really find much -- there's a few threads on the postgresql mailing lists about misestimation issues using CTEs which seemed potentially related (and one which recommended using temporary tables instead where possible), and there's stuff all over the place about CTEs being optimization fences (but that's a different problem than this, as far as I can determine -- it's possible that inlining these rather than using WITH or temp tables would have different performance characteristics too though). Given the little bit I found, I thought using temporary tables would be a nice thing to try and was pleasantly surprised at how well it worked!"}}
{"actor":{"display_login":"ianmcorvidae"}}