Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e3Jbs-0006dJ-LM for pgsql-performance@arkaria.postgresql.org; Sat, 14 Oct 2017 10:24:00 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e3Jbr-0004Cj-3n for pgsql-performance@arkaria.postgresql.org; Sat, 14 Oct 2017 10:23:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e3Ja4-00010e-9q for pgsql-performance@postgresql.org; Sat, 14 Oct 2017 10:22:08 +0000 Received: from smtp5-g21.free.fr ([2a01:e0c:1:1599::14]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e3JZv-0008AL-07 for pgsql-performance@postgresql.org; Sat, 14 Oct 2017 10:22:07 +0000 Received: from [192.168.1.23] (unknown [82.233.212.24]) by smtp5-g21.free.fr (Postfix) with ESMTP id CB8755FF7A; Sat, 14 Oct 2017 12:21:55 +0200 (CEST) Subject: Re: Stored Procedure Performance To: Purav Chovatia , Pavel Stehule References: <1507042486.2998.1.camel@cybertec.at> Cc: Adam Brusselback , "pgsql-performance@postgresql.org" From: phb07 Message-ID: <66c6dc93-617b-b5cd-b940-39ca7806b36e@apra.asso.fr> Date: Sat, 14 Oct 2017 12:21:55 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------8F884BD1440F592F4604503E" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------8F884BD1440F592F4604503E Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 > wrote: > > > > 2017-10-03 17:17 GMT+02:00 Adam Brusselback > >: > > 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. > > > 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 > > --------------8F884BD1440F592F4604503E Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
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.stehule@gmail.com> wrote:


2017-10-03 17:17 GMT+02:00 Adam Brusselback <adambrusselback@gmail.com>:
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.

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



--------------8F884BD1440F592F4604503E--