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.steh...@gmail.com> wrote: > > > 2017-10-11 15:59 GMT+02:00 Purav Chovatia <pur...@gmail.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.a...@cybertec.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 >>> >> >> >
