{"action":"created","issue":{"url":"https://api.github.com/repos/prestodb/presto/issues/5086","repository_url":"https://api.github.com/repos/prestodb/presto","labels_url":"https://api.github.com/repos/prestodb/presto/issues/5086/labels{/name}","comments_url":"https://api.github.com/repos/prestodb/presto/issues/5086/comments","events_url":"https://api.github.com/repos/prestodb/presto/issues/5086/events","html_url":"https://github.com/prestodb/presto/issues/5086","id":150239540,"number":5086,"title":"Optimize union all of similar aggregations","user":{"login":"martint","id":14387,"avatar_url":"https://avatars.githubusercontent.com/u/14387?v=3","gravatar_id":"","url":"https://api.github.com/users/martint","html_url":"https://github.com/martint","followers_url":"https://api.github.com/users/martint/followers","following_url":"https://api.github.com/users/martint/following{/other_user}","gists_url":"https://api.github.com/users/martint/gists{/gist_id}","starred_url":"https://api.github.com/users/martint/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/martint/subscriptions","organizations_url":"https://api.github.com/users/martint/orgs","repos_url":"https://api.github.com/users/martint/repos","events_url":"https://api.github.com/users/martint/events{/privacy}","received_events_url":"https://api.github.com/users/martint/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/prestodb/presto/labels/enhancement","name":"enhancement","color":"d4c5f9"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2016-04-22T01:36:54Z","updated_at":"2016-04-22T02:25:41Z","closed_at":null,"body":"Queries of the following shape:\r\n\r\n```sql\r\n\r\nSELECT \r\n   a1, ..., an,\r\n   agg1(...),\r\n   agg2(...)\r\nFROM t\r\nWHERE f1(...)\r\n\r\nUNION ALL\r\n\r\nSELECT \r\n   b1, ..., bn,\r\n   agg3(...),\r\n   agg4(...)\r\nFROM t\r\nWHERE f2(...)\r\n\r\n...\r\n```\r\n(a1, ..., an, b1, ..., bn are constants)\r\n\r\ncan be optimized to process all the data in a single pass to reduce the number of stages in the query execution.\r\n\r\nOne possible rewrite could be:\r\n\r\n```sql\r\nSELECT * \r\nFROM UNNEST( (\r\n    SELECT ARRAY[\r\n        ROW(a1, ..., an, agg1(...) FILTER (WHERE f1(...)), agg2(...) FILTER (WHERE f1(...)),\r\n        ROW(b1, ..., bn, agg3(...) FILTER (WHERE f2(...)), agg3(...) FILTER (WHERE f2(...)),\r\n        ...]\r\n    FROM t) ) u\r\n```"},"comment":{"url":"https://api.github.com/repos/prestodb/presto/issues/comments/213212860","html_url":"https://github.com/prestodb/presto/issues/5086#issuecomment-213212860","issue_url":"https://api.github.com/repos/prestodb/presto/issues/5086","id":213212860,"user":{"login":"zhenxiao","id":1274155,"avatar_url":"https://avatars.githubusercontent.com/u/1274155?v=3","gravatar_id":"","url":"https://api.github.com/users/zhenxiao","html_url":"https://github.com/zhenxiao","followers_url":"https://api.github.com/users/zhenxiao/followers","following_url":"https://api.github.com/users/zhenxiao/following{/other_user}","gists_url":"https://api.github.com/users/zhenxiao/gists{/gist_id}","starred_url":"https://api.github.com/users/zhenxiao/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zhenxiao/subscriptions","organizations_url":"https://api.github.com/users/zhenxiao/orgs","repos_url":"https://api.github.com/users/zhenxiao/repos","events_url":"https://api.github.com/users/zhenxiao/events{/privacy}","received_events_url":"https://api.github.com/users/zhenxiao/received_events","type":"User","site_admin":false},"created_at":"2016-04-22T02:25:41Z","updated_at":"2016-04-22T02:25:41Z","body":"may I take a try?"}}