public inbox for [email protected]  

help / color / mirror / Atom feed

From: Purav Chovatia <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: [email protected]
Subject: Re: Stored Procedure Performance
Date: Wed, 11 Oct 2017 19:29:19 +0530
Message-ID: <CADrzpjG3SDW1OxAFrYVNq63M6WA_y=DL27DenZ=tHmr=6L6Y0w@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CADrzpjHChC6q0NGMG0wZUFvj-A0QO3WL9JONLcmRRCZuPnQQNQ@mail.gmail.com>
	<[email protected]>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

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

view thread (13+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Stored Procedure Performance
  In-Reply-To: <CADrzpjG3SDW1OxAFrYVNq63M6WA_y=DL27DenZ=tHmr=6L6Y0w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox