"Craig A. James" <cjames@modgraph-usa.com> writes:
> But the weird thing is that, in spite of flattening, which would appear to make the queries equivalent, the function
getscalled in one case, and not in the other.
No, nothing particularly weird about it. ORDER BY in a subselect
acts as an "optimization fence" that prevents flattening. An
un-flattened subquery will evaluate all its output columns whether the
parent query reads them or not. (This is not set in stone mind you,
but in the current planner implementation it's hard to avoid, because
such a sub-query gets planned before we've figured out which columns
the parent wants to reference.) The cases in which you had the function
in a subquery without ORDER BY were flattenable, and in that case the
planner threw the function expression away as being unreferenced.
regards, tom lane