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?
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 >
