Message-ID: <4EFA50B3.4010800@dunslane.net>
Date: Tue, 27 Dec 2011 18:11:47 -0500
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:8.0) Gecko/20111115 Thunderbird/8.0
MIME-Version: 1.0
To: Merlin Moncure <mmoncure@gmail.com>
CC: Pavel Stehule <pavel.stehule@gmail.com>,
	Carlo Stonebanks <stonec.register@sympatico.ca>,
	pgsql-performance@postgresql.org
Subject: Re: Performance costs of various PL languages
References: <hsv49v$312j$1@news.hub.org>
	<4BFE8F4F0200002500031BA2@gw.wicourts.gov>
	<295DF03C1B134B0BBD05A8F54D985C76@CAPRICA>
	<CAFj8pRB-D6mmLQeffncWo-QrtDLW_ynwhQjnZsSzNfb0t_gQLQ@mail.gmail.com>
	<CAHyXU0w=UfdvxpngTmqcUKJUcQ_Q+zbec6eNyQKqavaR5aHimQ@mail.gmail.com>
In-Reply-To: 
 <CAHyXU0w=UfdvxpngTmqcUKJUcQ_Q+zbec6eNyQKqavaR5aHimQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


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