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


Fri, 22 Feb 2002 10:31:07 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Are stored procedures pre-compiled?
"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

Thu, 21 Feb 2002 22:15:44 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Are stored procedures pre-compiled? 
"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

21 Feb 2002 22:23:23 -0500
Doug McNaught <doug@wireboard.com>
Re: Are stored procedures pre-compiled?
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

Thu, 21 Feb 2002 23:11:57 -0500
Tom Lane <tgl@sss.pgh.pa.us>
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, Pennsylvania 19026

Thu, 21 Feb 2002 23:17:48 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Are stored procedures pre-compiled?
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


22 Feb 2002 00:15:47 -0500
Neil Conway <nconway@klamath.dyndns.org>
Re: Are stored procedures pre-compiled?

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, Pennsylvania 19026

Fri, 22 Feb 2002 00:22:02 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Are stored procedures pre-compiled?
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, Pennsylvania 19026

Fri, 22 Feb 2002 00:42:36 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Are stored procedures pre-compiled?