Thread: Slow PL/pgSQL 8.0.RC5 (7.4.6. 3times faster)
Hello, I have small piece of code for testing speed of stored procedures. First time used its for compare mysql5 and postgresql. One year ago (feb.2004) was PostgreSQL much faster than mysql5. Now I was surprised. Mysql was faster. Mysql's developers did some work, but plpgsql is slowly than before :-(. I used Linux, 2xP160, 256MB, with zero loading PostgreSQL 7.4.6 needed 8896 ms PostgreSQL 8.0.0. rc5 needed 24009 ms I know so used SP is not clasic, only arithmetic operations, no sql code, but maybe it can signalize some problems.. CREATE OR REPLACE FUNCTION delitel(int, int) RETURNS int AS ' DECLARE a integer; b integer; BEGIN a := $1; b := $2; WHILE a <> b LOOP IF a > b THEN a := a - b; ELSE b := b - a; END IF; END LOOP; RETURN a; END; ' LANGUAGE plpgsql; Regards Pavel Stehule
Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
> CREATE OR REPLACE FUNCTION delitel(int, int) RETURNS int AS '
> DECLARE a integer; b integer;
> BEGIN a := $1; b := $2;
> WHILE a <> b LOOP
> IF a > b THEN a := a - b; ELSE b := b - a; END IF;
> END LOOP;
> RETURN a;
> END; ' LANGUAGE plpgsql;
And?
(ie, what test case are you talking about?)
regards, tom lane
> > And? > > (ie, what test case are you talking about?) > > regards, tom lane This test is function for searching max factor. It is speaking only about quality of interpret an language. I would ask why? If I need solve fast this task I can write C function. What is reason and what can be impacts on speed for my aplication. Iam not sure if I understand well. Pavel Stehule
Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
>> And?
>>
>> (ie, what test case are you talking about?)
> This test is function for searching max factor. It is speaking only about
> quality of interpret an language. I would ask why?
So I can replicate your test.
regards, tom lane
On Thu, Jan 13, 2005 at 05:05:00PM -0500, Tom Lane wrote: > Pavel Stehule <stehule@kix.fsv.cvut.cz> writes: > >> And? > >> > >> (ie, what test case are you talking about?) > > > This test is function for searching max factor. It is speaking only about > > quality of interpret an language. I would ask why? > > So I can replicate your test. SELECT delitel(1000000, 1); Mean times over the last five of six runs on my poor 500MHz FreeBSD 4.11-PRERELEASE box: 6741 ms 7.4.6 (from FreeBSD ports collection) 14427 ms 8.0.0rc5 (from CVS source) I remembered that I had build 8.0.0rc5 with --enable-debug so I rebuilt it without that option, not sure if that would make a difference. The mean time increased by 8% to 15580 ms, which was opposite from what I expected. I re-ran the 7.4.6 tests and they came out the same as they had before. I'm not sure what optimization flags (if any) the ports build of 7.4.6 might have used. I can take a closer look if you think it matters. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
I don't thing so differention on speed depends on compilation or other options. I compile 8.0 and 7.4.6 from source today. I didn't use any option for configure. But the difference is too big for optimalizations. [root@stehule root]# uname -a Linux stehule.fsv.cvut.cz 2.6.4 #1 SMP Mon Mar 15 17:21:52 CET 2004 i586 i586 i386 GNU/Linux [root@stehule root]# gcc --version gcc (GCC) 3.3 20030715 (Red Hat Linux 3.3-14) I know PostgreSQL is little slowly when started new block BEGIN END now when I use catch exceptions. But in this test isn't any other subblock. Only one cycle and some basic arithmetic operations. On Thu, 13 Jan 2005, Michael Fuhr wrote: > On Thu, Jan 13, 2005 at 05:05:00PM -0500, Tom Lane wrote: > > Pavel Stehule <stehule@kix.fsv.cvut.cz> writes: > > >> And? > > >> > > >> (ie, what test case are you talking about?) > > > > > This test is function for searching max factor. It is speaking only about > > > quality of interpret an language. I would ask why? > > > > So I can replicate your test. > > SELECT delitel(1000000, 1); > > Mean times over the last five of six runs on my poor 500MHz FreeBSD > 4.11-PRERELEASE box: > > 6741 ms 7.4.6 (from FreeBSD ports collection) > 14427 ms 8.0.0rc5 (from CVS source) > > I remembered that I had build 8.0.0rc5 with --enable-debug so I > rebuilt it without that option, not sure if that would make a > difference. The mean time increased by 8% to 15580 ms, which was > opposite from what I expected. I re-ran the 7.4.6 tests and they > came out the same as they had before. > > I'm not sure what optimization flags (if any) the ports build of > 7.4.6 might have used. I can take a closer look if you think it > matters. > >
Michael Fuhr <mike@fuhr.org> writes:
> SELECT delitel(1000000, 1);
> Mean times over the last five of six runs on my poor 500MHz FreeBSD
> 4.11-PRERELEASE box:
> 6741 ms 7.4.6 (from FreeBSD ports collection)
> 14427 ms 8.0.0rc5 (from CVS source)
When I add IMMUTABLE to the function definition, I get much closer
times.
regards, tom lane
Michael Fuhr wrote: > On Thu, Jan 13, 2005 at 05:05:00PM -0500, Tom Lane wrote: > > SELECT delitel(1000000, 1); > > Mean times over the last five of six runs on my poor 500MHz FreeBSD > 4.11-PRERELEASE box: > > 6741 ms 7.4.6 (from FreeBSD ports collection) > 14427 ms 8.0.0rc5 (from CVS source) > > > Looks like something introduced between RC1 and RC5: 7455 ms 8.0.0RC5 3978 ms 8.0.0RC1 3951 ms 7.4.6 Box is FreeBSD 5.3, all 3 Pg versions built using ./configure --prefix=<prefix> --with-openssl
On Thu, Jan 13, 2005 at 06:25:10PM -0500, Tom Lane wrote: > Michael Fuhr <mike@fuhr.org> writes: > > > 6741 ms 7.4.6 (from FreeBSD ports collection) > > 14427 ms 8.0.0rc5 (from CVS source) > > When I add IMMUTABLE to the function definition, I get much closer > times. Indeed -- adding IMMUTABLE brought me down to 6859 ms. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Mark Kirkwood <markir@coretech.co.nz> writes:
> Looks like something introduced between RC1 and RC5:
You mean here
2004-12-19 15:20 tgl
* src/: pl/plpgsql/src/pl_exec.c,test/regress/expected/plpgsql.out, test/regress/sql/plpgsql.sql:plpgsql's
exec_eval_simple_expr()now has to take responsibilityfor advancing ActiveSnapshot when we are inside a
volatilefunction. Per example from Gaetano Mendola. Add a regression testto catch similar problems in future.
and here
2004-12-21 13:33 tgl
* src/: pl/plpgsql/src/pl_exec.c,test/regress/expected/plpgsql.out,
test/regress/sql/plpgsql.sql:exec_eval_simple_expr()needs to do CommandCounterIncrement() notjust
GetTransactionSnapshot()to ensure ActiveSnapshot advancesproperly. Sigh. Extend regression test so it reveals this
errortoo.
regards, tom lane
Hello, with IMMUTABLE or STABLE function is only 7% slowly. It can be usefull add into documentation so default flag is immutable, but if its not necessary its recommended IMMUTABLE or STABLE flag. Regards Pavel Stehule