MIME-Version: 1.0
In-Reply-To: <1507042486.2998.1.camel@cybertec.at>
References: 
 <CADrzpjHChC6q0NGMG0wZUFvj-A0QO3WL9JONLcmRRCZuPnQQNQ@mail.gmail.com>
 <1507042486.2998.1.camel@cybertec.at>
From: Purav Chovatia <puravc@gmail.com>
Date: Wed, 11 Oct 2017 19:29:19 +0530
Message-ID: 
 <CADrzpjG3SDW1OxAFrYVNq63M6WA_y=DL27DenZ=tHmr=6L6Y0w@mail.gmail.com>
Subject: Re: Stored Procedure Performance
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary="94eb2c13130e8cc3d4055b45d453"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--94eb2c13130e8cc3d4055b45d453
Content-Type: text/plain; charset="UTF-8"

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

--94eb2c13130e8cc3d4055b45d453
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Thanks Laurenz, am having a look at perf.<div><br></div><d=
iv>Can you pls help understand what exactly do you mean when you say &quot;=
<span style=3D"font-size:12.8px">PL/pgSQL is not optimized for performance =
like PL/SQL&quot;. Do you mean to indicate that app firing queries/DMLs dir=
ectly would be a better option as compared to putting those in Stored Procs=
?</span></div><div><span style=3D"font-size:12.8px"><br></span></div><div><=
span style=3D"font-size:12.8px">Regards</span></div></div><div class=3D"gma=
il_extra"><br><div class=3D"gmail_quote">On 3 October 2017 at 20:24, Lauren=
z Albe <span dir=3D"ltr">&lt;<a href=3D"mailto:laurenz.albe@cybertec.at" ta=
rget=3D"_blank">laurenz.albe@cybertec.at</a>&gt;</span> wrote:<br><blockquo=
te class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc so=
lid;padding-left:1ex">Purav Chovatia wrote:<br>
&gt; I come from Oracle world and we are porting all our applications to po=
stgresql.<br>
&gt;<br>
&gt; The application calls 2 stored procs,=C2=A0<br>
&gt; - first one does a few selects and then an insert<br>
&gt; - second one does an update<br>
&gt;<br>
&gt; The main table on which the insert and the update happens is truncated=
 before every performance test.<br>
&gt;<br>
&gt; We are doing about 100 executions of both of these stored proc per sec=
ond.<br>
&gt;<br>
&gt; In Oracle each exec takes about 1millisec whereas in postgres its taki=
ng 10millisec and that eventually leads to a queue build up in our applicat=
ion.<br>
&gt;<br>
&gt; All indices are in place. The select, insert &amp; update are all sing=
le row operations and use the PK.<br>
&gt;<br>
&gt; 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 al=
l on the CPU.<br>
<br>
You could profile the PostgreSQL server while it is executing the<br>
workload,<br>
see for example <a href=3D"https://wiki.postgresql.org/wiki/Profiling_with_=
perf" rel=3D"noreferrer" target=3D"_blank">https://wiki.postgresql.org/<wbr=
>wiki/Profiling_with_perf</a><br>
<br>
That way you could see where the time is spent.<br>
<br>
PL/pgSQL is not optimized for performance like PL/SQL.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div><br></div>

--94eb2c13130e8cc3d4055b45d453--