From: Andrew Dunstan <andrew@dunslane.net>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Carlo Stonebanks <stonec.register@sympatico.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance costs of various PL languages
Date: Tue, 27 Dec 2011 18:11:47 -0500
Message-ID: <4EFA50B3.4010800@dunslane.net> (raw)
In-Reply-To: <CAHyXU0w=UfdvxpngTmqcUKJUcQ_Q+zbec6eNyQKqavaR5aHimQ@mail.gmail.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@dunslane.net, mmoncure@gmail.com, pavel.stehule@gmail.com, stonec.register@sympatico.ca
Subject: Re: Performance costs of various PL languages
In-Reply-To: <4EFA50B3.4010800@dunslane.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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