Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e2JVr-0004Pq-IE for pgsql-performance@arkaria.postgresql.org; Wed, 11 Oct 2017 16:05:39 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e2JVq-00068O-Ei for pgsql-performance@arkaria.postgresql.org; Wed, 11 Oct 2017 16:05:38 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e2JVp-00068E-Ok for pgsql-performance@postgresql.org; Wed, 11 Oct 2017 16:05:37 +0000 Received: from mail-wm0-x232.google.com ([2a00:1450:400c:c09::232]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1e2JVm-0001aQ-7k for pgsql-performance@postgresql.org; Wed, 11 Oct 2017 16:05:36 +0000 Received: by mail-wm0-x232.google.com with SMTP id q124so6148444wmb.0 for ; Wed, 11 Oct 2017 09:05:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=uWsyQ2GNioOOpCh3CiqHttHO+hU56ZHUZuIlBF8WXJ0=; b=QxJ0IFRV4CIlkZ47zDPpcPY+XIs3oh7VhWmHL5NgegiOPfQoUocYUbP6Xplia8MGFu TG4tjLbbxNs8MMQ45eJptY2Zj3yVWdl8gA+h1Uq6RqYYBqqGNKhyWx3lrWA1ClEoXa0d BKOeOMO4zPUU9tO9c2gyXwYjtwCA5fKqhWEYHvULhRQt7JCy/7fAZE2wN2XhURnqpC9J 3uHMjFT6RUfeYiJ9FnzZxaLNDsPUgDyQLBBinHZqlPSVpIMXbvWbcJigAB+ggudPhm06 SSER+6nOHgB/hKEWUmqPtKXqp7JQo6jrzHmdFtpcaC4FilPG0JCYdFuGWpZdj+1oRX6B vTWg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=uWsyQ2GNioOOpCh3CiqHttHO+hU56ZHUZuIlBF8WXJ0=; b=l5EXonb7fGVvwawAIdR9opN9gdMsPt2GS9W/FZsN3vmQ01aYpUi5jnaejhN7qEM5fp FbQYYobwEqfDC/VQEErTPX40X2CyIaQKJDb/cAbph03Bh1mW7mRdz0QFU5Yhy+XAhcHU Yxp/GStOOjunpHLUOHg1DNIShcHscJQUccgdzbRARLCRsWTB9a+tFWH8hHCIH7IFqJzt E8XrAQjJS+tCoB08XOj9Z8fSulPBC3fWqXdnnU7tI1MNDiJicaumBN2bLXfOT99LOd5q u1+JHZWc+ERMWHWMm+YSlbkoHcHvDZLMTccDriNqsn1txQEYk8qMcXQTFYM6Fygbg26E lMXw== X-Gm-Message-State: AMCzsaW1mQodTPClfw6IxULvZCWm2tsvGjroQchvy5tYDA7WvR3fM/WK nT68eqRIA/8kXoHnUjnVvLXIieuEHimiShIbKWY= X-Google-Smtp-Source: AOwi7QBhRLzG8Kw9MIyzH16zjh9mythgB9wYe6OJRjV+c5jA1JWanOxH3slvN+nfQMcc55lx/9bzR5wB0rSg0XlyUhw= X-Received: by 10.223.157.137 with SMTP id p9mr107336wre.98.1507737932445; Wed, 11 Oct 2017 09:05:32 -0700 (PDT) MIME-Version: 1.0 Received: by 10.223.197.78 with HTTP; Wed, 11 Oct 2017 09:05:31 -0700 (PDT) In-Reply-To: References: <1507042486.2998.1.camel@cybertec.at> From: Purav Chovatia Date: Wed, 11 Oct 2017 21:35:31 +0530 Message-ID: Subject: Re: Stored Procedure Performance To: Pavel Stehule Cc: Laurenz Albe , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="f403043a2794e0bbb4055b4797b8" 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 --f403043a2794e0bbb4055b4797b8 Content-Type: text/plain; charset="UTF-8" Thanks Pavel. Our SPs are not doing any mathematical calculations. Its mostly if-else, so I would expect good performance. On 11 October 2017 at 19:50, Pavel Stehule wrote: > > > 2017-10-11 15:59 GMT+02:00 Purav Chovatia : > >> 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? >> > > 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 > > >> >> Regards >> >> On 3 October 2017 at 20:24, Laurenz Albe >> 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 >>> >> >> > --f403043a2794e0bbb4055b4797b8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks Pavel. Our SPs are not doing any mathematical calcu= lations. Its mostly if-else, so I would expect good performance.

On 11 October 2017 at = 19:50, Pavel Stehule <pavel.stehule@gmail.com> wrote:<= br>


2017-10-11 15:59 GMT+02:00 Purav Chov= atia <puravc@gmail.com>:
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 li= ke PL/SQL". Do you mean to indicate that app firing queries/DMLs direc= tly would be a better option as compared to putting those in Stored Procs?<= /span>

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 no= t designed for intensive mathematics calculation.=C2=A0 PL/SQL is self gove= ring environment ... it has own data types, it has own implementation of lo= gical 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 year= s there will be a JIT compiler. But it is not now. This is current bottlene= ck of PLpgSQL. If your PL code is glue for SQL queries (implementation of s= ome business processes), then PLpgSQL is fast enough. If you try to calcula= te numeric integration or derivation of some functions, then PLpgSQL is slo= w. It is not too slow - the speed is comparable with PHP, but it is signifi= cantly slower than C language.

PostgreSQL has perf= ect C API - so intensive numeric calculations are usually implemented as C = extension.

Regards

Pavel<= br>
=C2=A0
<= div>
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 po= stgresql.
>
> The application calls 2 stored procs,=C2=A0
> - 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 sec= ond.
>
> 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.
>
> All indices are in place. The select, insert & update are all sing= le 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 al= l 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



--f403043a2794e0bbb4055b4797b8--