Re: Performance costs of various PL languages - Mailing list pgsql-performance
From | Andrew Dunstan |
---|---|
Subject | Re: Performance costs of various PL languages |
Date | |
Msg-id | 4EFA50B3.4010800@dunslane.net Whole thread Raw |
In response to | Re: Performance costs of various PL languages (Merlin Moncure <mmoncure@gmail.com>) |
List | pgsql-performance |
On 12/27/2011 05:54 PM, Merlin Moncure wrote: > On Tue, Dec 27, 2011 at 4:20 PM, Pavel Stehule<pavel.stehule@gmail.com> wrote: >> Hello >> >> 2011/12/27 Carlo Stonebanks<stonec.register@sympatico.ca>: >>> 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
pgsql-performance by date: