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

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

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

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