On 12/27/2011 05:54 PM, Merlin Moncure wrote:
On Tue, Dec 27, 2011 at 4:20 PM, Pavel Stehule<pavel.stehule@xxxxxxxxx> wrote:
Hello
2011/12/27 Carlo Stonebanks<stonec.register@xxxxxxxxxxxx>:
We are currently using pltclu as our PL of choice AFTER plpgSql.
I'd like to know if anyone can comment on the performance costs of the
various PL languages BESIDES C. For example, does pltclu instantiate faster
than pltcl (presumably because it uses a shared interpreter?) Is Perl more
lightweight?
I know that everything depends on context - what you are doing with it, e.g.
choose Tcl for string handling vs. Perl for number crunching - but for those
who know about this, is there a clear performance advantage for any of the
various PL languages - and if so, is it a difference so big to be worth
switching?
I ask this because I had expected to see pl/pgsql as a clear winner in terms
of performance over pltclu, but my initial test showed the opposite. I know
this may be an apples vs oranges problem and I will test further, but if
anyone has any advice or insight, I would appreciate it so I can tailor my
tests accordingly.
A performance strongly depends on use case.
PL/pgSQL has fast start but any expression is evaluated as simple SQL
expression - and some repeated operation should be very expensive -
array update, string update. PL/pgSQL is best as SQL glue. Positive to
performance is type compatibility between plpgsql and Postgres.
Interpret plpgsql is very simply - there are +/- zero optimizations -
plpgsql code should be minimalistic, but when you don't do some really
wrong, then a speed is comparable with PHP.
http://www.pgsql.cz/index.php/PL/pgSQL_%28en%29#Inappropriate_use_of_the_PL.2FpgSQL_language
PL/Perl has slower start - but string or array operations are very
fast. Perl has own expression evaluator - faster than expression
evaluation in plpgsql. On second hand - any input must be transformed
from postgres format to perl format and any result must be transformed
too. Perl and other languages doesn't use data type compatible with
Postgres.
One big advantage pl/pgsql has over scripting languages is that it
understands postgresql types natively. It knows what a postgres array
is, and can manipulate one directly. pl/perl would typically have to
have the database convert it to a string, parse it into a perl
structure, do the manipulation, then send it to the database to be
parsed again. If your procedure code is mainly moving data between
tables and doing minimal intermediate heavy processing, this adds up
to a big advantage. Which pl to go with really depends on what you
need to do. pl/pgsql is always my first choice though.
perl and tcl are not particularly fast languages in the general case
-- you are largely at the mercy of how well the language's syntax or
library features map to the particular problem you're solving. if you
need a fast general purpose language in the backend and are (very
understandably) skeptical about C, I'd look at pl/java.
PLV8, which is not yet ready for prime time, maps many common Postgres
types into native JS types without the use of Input/Output functions,
which means the conversion is very fast. It's work which could very
well do with repeating for the other PL's.
cheers
andrew
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
