Thread: Strange query plan with redundant aggregate nodes

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

Gregory Stark <stark@enterprisedb.com> writes:
> I don't really understand what's going on here.

It's flattening the sub-select, converting

select sum(n),sum(n)   from (select (select count(*) as n from a ) as n            from (select random() as s) as
xyzzy)as xyzzy ;
 

to

select sum((select count(*) from a)), sum((select count(*) from a))   from (select random() as s) as xyzzy;

Maybe we could stop it from doing that when there are sub-selects in the
sub-select's targetlist, but I'm afraid that would make other cases
worse.

BTW, in CVS HEAD it looks like this

regression=# explain verbose select sum(n),sum(n)                                   from (select (select count(*) as n
froma ) as n            from (select random() as s) as xyzzy) as xyzzy ;                           QUERY PLAN
                 
 
-------------------------------------------------------------------Aggregate  (cost=80.06..80.07 rows=1 width=0)
Output:sum($0), sum($1)  InitPlan 1 (returns $0)    ->  Aggregate  (cost=40.00..40.01 rows=1 width=0)          Output:
count(*)         ->  Seq Scan on a  (cost=0.00..34.00 rows=2400 width=0)                Output: public.a.f1  InitPlan 2
(returns$1)    ->  Aggregate  (cost=40.00..40.01 rows=1 width=0)          Output: count(*)          ->  Seq Scan on a
(cost=0.00..34.00rows=2400 width=0)                Output: public.a.f1  ->  Result  (cost=0.00..0.01 rows=1 width=0)
   Output: random()
 
(14 rows)

which makes it at least a little clearer where the subplans are
connected to ...
        regards, tom lane