Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong? - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?
Date
Msg-id CAFj8pRB20C72grQCJzz1w5+65bD+8pN-kM+qsJOHOB_XeHFB5g@mail.gmail.com
Whole thread Raw
In response to Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-hackers
Hi

this code is +/- equal to Oracle (it should be eliminate a useless code)

postgres=# DO LANGUAGE plpgsql $$ DECLARE n real;
DECLARE f integer;
BEGIN
FOR f IN 1..10000000 LOOP
--if 0=0 then
n = SQRT (f)::real;
--end if;
END LOOP;
RAISE NOTICE 'Result => %',n;
END $$;
NOTICE:  Result => 3162.28
DO
Time: 5787.797 ms



2014-08-06 21:41 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:
Hi

I returned to this issue and maybe I found a root issue. It is PL/pgSQL implicit IO cast

Original text:

postgres=# DO LANGUAGE plpgsql $$ DECLARE n real;

DECLARE f integer;
BEGIN
FOR f IN 1..10000000 LOOP
if 0=0 then
n = SQRT (f);
end if;
END LOOP;
RAISE NOTICE 'Result => %',n;
END $$;
NOTICE:  Result => 3162.28
DO
Time: 31988.720 ms

Little bit modified

postgres=# DO LANGUAGE plpgsql $$ DECLARE n real;

DECLARE f integer;
BEGIN
FOR f IN 1..10000000 LOOP
if 0=0 then
n = SQRT (f)::real;
end if;

END LOOP;
RAISE NOTICE 'Result => %',n;
END $$;
NOTICE:  Result => 3162.28
DO
Time: 9660.592 ms

It is 3x faster

there is invisible IO conversion from double precision::real via libc vfprintf

https://github.com/okbob/plpgsql_check/ can raise a performance warning in this situation, but we cannot do too much now without possible breaking compatibility

Regards

Pavel


2014-08-05 16:02 GMT+02:00 Roberto Mello <roberto.mello@gmail.com>:

On Tue, Aug 5, 2014 at 9:50 AM, Kevin Grittner <kgrittn@ymail.com> wrote:
>
> Since that is outside the loop, the difference should be nominal;

Apologies. I misread on my phone and though it was within the loop.

> and in a quick test it was.  On the other hand, reducing the
> procedural code made a big difference.

<snip>

> test=# DO LANGUAGE plpgsql $$ DECLARE n real;
> BEGIN
>   PERFORM SQRT(f) FROM generate_series(1, 10000000) x(f);
> END $$;
> DO
> Time: 3916.815 ms

That is a big difference. Are you porting a lot of code from PL/SQL,
and therefore evaluating the performance difference of running this
code? Or is this just a general test where you wish to assess the
performance difference?

PL/pgSQL could definitely use some loving, as far as optimization
goes, but my feeling is that it hasn't happened because there are
other suitable backends that give the necessary flexibility for the
different use cases.

Roberto


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


pgsql-hackers by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?
Next
From: Nicolas Barbier
Date:
Subject: Re: Minmax indexes