Thread: Functions in Postgres

Functions in Postgres

From
gordoncl@optusnet.com.au
Date:
Greetings All,

Just a quick question about functions.

Do all functions get compiled in Postgresql? I know functions with trusted 
languages like PL/pgsql and PL/TCL etc do, but what about functions written in 
SQL?

Thanks for your responses.

Cheers...Gordon


Re: Functions in Postgres

From
Peter Eisentraut
Date:
gordoncl@optusnet.com.au writes:

> Do all functions get compiled in Postgresql? I know functions with trusted
> languages like PL/pgsql and PL/TCL etc do, but what about functions written in
> SQL?

Each language handler is free to interpret the source text in its own way.
This has nothing to do with trustedness (is SQL not trusted?).  Neither
PL/pgSQL nor PL/Tcl involve a compilation step, although PL/pgSQL does
some optimizations internally that could be called compilation.

Is this question just out of random interest, or are you really asking
something like are functions fast?

-- 
Peter Eisentraut   peter_e@gmx.net



Re: Functions in Postgres

From
Peter Eisentraut
Date:
Gordon Clarke writes:

> I was asking to see if there was any speed penalty with coding a fn in SQL
> as opposed to PL/pgsql. It appears that there is and from what you've said
> PL/pgsql is slightly faster than PL/TCL and probably PL/Perl. Am I correct
> in drawing that conclusion?

Yes, that seems about right.

-- 
Peter Eisentraut   peter_e@gmx.net



Re: Functions in Postgres

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> Gordon Clarke writes:
>> I was asking to see if there was any speed penalty with coding a fn in SQL
>> as opposed to PL/pgsql. It appears that there is and from what you've said
>> PL/pgsql is slightly faster than PL/TCL and probably PL/Perl. Am I correct
>> in drawing that conclusion?

> Yes, that seems about right.

plpgsql should be noticeably faster for issuing repetitive SQL queries,
since it caches query plans and the other two do not.  However, I expect
the reverse is true for simple calculations (arithmetic, text-mashing,
etc).  plpgsql turns *every* expression into an SQL SELECT query; even
with caching of query plans, I really doubt that our SQL engine can do
expressions faster than Tcl or Perl can.  (Note that both of those do
some amount of pre-parsing of function text themselves, so that the
caching of query plans in itself gives no advantage to plpgsql for
expressions.)  And of course there are many areas where either Tcl or
Perl will win hands down for functionality, for example pattern match
or text substitution tasks.

AFAIK no one has done any actual comparisons to prove or disprove these
theories, though.  It'd be interesting to code up equivalent functions
in all three PL languages and do some benchmarking.  Any volunteers out
there?
        regards, tom lane


Re: Functions in Postgres

From
Jan Wieck
Date:
Peter Eisentraut wrote:
> Gordon Clarke writes:
>
> > I was asking to see if there was any speed penalty with coding a fn in SQL
> > as opposed to PL/pgsql. It appears that there is and from what you've said
> > PL/pgsql is slightly faster than PL/TCL and probably PL/Perl. Am I correct
> > in drawing that conclusion?
>
> Yes, that seems about right.
   I  would  say it depends pretty much on what that function is   doing.   PL/pgSQL  is  not  the  best  answer  to
do string   manipulation  and  such.  PL/Tcl can do that alot better, Tcl   has it's own bytecode compiler since 8.0
(ifmemory  serves),   and  if  the  programmer  cares,  it can cache query plans as   well. As allways
 
   a)  Using the right tool for  a  problem  can  lead  to  good       results.
   b)  A fool with a tool is still a fool.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #




Re: Functions in Postgres

From
Jan Wieck
Date:
Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Gordon Clarke writes:
> >> I was asking to see if there was any speed penalty with coding a fn in SQL
> >> as opposed to PL/pgsql. It appears that there is and from what you've said
> >> PL/pgsql is slightly faster than PL/TCL and probably PL/Perl. Am I correct
> >> in drawing that conclusion?
>
> > Yes, that seems about right.
>
> plpgsql should be noticeably faster for issuing repetitive SQL queries,
> since it caches query plans and the other two do not.  However, I expect
   In  PL/Tcl, it's the programmer who has to explicitly ask for   query plan caching. It's definitely possible and has
beenall   the time.
 

> the reverse is true for simple calculations (arithmetic, text-mashing,
> etc).  plpgsql turns *every* expression into an SQL SELECT query; even
> with caching of query plans, I really doubt that our SQL engine can do
> expressions faster than Tcl or Perl can.  (Note that both of those do
   PL/pgSQL  does  turn every expression into a SELECT, but then   it analyzes the targetlist a little and if it's
just a  few   nested  function  calls with parameters or such, it creates a   suitable executor context and calls
ExecEvalExpr() directly,   without  going  through  SPI. Since Tcl's interface is purely   string based, everything
goesfirst from internal to  string,   then  from  string  to  Tcl's  internal  and all the way back   again. Not easy
toestimate who will win here.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #




Re: Functions in Postgres

From
Tom Lane
Date:
Jan Wieck <janwieck@yahoo.com> writes:
>     .... Since Tcl's interface is purely
>     string based, everything goes first from internal to  string,
>     then  from  string  to  Tcl's  internal  and all the way back
>     again.

Not since about Tcl 8.0.

> Not easy to estimate who will win here.

Agreed, some hard numbers would be more useful than speculation.
        regards, tom lane


Re: Functions in Postgres

From
Jan Wieck
Date:
Tom Lane wrote:
> Jan Wieck <janwieck@yahoo.com> writes:
> >     .... Since Tcl's interface is purely
> >     string based, everything goes first from internal to  string,
> >     then  from  string  to  Tcl's  internal  and all the way back
> >     again.
>
> Not since about Tcl 8.0.
   You're right, not Tcl's fault. Noone changed PL/Tcl to use it   though. It  still  does  Tcl_CreateCommand(),  which
is  the   backward   compatibility   pure   string  interface  wrapping   technology. :-)
 


Jan

> > Not easy to estimate who will win here.
>
> Agreed, some hard numbers would be more useful than speculation.
>
>              regards, tom lane
>


--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #




Re: Functions in Postgres

From
Tom Lane
Date:
Jan Wieck <janwieck@yahoo.com> writes:
> Tom Lane wrote:
>> Not since about Tcl 8.0.

>     You're right, not Tcl's fault. Noone changed PL/Tcl to use it
>     though. It  still  does  Tcl_CreateCommand(),  which  is  the
>     backward   compatibility   pure   string  interface  wrapping
>     technology. :-)

I could be mistaken, but I thought Tcl would still reduce the function
text to bytecode behind-the-scenes.

We are losing with unnecessary text conversions in a number of places in
pltcl, but I think it's just in parameter passing at pltcl function
entry/exit and calls to the special Tcl commands provided by pltcl.
If you're executing regular Tcl commands inside a pltcl function,
I think you still will get benefit from Tcl 8's speedups.
        regards, tom lane


Re: Functions in Postgres

From
Jan Wieck
Date:
Tom Lane wrote:
> Jan Wieck <janwieck@yahoo.com> writes:
> > Tom Lane wrote:
> >> Not since about Tcl 8.0.
>
> >     You're right, not Tcl's fault. Noone changed PL/Tcl to use it
> >     though. It  still  does  Tcl_CreateCommand(),  which  is  the
> >     backward   compatibility   pure   string  interface  wrapping
> >     technology. :-)
>
> I could be mistaken, but I thought Tcl would still reduce the function
> text to bytecode behind-the-scenes.
>
> We are losing with unnecessary text conversions in a number of places in
> pltcl, but I think it's just in parameter passing at pltcl function
> entry/exit and calls to the special Tcl commands provided by pltcl.
> If you're executing regular Tcl commands inside a pltcl function,
> I think you still will get benefit from Tcl 8's speedups.
   Absolutely. It's only all the SPI_* crap and parameter/result   conversion.  Inside Tcl it's calm like in  the
center of  a   hurricane, the problem is just getting in and out...
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #




Re: Functions in Postgres

From
Gordon Clarke
Date:
On Wed, 10 Apr 2002, Peter Eisentraut wrote:

> Each language handler is free to interpret the source text in its own way.
> This has nothing to do with trustedness (is SQL not trusted?).  Neither
> PL/pgSQL nor PL/Tcl involve a compilation step, although PL/pgSQL does
> some optimizations internally that could be called compilation.
> 
> Is this question just out of random interest, or are you really asking
> something like are functions fast?

I was asking to see if there was any speed penalty with coding a fn in SQL
as opposed to PL/pgsql. It appears that there is and from what you've said
PL/pgsql is slightly faster than PL/TCL and probably PL/Perl. Am I correct
in drawing that conclusion?

Thanks for your help.

-- 
Cheers...Gordon
-------------+------------------------------------------------------------
ADF Aircraft | Cataloguing the serial numbers of Australian Defence Force  Serial    |            aircraft for all the
threeseries. Numbers    |               http://www.adf-serials.com
 
-------------+------------------------------------------------------------