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

Roberto Mello <roberto(dot)mello(at)gmail(dot)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