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
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
