Re: Are stored procedures pre-compiled?

Added to TODO:

* Inline simple SQL functions to avoid overhead (Tom)

---------------------------------------------------------------------------

Tom Lane wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026