Re: func() & select func()

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> 1) Using non-cachable function f()
>   =# explain select * from pg_class where oid=f(1259);
>   Seq Scan on pg_class  (cost=0.00..3.17 rows=1 width=92) 

> 2) Using select f() 
>  =# explain select * from pg_class where oid=(select f(1259)); 
>   Index Scan using pg_class_oid_index on pg_class  (cost=0.00..2.01
>     rows=1 width=92)
>     InitPlan
-> Result  (cost=0.00..0.00 rows=0 width=0) 

The sub-select is reduced to an initplan --- ie, executed only once,
not once per row --- because it has no dependency on the outer select.

Currently we do not consider the presence of noncachable functions as
a reason that prevents reducing a subplan to an initplan.  I thought
about it but didn't like the performance penalty.  It seems to me that
it's debatable which is the correct semantics, anyway.  Arguably an
outer select *should* assume that a parameterless inner select yields
constant results --- if you don't assume that then it makes no sense
to do joins over the results of sub-SELECTs in FROM, which is a feature
required by full SQL...
        regards, tom lane