Re: Stored Procedure Performance

Thanks Pavel. Our SPs are not doing any mathematical calculations. Its
mostly if-else, so I would expect good performance.

On 11 October 2017 at 19:50, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

>
>
> 2017-10-11 15:59 GMT+02:00 Purav Chovatia <puravc(at)gmail(dot)com>:
>
>> Thanks Laurenz, am having a look at perf.
>>
>> Can you pls help understand what exactly do you mean when you say "PL/pgSQL
>> is not optimized for performance like PL/SQL". Do you mean to indicate that
>> app firing queries/DMLs directly would be a better option as compared to
>> putting those in Stored Procs?
>>
>
> PL/pgSQL is perfect glue for SQL. SQL queries has same speed without
> dependency on environment that executed it.
>
> This sentence mean, so PLpgSQL is not designed for intensive mathematics
> calculation. PL/SQL is self govering environment ... it has own data
> types, it has own implementation of logical and mathematics operators.
> PLpgSQL is layer over SQL engine - and has not own types, has not own
> operators. Any expression is translated to SQL and then is interpreted by
> SQL expression interpret. Maybe in next few years there will be a JIT
> compiler. But it is not now. This is current bottleneck of PLpgSQL. If your
> PL code is glue for SQL queries (implementation of some business
> processes), then PLpgSQL is fast enough. If you try to calculate numeric
> integration or derivation of some functions, then PLpgSQL is slow. It is
> not too slow - the speed is comparable with PHP, but it is significantly
> slower than C language.
>
> PostgreSQL has perfect C API - so intensive numeric calculations are
> usually implemented as C extension.
>
> Regards
>
> Pavel
>
>
>>
>> Regards
>>
>> On 3 October 2017 at 20:24, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
>> wrote:
>>
>>> Purav Chovatia wrote:
>>> > I come from Oracle world and we are porting all our applications to
>>> postgresql.
>>> >
>>> > The application calls 2 stored procs,
>>> > - first one does a few selects and then an insert
>>> > - second one does an update
>>> >
>>> > The main table on which the insert and the update happens is truncated
>>> before every performance test.
>>> >
>>> > We are doing about 100 executions of both of these stored proc per
>>> second.
>>> >
>>> > In Oracle each exec takes about 1millisec whereas in postgres its
>>> taking 10millisec and that eventually leads to a queue build up in our
>>> application.
>>> >
>>> > All indices are in place. The select, insert & update are all single
>>> row operations and use the PK.
>>> >
>>> > It does not look like any query taking longer but something else. How
>>> can I check where is the time being spent? There are no IO waits, so its
>>> all on the CPU.
>>>
>>> You could profile the PostgreSQL server while it is executing the
>>> workload,
>>> see for example https://wiki.postgresql.org/wiki/Profiling_with_perf
>>>
>>> That way you could see where the time is spent.
>>>
>>> PL/pgSQL is not optimized for performance like PL/SQL.
>>>
>>> Yours,
>>> Laurenz Albe
>>>
>>
>>
>