Re: Function is called multiple times in subselect

Chris Campbell <chris(at)bignerdranch(dot)com> writes:
> Or is there a way to advise the optimizer of the cost of my function, so
> that it will choose to not flatten the subquery (since the total cost of
> doing that will be higher)?

Not at present. AFAIR, we have removed all traces of Joe Hellerstein's
thesis work on optimizing expensive functions ;-). The thesis work
itself wasn't bad, but it depended on knowing a great deal about the
behavior of arbitrary functions, which isn't a very tenable assumption
in the real world.

> Or should the optimizer be assuming that
> function calls are fairly expensive by default,

Certainly not. Remember that every comparison and arithmetic operator
is a function call under the hood.

We could possibly think about a simple binary classification "cheap" vs
"expensive", with the consequence being that the optimizer would try to
avoid duplicating expressions containing any "expensive" functions.
I'm not at all sure what it would take to implement that, though.

regards, tom lane