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 (select random() 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.00 rows=100000 width=0)
-> Aggregate (cost=2838.00..2838.01 rows=1 width=0)
-> Seq Scan on a (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 from a ) 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.00 rows=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