Thread: Are stored procedures pre-compiled?
Hi Hackers, Are stored sql or pl/pgsql (and in fact other langs) precompiled on Postgres for efficiency??? This is a question that came up as part of GeekLog development... Thanks, Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes: > Are stored sql or pl/pgsql (and in fact other langs) precompiled on Postgres > for efficiency??? plpgsql is, see http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/plpgsql.html#PLPGSQL-OVERVIEW although "compiling" might be a bit of a strong word for producing query plans in advance. One should certainly not mistake plpgsql for a compiled-language substitute. It's great for issuing queries but not for adding 2 and 2 to get 4. plperl and pltcl do whatever the underlying implementations of those languages do. Dunno about plpython. 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. regards, tom lane
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes: > Hi Hackers, > > Are stored sql or pl/pgsql (and in fact other langs) precompiled on Postgres > for efficiency??? I'm not sure what happens with SQL, I think it's just stored as text. PL/pgSQL is parsed and stored as a parse tree for execution. Perl does basically the same thing IIRC, and Python compiles to byte-code. I'm not sure about TCL... -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
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
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
On Thu, 2002-02-21 at 23:11, 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. Okay, I'll take a look at this and see if I can implement it. Bruce, can you add this to do the TODO list (it may be there already, I just couldn't see it), and add me as responsible for it? Thanks. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
Already on TODO, your name added. --------------------------------------------------------------------------- Neil Conway wrote: > On Thu, 2002-02-21 at 23:11, 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. > > Okay, I'll take a look at this and see if I can implement it. > > Bruce, can you add this to do the TODO list (it may be there already, I > just couldn't see it), and add me as responsible for it? Thanks. > > Cheers, > > Neil > > -- > Neil Conway <neilconway@rogers.com> > PGP Key ID: DB3C29FC > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- 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
Bruce Momjian wrote: > > Already on TODO, your name added. Sorry, got it wrong. It is now: > * Inline simple SQL functions to avoid overhead (Tom) > * Precompile SQL functions to avoid overhead (Neil Conway) -- 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