public inbox for [email protected]  

help / color / mirror / Atom feed

From: Purav Chovatia <[email protected]>
To: Pavel Stehule <[email protected]>
Cc: Adam Brusselback <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Stored Procedure Performance
Date: Wed, 11 Oct 2017 19:41:03 +0530
Message-ID: <CADrzpjG1DvdBcJ+qRcSKoEwnSZ6z9oPSX-YdTX4n2MU-fbvYoQ@mail.gmail.com> (raw)
In-Reply-To: <CAFj8pRB32vGfzOuQy8ZC03Jvgx8hKL4_P7Zv4oO_w0dmUKbP-w@mail.gmail.com>
References: <CADrzpjHChC6q0NGMG0wZUFvj-A0QO3WL9JONLcmRRCZuPnQQNQ@mail.gmail.com>
	<[email protected]>
	<CAMjNa7dQYE44gisjODEWECs82oAjj5Q7pDDhFKmP_aYX3dZYow@mail.gmail.com>
	<CAFj8pRB32vGfzOuQy8ZC03Jvgx8hKL4_P7Zv4oO_w0dmUKbP-w@mail.gmail.com>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-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 <[email protected]> wrote:

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

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], [email protected]
  Subject: Re: Stored Procedure Performance
  In-Reply-To: <CADrzpjG1DvdBcJ+qRcSKoEwnSZ6z9oPSX-YdTX4n2MU-fbvYoQ@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