Roberto Mello <roberto.mello@gmail.com> wrote:
> In addition to the other suggestions that have been posted (using
> a procedural language more suitable to mathematical ops, etc) I
> noticed that you are using a RAISE in the PostgreSQL version that
> you are not in Oracle.
>
> I am curious as to what the difference is if you use the RAISE in
> both or neither cases.
Since that is outside the loop, the difference should be nominal;
and in a quick test it was. On the other hand, reducing the
procedural code made a big difference.
test=# \timing on
Timing is on.
test=# DO LANGUAGE plpgsql $$ DECLARE n real;
DECLARE f integer;
BEGIN
FOR f IN 1..10000000 LOOP
n = SQRT (f);
END LOOP;
RAISE NOTICE 'Result => %',n;
END $$;
NOTICE: Result => 3162.28
DO
Time: 23687.914 ms
test=# DO LANGUAGE plpgsql $$ DECLARE n real;
BEGIN
PERFORM SQRT(f) FROM generate_series(1, 10000000) x(f);
END $$;
DO
Time: 3916.815 ms
Eliminating the plpgsql function entirely shaved a little more off:
test=# SELECT FROM generate_series(1, 10000000) x(f);
Time: 3762.886 ms
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company