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

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(dot)stehule(at)gmail(dot)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(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
>>
>
>