Re: Are stored procedures pre-compiled?

Added to TODO:
* Inline simple SQL functions to avoid overhead (Tom)

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

Tom Lane wrote:
> Neil Conway <nconway@klamath.dyndns.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@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026