Re: 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(dot)stehule(at)gmail(dot)com> wrote:

>
>
> 2017-10-03 17:17 GMT+02:00 Adam Brusselback <adambrusselback(at)gmail(dot)com>:
>
>> There is also the option of pg_stat_statements: https://ww
>> w.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
>
>