Thread: Functions in Postgres
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
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
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
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
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 #
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 #
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
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 #
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
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 #
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 -------------+------------------------------------------------------------