Re: Performance costs of various PL languages

On 12/27/2011 05:54 PM, Merlin Moncure wrote:
> On Tue, Dec 27, 2011 at 4:20 PM, Pavel Stehule<pavel(dot)stehule(at)gmail(dot)com> wrote:
>> Hello
>>
>> 2011/12/27 Carlo Stonebanks<stonec(dot)register(at)sympatico(dot)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