Re: Nested IMMUTABLE functions

Peter wrote:
> I have two immutable Pl/PG funcs - func A takes a parameter X, looks up
> related value Y from a table and passes Y to func B. Now, if I do something
> like
>
> select A(field_x) from bigtable
>
> it will, of course call A for every single row since paramater is changing.
> However, it also calls func B for every row even though most (actually all)
> related values Y are the same!
>
> Is this by design, or flaw in optimizer? I thought immutable funcs with the
> same arguments are only called once within a scope of single query, and that
> 'select A(...)' should have counted as single query, right?

No, not really.
Its rather that the optimizer doesn't consider the content of any
functions that are called. Mostly since this is near to impossible. So
if you call a function, that function will be executed. Any functions
calls internally will therefore also be executed.

A second point is that the optimizer CANNOT make any assumptions on your
data. Your assumption that you look up a value that is nearly always the
same, is not taken into account by the optimizer.

> This stuff is killing me... func B is small, all table lookups optimized to
> the hilt but still I'm taking major performance hit as it's called
> hundreds/thousands of times.

What you can try is the following:
SELECT B(lookuptable.value)
FROM bigtable INNER JOIN lookuptable ON lookuptable.key =
A(bigtable.whatever)

> Any ideas?

A second part is the cost of the actual function. Depending on the costs
various things might be chosen by the optimizer. This should at least
pull out the lookup from your functions, so the optimizer will take them
into consideration.

I can't tell you how SQL stored procedures are handled, but you can be
pretty sure that any PL/* languages are considered as normal procedure
calls by the optimizer.

- Joris