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

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

From
Pavel Stehule
Date:
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



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

From
Tom Lane
Date:
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


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

From
Pavel Stehule
Date:
> 
> 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



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

From
Tom Lane
Date:
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


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

From
Michael Fuhr
Date:
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/


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

From
Pavel Stehule
Date:
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.
> 
> 



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

From
Tom Lane
Date:
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


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

From
Mark Kirkwood
Date:
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



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

From
Michael Fuhr
Date:
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/


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

From
Tom Lane
Date:
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


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

From
Pavel Stehule
Date:
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