This query surprised me. I expected us to do the Aggregate once for all the
aggregate functions in the select target which is what normally happens. If I
simplify the query further it actually does so.
I don't really understand what's going on here. It can't be the volatile
random() because in fact even if I make them depend on the random value the
subplans are executed with the same parameter values anyways and the sums end
up being the same.
postgres=# postgres=# explain select sum(n),sum(n) from (select (select count(*) as n from a ) as n from
(selectrandom() as s) as xyzzy) as xyzzy ;
QUERY PLAN
-----------------------------------------------------------------------Aggregate (cost=5676.06..5676.07 rows=1
width=0) InitPlan -> Aggregate (cost=2838.00..2838.01 rows=1 width=0) -> Seq Scan on a
(cost=0.00..2588.00rows=100000 width=0) -> Aggregate (cost=2838.00..2838.01 rows=1 width=0) -> Seq Scan
ona (cost=0.00..2588.00 rows=100000 width=0) -> Result (cost=0.00..0.01 rows=1 width=0)
(7 rows)
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production
Tuning