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.steh...@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.me...@gmail.com>: > > On Tue, Aug 5, 2014 at 9:50 AM, Kevin Grittner <kgri...@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 >> > >
