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 >
