pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Functions in Postgres
11+ messages / 5 participants
[nested] [flat]

* Functions in Postgres
@ 2002-04-11 00:35 gordoncl@optusnet.com.au
  2002-04-11 00:55 ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 1 reply; 11+ messages in thread

From: gordoncl@optusnet.com.au @ 2002-04-11 00:35 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Functions in Postgres
  2002-04-11 00:35 Functions in Postgres gordoncl@optusnet.com.au
@ 2002-04-11 00:55 ` Peter Eisentraut <peter_e@gmx.net>
  2002-04-11 20:59   ` Re: Functions in Postgres Gordon Clarke <gordonc@adf-serials.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Peter Eisentraut @ 2002-04-11 00:55 UTC (permalink / raw)
  To: gordoncl@optusnet.com.au; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Functions in Postgres
  2002-04-11 00:35 Functions in Postgres gordoncl@optusnet.com.au
  2002-04-11 00:55 ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
@ 2002-04-11 20:59   ` Gordon Clarke <gordonc@adf-serials.com>
  2002-04-12 16:24     ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 1 reply; 11+ messages in thread

From: Gordon Clarke @ 2002-04-11 20:59 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql

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 three series.
  Numbers    |               http://www.adf-serials.com
-------------+------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Functions in Postgres
  2002-04-11 00:35 Functions in Postgres gordoncl@optusnet.com.au
  2002-04-11 00:55 ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
  2002-04-11 20:59   ` Re: Functions in Postgres Gordon Clarke <gordonc@adf-serials.com>
@ 2002-04-12 16:24     ` Peter Eisentraut <peter_e@gmx.net>
  2002-04-12 17:11       ` Re: Functions in Postgres Tom Lane <tgl@sss.pgh.pa.us>
  2002-04-12 18:07       ` Re: Functions in Postgres Jan Wieck <janwieck@yahoo.com>
  0 siblings, 2 replies; 11+ messages in thread

From: Peter Eisentraut @ 2002-04-12 16:24 UTC (permalink / raw)
  To: Gordon Clarke <gordonc@adf-serials.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Functions in Postgres
  2002-04-11 00:35 Functions in Postgres gordoncl@optusnet.com.au
  2002-04-11 00:55 ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
  2002-04-11 20:59   ` Re: Functions in Postgres Gordon Clarke <gordonc@adf-serials.com>
  2002-04-12 16:24     ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
@ 2002-04-12 17:11       ` Tom Lane <tgl@sss.pgh.pa.us>
  2002-04-12 18:16         ` Re: Functions in Postgres Jan Wieck <janwieck@yahoo.com>
  1 sibling, 1 reply; 11+ messages in thread

From: Tom Lane @ 2002-04-12 17:11 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Gordon Clarke <gordonc@adf-serials.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Functions in Postgres
  2002-04-11 00:35 Functions in Postgres gordoncl@optusnet.com.au
  2002-04-11 00:55 ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
  2002-04-11 20:59   ` Re: Functions in Postgres Gordon Clarke <gordonc@adf-serials.com>
  2002-04-12 16:24     ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
  2002-04-12 17:11       ` Re: Functions in Postgres Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-04-12 18:16         ` Jan Wieck <janwieck@yahoo.com>
  2002-04-12 18:28           ` Re: Functions in Postgres Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 11+ messages in thread

From: Jan Wieck @ 2002-04-12 18:16 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Gordon Clarke <gordonc@adf-serials.com>; pgsql-sql

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 been all
    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 goes first from internal to  string,
    then  from  string  to  Tcl's  internal  and all the way back
    again. Not easy to estimate 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 #





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Functions in Postgres
  2002-04-11 00:35 Functions in Postgres gordoncl@optusnet.com.au
  2002-04-11 00:55 ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
  2002-04-11 20:59   ` Re: Functions in Postgres Gordon Clarke <gordonc@adf-serials.com>
  2002-04-12 16:24     ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
  2002-04-12 17:11       ` Re: Functions in Postgres Tom Lane <tgl@sss.pgh.pa.us>
  2002-04-12 18:16         ` Re: Functions in Postgres Jan Wieck <janwieck@yahoo.com>
@ 2002-04-12 18:28           ` Tom Lane <tgl@sss.pgh.pa.us>
  2002-04-12 18:46             ` Re: Functions in Postgres Jan Wieck <janwieck@yahoo.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Tom Lane @ 2002-04-12 18:28 UTC (permalink / raw)
  To: Jan Wieck <janwieck@yahoo.com>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Gordon Clarke <gordonc@adf-serials.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Functions in Postgres
  2002-04-11 00:35 Functions in Postgres gordoncl@optusnet.com.au
  2002-04-11 00:55 ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
  2002-04-11 20:59   ` Re: Functions in Postgres Gordon Clarke <gordonc@adf-serials.com>
  2002-04-12 16:24     ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
  2002-04-12 17:11       ` Re: Functions in Postgres Tom Lane <tgl@sss.pgh.pa.us>
  2002-04-12 18:16         ` Re: Functions in Postgres Jan Wieck <janwieck@yahoo.com>
  2002-04-12 18:28           ` Re: Functions in Postgres Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-04-12 18:46             ` Jan Wieck <janwieck@yahoo.com>
  2002-04-12 18:58               ` Re: Functions in Postgres Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 11+ messages in thread

From: Jan Wieck @ 2002-04-12 18:46 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jan Wieck <janwieck@yahoo.com>; Peter Eisentraut <peter_e@gmx.net>; Gordon Clarke <gordonc@adf-serials.com>; pgsql-sql

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 #





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Functions in Postgres
  2002-04-11 00:35 Functions in Postgres gordoncl@optusnet.com.au
  2002-04-11 00:55 ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
  2002-04-11 20:59   ` Re: Functions in Postgres Gordon Clarke <gordonc@adf-serials.com>
  2002-04-12 16:24     ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
  2002-04-12 17:11       ` Re: Functions in Postgres Tom Lane <tgl@sss.pgh.pa.us>
  2002-04-12 18:16         ` Re: Functions in Postgres Jan Wieck <janwieck@yahoo.com>
  2002-04-12 18:28           ` Re: Functions in Postgres Tom Lane <tgl@sss.pgh.pa.us>
  2002-04-12 18:46             ` Re: Functions in Postgres Jan Wieck <janwieck@yahoo.com>
@ 2002-04-12 18:58               ` Tom Lane <tgl@sss.pgh.pa.us>
  2002-04-12 19:12                 ` Re: Functions in Postgres Jan Wieck <janwieck@yahoo.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Tom Lane @ 2002-04-12 18:58 UTC (permalink / raw)
  To: Jan Wieck <janwieck@yahoo.com>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Gordon Clarke <gordonc@adf-serials.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Functions in Postgres
  2002-04-11 00:35 Functions in Postgres gordoncl@optusnet.com.au
  2002-04-11 00:55 ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
  2002-04-11 20:59   ` Re: Functions in Postgres Gordon Clarke <gordonc@adf-serials.com>
  2002-04-12 16:24     ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
  2002-04-12 17:11       ` Re: Functions in Postgres Tom Lane <tgl@sss.pgh.pa.us>
  2002-04-12 18:16         ` Re: Functions in Postgres Jan Wieck <janwieck@yahoo.com>
  2002-04-12 18:28           ` Re: Functions in Postgres Tom Lane <tgl@sss.pgh.pa.us>
  2002-04-12 18:46             ` Re: Functions in Postgres Jan Wieck <janwieck@yahoo.com>
  2002-04-12 18:58               ` Re: Functions in Postgres Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-04-12 19:12                 ` Jan Wieck <janwieck@yahoo.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Jan Wieck @ 2002-04-12 19:12 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jan Wieck <janwieck@yahoo.com>; Peter Eisentraut <peter_e@gmx.net>; Gordon Clarke <gordonc@adf-serials.com>; pgsql-sql

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 #





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Functions in Postgres
  2002-04-11 00:35 Functions in Postgres gordoncl@optusnet.com.au
  2002-04-11 00:55 ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
  2002-04-11 20:59   ` Re: Functions in Postgres Gordon Clarke <gordonc@adf-serials.com>
  2002-04-12 16:24     ` Re: Functions in Postgres Peter Eisentraut <peter_e@gmx.net>
@ 2002-04-12 18:07       ` Jan Wieck <janwieck@yahoo.com>
  1 sibling, 0 replies; 11+ messages in thread

From: Jan Wieck @ 2002-04-12 18:07 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Gordon Clarke <gordonc@adf-serials.com>; pgsql-sql

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 (if memory  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 #





^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2002-04-12 19:12 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-11 00:35 Functions in Postgres gordoncl@optusnet.com.au
2002-04-11 00:55 ` Peter Eisentraut <peter_e@gmx.net>
2002-04-11 20:59   ` Gordon Clarke <gordonc@adf-serials.com>
2002-04-12 16:24     ` Peter Eisentraut <peter_e@gmx.net>
2002-04-12 17:11       ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-12 18:16         ` Jan Wieck <janwieck@yahoo.com>
2002-04-12 18:28           ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-12 18:46             ` Jan Wieck <janwieck@yahoo.com>
2002-04-12 18:58               ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-12 19:12                 ` Jan Wieck <janwieck@yahoo.com>
2002-04-12 18:07       ` Jan Wieck <janwieck@yahoo.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox