Re: [PERFORM] Stored Procedure Performance

Thanks.

We looked at pg_stat_statements and we see execution count & total time taken. But that still does not help me to identify why is it slow or what is taking time or where is the wait. 

btw, does pg_stat_statements add considerable overhead? Coming from the Oracle world, we are very used to such execution stats, and hence we are planning to add this extension as a default to all our production deployments.

Its a single row select using PK, single row update using PK and a single row insert, so I dont see anything wrong with the code. So auto_explain would not add any value, I believe.

Basically, on an Oracle server, I would minimally look at statspack/awr report & OS stats (like cpu, iostat & memory) to start with. What should I look for in case of a Postgres server.

Thanks & Regards

On 3 October 2017 at 20:58, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2017-10-03 17:17 GMT+02:00 Adam Brusselback <adambrusselback@gmail.com>:

There is also the option of pg_stat_statements: https://www.postgresql.org/docs/current/static/pgstatstatements.html and auto_explain: https://www.postgresql.org/docs/current/static/auto-explain.html

These should help you identify what is slowing things down.  There is no reason I could think of you should be seeing a 10x slowdown between Postgres and Oracle, so you'll likely have to just profile it to find out.

depends what is inside.

The max 10x slow down is possible if you are hit some unoptimized cases. The times about 1ms - 10ms shows so procedure (code) can be very sensitive to some impacts.

Regards

Pavel