Re: Are stored procedures pre-compiled?

Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> writes:
> On Thu, 2002-02-21 at 22:15, Tom Lane wrote:
>> I believe that SQL-language functions don't have any such optimization
>> at all :-( ... I think they are re-parsed and re-planned from scratch
>> on each call.

> Would it be possible to enhance SQL-language functions to cache their
> query plan?

Certainly; if plpgsql can do it, so can SQL functions. You could even
steal (or better, find a way to share) a lot of the code from plpgsql.
But no one's gotten around to it.

A related improvement that's been in the back of my mind for awhile
is to "inline" trivial SQL functions. If you look in pg_proc you'll
find quite a few SQL functions that are just "SELECT
some-arithmetic-expression". I would like to get the planner to expand
those into the parse tree of the invoking query, so that the function
call overhead goes away completely. For example, bit_length(text) is
defined as "select octet_length($1) * 8", so

SELECT bit_length(f1) FROM text_tbl WHERE ...

could be expanded to

SELECT octet_length(f1) * 8 FROM text_tbl WHERE ...

which seems to run about three or four times as fast (though of course
some of that differential would go away given caching of SQL-function
plans).

I don't believe this would be excessively difficult, but it hasn't
gotten to the top of the to-do queue...

regards, tom lane