pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedStored Procedure Performance
22+ messages / 13 participants
[nested] [flat]
* Stored Procedure Performance
@ 2006-04-11 07:19 Simon Dale <sdale@rm.com>
0 siblings, 4 replies; 22+ messages in thread
From: Simon Dale @ 2006-04-11 07:19 UTC (permalink / raw)
To: pgsql-performance
Hi,
I'm trying to evaluate PostgreSQL as a database that will have to store
a high volume of data and access that data frequently. One of the
features on our wish list is to be able to use stored procedures to
access the data and I was wondering if it is usual for stored procedures
to perform slower on PostgreSQL than raw SQL?
A simple example of this can be shown with the following commands:
First I created a test table:
CREATE TABLE test (
id int8,
name varchar(128),
description varchar(500),
constraint "pk_test" primary key (id)
);
Then the function I want to test:
CREATE OR REPLACE FUNCTION readTest() RETURNS SETOF test AS
$$
DECLARE
row test%ROWTYPE;
BEGIN
FOR row IN SELECT * FROM test LOOP
RETURN NEXT row;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
Firstly, I ran EXPLAIN on the raw SQL to see how long that takes to
access the database the results are as follows:
EXPLAIN ANALYZE SELECT * FROM test;
Seq Scan on test (cost=0.00..10.90 rows=90 width=798) (actual
time=0.003..0.003 rows=0 loops=1)
Total runtime: 0.074 ms
(2 rows)
Secondly, I ran EXPLAIN on the function created above and the results
are as follows:
EXPLAIN ANALYZE SELECT * FROM readTest();
Function Scan on readtest (cost=0.00..12.50 rows=1000 width=798)
(actual time=0.870..0.870 rows=0 loops=1)
Total runtime: 0.910 ms
(2 rows)
I know that the function is planned the first time it is executed so I
ran the same command again to remove that processing from the timings
and the results are as follows:
EXPLAIN ANALYZE SELECT * FROM readTest();
Function Scan on readtest (cost=0.00..12.50 rows=1000 width=798)
(actual time=0.166..0.166 rows=0 loops=1)
Total runtime: 0.217 ms
(2 rows)
Event with the planning removed, the function still performs
significantly slower than the raw SQL. Is that normal or am I doing
something wrong with the creation or calling of the function?
Thanks for your help,
Simon
Visit our Website at http://www.rm.com
This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RM does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RM. If this email has come to you in error, please delete it, along with any attachments. Please note that RM may intercept incoming and outgoing email communications.
Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging to RM. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RM and the disclosure of which would be prejudicial to RM's commercial interests.
This email has been scanned for viruses by Trend ScanMail.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2006-04-11 07:58 hubert depesz lubaczewski <depesz@gmail.com>
parent: Simon Dale <sdale@rm.com>
3 siblings, 0 replies; 22+ messages in thread
From: hubert depesz lubaczewski @ 2006-04-11 07:58 UTC (permalink / raw)
To: Simon Dale <sdale@rm.com>; +Cc: pgsql-performance
On 4/11/06, Simon Dale <sdale@rm.com> wrote:
>
> I'm trying to evaluate PostgreSQL as a database that will have to store a
> high volume of data and access that data frequently. One of the features on
> our wish list is to be able to use stored procedures to access the data and
> I was wondering if it is usual for stored procedures to perform slower on
> PostgreSQL than raw SQL?
>
worry but your benchmark is completelly flawed.
1st. the tables are empty. will you ever run the real code on empty tables?
2nd. do you really need a stored procedure for such a simple query?
testing something that's far from real usage will not give you any good.
return next will of course show up as slower than standard select. the thing
is - will the relative slowness of return next matter to you when you will
put more logic in the procedure?
depesz
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2006-04-11 08:22 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: Simon Dale <sdale@rm.com>
3 siblings, 1 reply; 22+ messages in thread
From: Rajesh Kumar Mallah @ 2006-04-11 08:22 UTC (permalink / raw)
To: Simon Dale <sdale@rm.com>; +Cc: pgsql-performance
On 4/11/06, Simon Dale <sdale@rm.com> wrote:
>
>
>
> Hi,
>
>
>
> I'm trying to evaluate PostgreSQL as a database that will have to store a
> high volume of data and access that data frequently. One of the features on
> our wish list is to be able to use stored procedures to access the data and
> I was wondering if it is usual for stored procedures to perform slower on
> PostgreSQL than raw SQL?
No.
RETURN NEXT keeps accumulating the data before returning.
I am not sure if any optimisations have been done to that effect.
In general functions are *NOT* slower than RAW SQL.
Regds
mallah.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2006-04-11 10:04 Richard Huxton <dev@archonet.com>
parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Richard Huxton @ 2006-04-11 10:04 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: Simon Dale <sdale@rm.com>; pgsql-performance
Rajesh Kumar Mallah wrote:
> On 4/11/06, Simon Dale <sdale@rm.com> wrote:
>>
>> I'm trying to evaluate PostgreSQL as a database that will have to store a
>> high volume of data and access that data frequently. One of the features on
>> our wish list is to be able to use stored procedures to access the data and
>> I was wondering if it is usual for stored procedures to perform slower on
>> PostgreSQL than raw SQL?
>
> No.
>
> RETURN NEXT keeps accumulating the data before returning.
> I am not sure if any optimisations have been done to that effect.
>
> In general functions are *NOT* slower than RAW SQL.
Actually, in cases where there is a simple way to state the query in raw
SQL then I'd expect that a procedural solution IS slower. After all,
you're adding another layer of processing.
Of course, you normally wouldn't write a procedural solution to a simple
query.
Added to this is the difference that plpgsql is planned once whereas raw
sql will be planned on each query. This means you save planning costs
with the plpgsql but have the chance to get better plans with the raw sql.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2006-04-11 13:49 Merlin Moncure <mmoncure@gmail.com>
parent: Simon Dale <sdale@rm.com>
3 siblings, 2 replies; 22+ messages in thread
From: Merlin Moncure @ 2006-04-11 13:49 UTC (permalink / raw)
To: Simon Dale <sdale@rm.com>; +Cc: pgsql-performance
On 4/11/06, Simon Dale <sdale@rm.com> wrote:
> I'm trying to evaluate PostgreSQL as a database that will have to store a
> high volume of data and access that data frequently. One of the features on
> our wish list is to be able to use stored procedures to access the data and
> I was wondering if it is usual for stored procedures to perform slower on
> PostgreSQL than raw SQL?
pl/pgsql procedures are a very thin layer over the query engine.
Generally, they run about the same speed as SQL but you are not making
apples to apples comparison. One of the few but annoying limitations
of pl/pgsql procedures is that you can't return a select directly from
the query engine but have to go through the return/return next
paradigm which will be slower than raw query for obvious reasons.
You can however return a refcursor and you may want to look at them in
situations where you want to return arbitrary sets outside the query
engine or between pl/pgsql functions. An example of using refcurors
in that way is on my blog at
http://people.planetpostgresql.org/merlin/index.php?/archives/2-Dealing-With-Recursive-Sets-With-PLP...
Generally, in my opinion if you want to really unlock the power of
postgresql you have to master pl/pgsql. Go for it...it will work and
work well.
merlin
merlin
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2006-04-11 13:56 Christopher Browne <cbbrowne@acm.org>
parent: Simon Dale <sdale@rm.com>
3 siblings, 0 replies; 22+ messages in thread
From: Christopher Browne @ 2006-04-11 13:56 UTC (permalink / raw)
To: pgsql-performance
sdale@rm.com ("Simon Dale") wrote:
> <p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
> font-family:Arial'>Event with the planning removed, the function still performs
> significantly slower than the raw SQL. Is that normal or am I doing something wrong
> with the creation or calling of the
> function?<o:p></o:p></span></font></p>
I'd expect this, yes.
You're doing something via "stored procedure logic" that would be done
more directly via straight SQL; of course it won't be faster.
In effect, pl/pgsql involves (planning once) then running each line of
logic. In effect, you replaced one query (select * from some table)
into 90 queries. Yup, there's extra cost there.
There's not some "magic" by which stored procedures provide results
faster as a natural "matter of course;" the performance benefits
generally fall out of two improvements:
1. You eliminate client-to-server round trips.
A stored proc that runs 8 queries saves you 8 round trips over
submitting the 8 queries directly. Saving you latency time.
2. You can eliminate the marshalling and transmission of unnecessary
data.
A stored proc that runs 8 queries, and only returns summarized
results that all come from the last table queried will eliminate
the need to marshall and transmit (possibly over a slow link) the
data for the 7 preceding queries.
The case that you tried can benefit from neither of those effects;
your stored procedure eliminates NO round trips, and NO
marshalling/transmission.
--
(format nil "~S@~S" "cbbrowne" "gmail.com")
http://linuxdatabases.info/info/rdbms.html
Rules of the Evil Overlord #228. "If the hero claims he wishes to
confess in public or to me personally, I will remind him that a
notarized deposition will serve just as well."
<http://www.eviloverlord.com/;
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2006-04-11 14:02 H.J. Sanders <hjs@rmax.nl>
parent: Merlin Moncure <mmoncure@gmail.com>
1 sibling, 0 replies; 22+ messages in thread
From: H.J. Sanders @ 2006-04-11 14:02 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; Simon Dale <sdale@rm.com>; +Cc: pgsql-performance
Hello
At my little machine (pentium 4, 2.8 Ghz, 256 Mb RAM, Suse linux 9)
I can process about 100000 records a minute using the next setup:
begin work
begin for
processing
if 10.000 records processed:
commit work
begin work
end if
end for
commit work (!)
Regards
Henk Sanders
> -----Oorspronkelijk bericht-----
> Van: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]Namens Merlin Moncure
> Verzonden: dinsdag 11 april 2006 15:50
> Aan: Simon Dale
> CC: pgsql-performance@postgresql.org
> Onderwerp: Re: [PERFORM] Stored Procedure Performance
>
>
> On 4/11/06, Simon Dale <sdale@rm.com> wrote:
> > I'm trying to evaluate PostgreSQL as a database that will have to store a
> > high volume of data and access that data frequently. One of the features on
> > our wish list is to be able to use stored procedures to access the data and
> > I was wondering if it is usual for stored procedures to perform slower on
> > PostgreSQL than raw SQL?
>
> pl/pgsql procedures are a very thin layer over the query engine.
> Generally, they run about the same speed as SQL but you are not making
> apples to apples comparison. One of the few but annoying limitations
> of pl/pgsql procedures is that you can't return a select directly from
> the query engine but have to go through the return/return next
> paradigm which will be slower than raw query for obvious reasons.
>
> You can however return a refcursor and you may want to look at them in
> situations where you want to return arbitrary sets outside the query
> engine or between pl/pgsql functions. An example of using refcurors
> in that way is on my blog at
> http://people.planetpostgresql.org/merlin/index.php?/archives/2-Dealing-With-Recursive-Sets-With-PLP...
>
> Generally, in my opinion if you want to really unlock the power of
> postgresql you have to master pl/pgsql. Go for it...it will work and
> work well.
>
> merlin
>
> merlin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2006-04-11 14:08 Alvaro Herrera <alvherre@commandprompt.com>
parent: Merlin Moncure <mmoncure@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Alvaro Herrera @ 2006-04-11 14:08 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Simon Dale <sdale@rm.com>; pgsql-performance
Merlin Moncure wrote:
> On 4/11/06, Simon Dale <sdale@rm.com> wrote:
> > I'm trying to evaluate PostgreSQL as a database that will have to store a
> > high volume of data and access that data frequently. One of the features on
> > our wish list is to be able to use stored procedures to access the data and
> > I was wondering if it is usual for stored procedures to perform slower on
> > PostgreSQL than raw SQL?
>
> pl/pgsql procedures are a very thin layer over the query engine.
> Generally, they run about the same speed as SQL but you are not making
> apples to apples comparison. One of the few but annoying limitations
> of pl/pgsql procedures is that you can't return a select directly from
> the query engine but have to go through the return/return next
> paradigm which will be slower than raw query for obvious reasons.
There's one problem that hasn't been mentioned. For the optimizer a
PL/pgSQL function (really, a function in any language except SQL) is a
black box. If you have a complex join of two or three functions, and
they don't return 1000 rows, it's very likely that the optimizer is
going to get it wrong.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2006-04-11 21:02 Merlin Moncure <mmoncure@gmail.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 22+ messages in thread
From: Merlin Moncure @ 2006-04-11 21:02 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; Simon Dale <sdale@rm.com>; pgsql-performance
On 4/11/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Merlin Moncure wrote:
> > pl/pgsql procedures are a very thin layer over the query engine.
> > Generally, they run about the same speed as SQL but you are not making
> > apples to apples comparison. One of the few but annoying limitations
> > of pl/pgsql procedures is that you can't return a select directly from
> > the query engine but have to go through the return/return next
> > paradigm which will be slower than raw query for obvious reasons.
>
> There's one problem that hasn't been mentioned. For the optimizer a
> PL/pgSQL function (really, a function in any language except SQL) is a
> black box. If you have a complex join of two or three functions, and
> they don't return 1000 rows, it's very likely that the optimizer is
> going to get it wrong.
This doesn't bother me that much. Those cases usually have a high
overlap with views.You just have to plan on the function being fully
materialized before it is inovled further. What drives me crazy is I
have to do 'select * from plpgsql_srf()' but I am allowed to do the
much friendlier and more versatile 'select sql_srf()', even if they do
more or less the same thing.
On the flip side, what drives me crazy about sql functions is that all
tables have to be in the search path for the validator. Since I
frequently use the trick of having multiple schemas with one set of
functions this is annoying.
Merlin
^ permalink raw reply [nested|flat] 22+ messages in thread
* Stored Procedure Performance
@ 2017-10-03 14:33 Purav Chovatia <puravc@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Purav Chovatia @ 2017-10-03 14:33 UTC (permalink / raw)
To: pgsql-performance
Hello,
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.
btw, postgres and oracle both are installed on the same server, so no
differences in env.
All suggestions welcome but I am more of looking at tools or any profilers
that I can use to find out where is the time being spent because we believe
most of our applications will run into similar issues.
The version is 9.6 on RHEL 7.2.
Many thanks in advance.
Regards,
Purav
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-03 14:54 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Purav Chovatia <puravc@gmail.com>
0 siblings, 2 replies; 22+ messages in thread
From: Laurenz Albe @ 2017-10-03 14:54 UTC (permalink / raw)
To: Purav Chovatia <puravc@gmail.com>; pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-03 15:17 Adam Brusselback <adambrusselback@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 22+ messages in thread
From: Adam Brusselback @ 2017-10-03 15:17 UTC (permalink / raw)
To: Purav Chovatia <puravc@gmail.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-03 15:28 Pavel Stehule <pavel.stehule@gmail.com>
parent: Adam Brusselback <adambrusselback@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Pavel Stehule @ 2017-10-03 15:28 UTC (permalink / raw)
To: Adam Brusselback <adambrusselback@gmail.com>; +Cc: Purav Chovatia <puravc@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-11 13:59 Purav Chovatia <puravc@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 22+ messages in thread
From: Purav Chovatia @ 2017-10-11 13:59 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-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 <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
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-11 14:11 Purav Chovatia <puravc@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Purav Chovatia @ 2017-10-11 14:11 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Adam Brusselback <adambrusselback@gmail.com>; pgsql-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 <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://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
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-11 14:20 Pavel Stehule <pavel.stehule@gmail.com>
parent: Purav Chovatia <puravc@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Pavel Stehule @ 2017-10-11 14:20 UTC (permalink / raw)
To: Purav Chovatia <puravc@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance
2017-10-11 15:59 GMT+02:00 Purav Chovatia <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 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 <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
>>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-11 16:05 Purav Chovatia <puravc@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Purav Chovatia @ 2017-10-11 16:05 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance
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 <pavel.stehule@gmail.com> wrote:
>
>
> 2017-10-11 15:59 GMT+02:00 Purav Chovatia <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 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 <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
>>>
>>
>>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-11 16:37 Adam Brusselback <adambrusselback@gmail.com>
parent: Purav Chovatia <puravc@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Adam Brusselback @ 2017-10-11 16:37 UTC (permalink / raw)
To: Purav Chovatia <puravc@gmail.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance
Is there any error handling in there? I remember seeing performance
issues if you put in any code to catch exceptions.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-11 16:52 Purav Chovatia <puravc@gmail.com>
parent: Adam Brusselback <adambrusselback@gmail.com>
0 siblings, 2 replies; 22+ messages in thread
From: Purav Chovatia @ 2017-10-11 16:52 UTC (permalink / raw)
To: Adam Brusselback <adambrusselback@gmail.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance
Yes, there is some code to catch exceptions like unique constraint
violation and no data found. Do you suggest we trying by commenting that
part? btw, the dataset is a controlled one, so what I can confirm is we are
not hitting any exceptions.
Thanks
On 11 October 2017 at 22:07, Adam Brusselback <adambrusselback@gmail.com>
wrote:
> Is there any error handling in there? I remember seeing performance
> issues if you put in any code to catch exceptions.
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-11 16:54 Adam Brusselback <adambrusselback@gmail.com>
parent: Purav Chovatia <puravc@gmail.com>
1 sibling, 0 replies; 22+ messages in thread
From: Adam Brusselback @ 2017-10-11 16:54 UTC (permalink / raw)
To: Purav Chovatia <puravc@gmail.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance
> Yes, there is some code to catch exceptions like unique constraint violation and no data found. Do you suggest we trying by commenting that part?
That is likely it. Comment that out and test.
If you still need to handle a unique violation, see if you can instead
use the ON CONFLICT clause on the INSERT.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-11 19:06 Pavel Stehule <pavel.stehule@gmail.com>
parent: Purav Chovatia <puravc@gmail.com>
1 sibling, 0 replies; 22+ messages in thread
From: Pavel Stehule @ 2017-10-11 19:06 UTC (permalink / raw)
To: Purav Chovatia <puravc@gmail.com>; +Cc: Adam Brusselback <adambrusselback@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance
2017-10-11 18:52 GMT+02:00 Purav Chovatia <puravc@gmail.com>:
> Yes, there is some code to catch exceptions like unique constraint
> violation and no data found. Do you suggest we trying by commenting that
> part? btw, the dataset is a controlled one, so what I can confirm is we are
> not hitting any exceptions.
>
If it is possible, don't do it in cycle, or use exception handling only
when it is necessary, not from pleasure.
Regards
Pavel
> Thanks
>
> On 11 October 2017 at 22:07, Adam Brusselback <adambrusselback@gmail.com>
> wrote:
>
>> Is there any error handling in there? I remember seeing performance
>> issues if you put in any code to catch exceptions.
>>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Stored Procedure Performance
@ 2017-10-14 10:21 phb07 <phb07@apra.asso.fr>
parent: Purav Chovatia <puravc@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: phb07 @ 2017-10-14 10:21 UTC (permalink / raw)
To: Purav Chovatia <puravc@gmail.com>; Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Adam Brusselback <adambrusselback@gmail.com>; pgsql-performance
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
> <mailto:pavel.stehule@gmail.com>> wrote:
>
>
>
> 2017-10-03 17:17 GMT+02:00 Adam Brusselback
> <adambrusselback@gmail.com <mailto:adambrusselback@gmail.com>>:
>
> There is also the option of pg_stat_statements:
> https://www.postgresql.org/docs/current/static/pgstatstatements.html
> <https://www.postgresql.org/docs/current/static/pgstatstatements.html;
> and auto_explain:
> https://www.postgresql.org/docs/current/static/auto-explain.html
> <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
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2017-10-14 10:21 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-11 07:19 Stored Procedure Performance Simon Dale <sdale@rm.com>
2006-04-11 07:58 ` hubert depesz lubaczewski <depesz@gmail.com>
2006-04-11 08:22 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-04-11 10:04 ` Richard Huxton <dev@archonet.com>
2006-04-11 13:49 ` Merlin Moncure <mmoncure@gmail.com>
2006-04-11 14:02 ` H.J. Sanders <hjs@rmax.nl>
2006-04-11 14:08 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-04-11 21:02 ` Merlin Moncure <mmoncure@gmail.com>
2006-04-11 13:56 ` Christopher Browne <cbbrowne@acm.org>
2017-10-03 14:33 Stored Procedure Performance Purav Chovatia <puravc@gmail.com>
2017-10-03 14:54 ` Laurenz Albe <laurenz.albe@cybertec.at>
2017-10-03 15:17 ` Adam Brusselback <adambrusselback@gmail.com>
2017-10-03 15:28 ` Pavel Stehule <pavel.stehule@gmail.com>
2017-10-11 14:11 ` Purav Chovatia <puravc@gmail.com>
2017-10-14 10:21 ` phb07 <phb07@apra.asso.fr>
2017-10-11 13:59 ` Purav Chovatia <puravc@gmail.com>
2017-10-11 14:20 ` Pavel Stehule <pavel.stehule@gmail.com>
2017-10-11 16:05 ` Purav Chovatia <puravc@gmail.com>
2017-10-11 16:37 ` Adam Brusselback <adambrusselback@gmail.com>
2017-10-11 16:52 ` Purav Chovatia <puravc@gmail.com>
2017-10-11 16:54 ` Adam Brusselback <adambrusselback@gmail.com>
2017-10-11 19:06 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox