Re: Performance PLV8 vs PLPGSQL

2016-12-28 10:46 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

> Hi
>
> 2016-12-28 10:15 GMT+01:00 Tim Uckun <timuckun(at)gmail(dot)com>:
>
>> I have seen various links on the internet which indicate that PLV8 is
>> significantly faster than PL-PGSQL sometimes an order of magnitude faster.
>>
>> Is this uniformly true or is it just in certain circumstances?
>>
>
> It depends on usage
>
>
>>
>> Is there any benefit to choosing PL-PGSQL?
>>
>
> there are more benefits
>
> 0. this language has integrated SQL - the code with lot of SQL is more
> readable.
> 1. it is everywhere, where is Postgres
> 2. it uses same data types like Postgres, there is not any conversion
> related overhead
> 3. this engine has quick start, faster than anything else.
>

4. It is not slow language - it is AST interpret - it is significantly
faster than byte code based interpreter without JIT. The expression are
slower - the PostgerSQL expression interpret. This is bottle neck of
PLpgSQL performance - on second hand - it is main benefit of PLpgSQL -
there is full integration with PostgreSQL runtime without any exceptions.

Years ago PLpgSQL has very slow operations on arrays - this was fixed in
9.5. Still PLpgSQL has slow string updates - Strings are immutable in
PLpgSQL - so update means generating new string. But usually it is not
bottleneck in PL environment.

Regards

Pavel

>
>
>>
>> Is there work going on to make PL-PGSQL more performant or has it gotten
>> significantly faster in the last two years or so (some of the links are a
>> from a while ago).
>>
>
> What I know no. There is not any reason why to do it. This language is
> designed be glue of SQL statements. Nothing more, nothing less. I did
> tests, and it is significantly faster than SQL engine.
>
> Some years ago I though about compilation to C language, but the target
> was better obfuscation not performance.
>
> PLpgSQL is not designed for hard calculation - the critical path is in SQL
> always. If you need different performance, than you can use PLV8, PLPython
> or native C extension. Postgres has very good API for writing C extensions.
>
> Regards
>
> Pavel
>
>
>>
>> Thanks.
>>
>
>