Slow PL/pgSQL 8.0.RC5 (7.4.6. 3times faster)

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:

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 responsibility
for advancing ActiveSnapshot when we are inside a volatile
function. Per example from Gaetano Mendola. Add a regression test
to 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() not
just GetTransactionSnapshot() to ensure ActiveSnapshot advances
properly. Sigh. Extend regression test so it reveals this error
too.

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