Subject: Stored Procedure Performance

From: Purav Chovatia <[email-masked]>
Date: Tue, 3 Oct 2017 20:03:16 +0530

Hello,

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.

btw, postgres and oracle both are installed on the same server, so no
differences in env.

All suggestions welcome but I am more of looking at tools or any profilers
that I can use to find out where is the time being spent because we believe
most of our applications will run into similar issues.

The version is 9.6 on RHEL 7.2.

Many thanks in advance.

Regards,
Purav

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 03 Oct 2017 16:54:46 +0200

Purav Chovatia wrote:
> I come from Oracle world and we are porting all our applications to postgresql.
> 
> The main table on which the insert and the update happens is truncated before every performance test.
> 
> 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

-- 
Sent via pgsql-performance mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

---

From: Adam Brusselback <[email-masked]>
Date: Tue, 3 Oct 2017 11:17:38 -0400

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.

---

From: Pavel Stehule <[email-masked]>
Date: Tue, 3 Oct 2017 17:28:55 +0200

2017-10-03 17:17 GMT+02:00 Adam Brusselback <[email-masked]>:

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

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

---

From: Purav Chovatia <[email-masked]>
Date: Wed, 11 Oct 2017 19:29:19 +0530

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 <[email-masked]> wrote:

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

---

From: Purav Chovatia <[email-masked]>
Date: Wed, 11 Oct 2017 19:41:03 +0530

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 <[email-masked]> wrote:

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

---

From: Pavel Stehule <[email-masked]>
Date: Wed, 11 Oct 2017 16:20:36 +0200

> 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

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

---

From: Purav Chovatia <[email-masked]>
Date: Wed, 11 Oct 2017 21:35:31 +0530

Thanks Pavel. Our SPs are not doing any mathematical calculations. Its
mostly if-else, so I would expect good performance.

---

From: Adam Brusselback <[email-masked]>
Date: Wed, 11 Oct 2017 12:37:01 -0400

Is there any error handling in there?  I remember seeing performance
issues if you put in any code to catch exceptions.

---

From: Purav Chovatia <[email-masked]>
Date: Wed, 11 Oct 2017 22:22:23 +0530

Yes, there is some code to catch exceptions like unique constraint
violation and no data found. Do you suggest we trying by commenting that
part? btw, the dataset is a controlled one, so what I can confirm is we are
not hitting any exceptions.

Thanks

---

From: Adam Brusselback <[email-masked]>
Date: Wed, 11 Oct 2017 12:54:37 -0400

> Yes, there is some code to catch exceptions like unique constraint violation and no data found. Do you suggest we trying by commenting that part?

That is likely it.  Comment that out and test.
If you still need to handle a unique violation, see if you can instead
use the ON CONFLICT clause on the INSERT.

---

From: Pavel Stehule <[email-masked]>
Date: Wed, 11 Oct 2017 21:06:42 +0200

> not hitting any exceptions.

If it is possible, don't do it in cycle, or use exception handling only
when it is necessary, not from pleasure.

Regards

Pavel

---

From: phb07 <[email-masked]>
Date: Sat, 14 Oct 2017 12:21:55 +0200

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 <[email-masked] 
> <mailto:[email-masked]>> wrote:
>
>
>
>     2017-10-03 17:17 GMT+02:00 Adam Brusselback
>     <[email-masked] <mailto:[email-masked]>>:
>
>         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>
>
>         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
>
>