Re: Stored Procedure Performance

Le 11/10/2017 à 16:11, Purav Chovatia a écrit :
> 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.
You could have a look at the PoWA extension
(http://dalibo.github.io/powa/) It has the same purpose as AWR.

>
> Thanks & Regards
>
> On 3 October 2017 at 20:58, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com
> <mailto:pavel(dot)stehule(at)gmail(dot)com>> wrote:
>
>
>
> 2017-10-03 17:17 GMT+02:00 Adam Brusselback
> <adambrusselback(at)gmail(dot)com <mailto:adambrusselback(at)gmail(dot)com>>:
>
> There is also the option of pg_stat_statements:
> https://www.postgresql.org/docs/current/static/pgstatstatements.html
> <https://www.postgresql.org/docs/current/static/pgstatstatements.html>
> and auto_explain:
> https://www.postgresql.org/docs/current/static/auto-explain.html
> <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
>
>