pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunctions in Postgres
11+ messages / 5 participants
[nested] [flat]
* Functions in Postgres
@ 2002-04-11 00:35 gordoncl@optusnet.com.au
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:55 Peter Eisentraut <peter_e@gmx.net>
parent: gordoncl@optusnet.com.au
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 20:59 Gordon Clarke <gordonc@adf-serials.com>
parent: 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-12 16:24 Peter Eisentraut <peter_e@gmx.net>
parent: Gordon Clarke <gordonc@adf-serials.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-12 17:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
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-12 18:07 Jan Wieck <janwieck@yahoo.com>
parent: Peter Eisentraut <peter_e@gmx.net>
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
* Re: Functions in Postgres
@ 2002-04-12 18:16 Jan Wieck <janwieck@yahoo.com>
parent: 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-12 18:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: 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-12 18:46 Jan Wieck <janwieck@yahoo.com>
parent: 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-12 18:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: 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-12 19:12 Jan Wieck <janwieck@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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
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