Michael Fuhr <mike@fuhr.org> writes:
> Why does the first query below return the same value for each row
> while the second query returns random values? Planner optimization?
> test=> SELECT ARRAY(SELECT random()) FROM generate_series(1, 5);
> test=> SELECT ARRAY(SELECT random() + x * 0) FROM generate_series(1, 5) AS g(x);
The sub-SELECT in the first one is considered an uncorrelated subquery,
so you get a plan that evaluates the subquery just once:
Function Scan on generate_series (cost=0.01..12.51 rows=1000 width=0) InitPlan -> Result (cost=0.00..0.01 rows=1
width=0)
In the second case, x is an outer parameter to the subquery, so it has
to be re-evaluated for each row of the outer query:
Function Scan on generate_series g (cost=0.00..32.50 rows=1000 width=4) SubPlan -> Result (cost=0.00..0.02
rows=1width=0)
Note the "InitPlan" vs "SubPlan" labels --- they look similar, but the
evaluation rules are totally different.
The fact that there's a volatile function in the subquery isn't
considered while making this decision. I'm not sure if it should be.
regards, tom lane
