Re: func() & select func()

"Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)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