Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

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(dot)mello(at)gmail(dot)com>:

> On Tue, Aug 5, 2014 at 9:50 AM, Kevin Grittner <kgrittn(at)ymail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>