Re: ARRAY(subquery) volatility

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