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

On 05/08/14 08:48, testman1316 wrote:
> We am trying to get an idea of the raw performance of Oracle vs PostgreSQL.
> We have extensive oracle experience but are new to PostgreSQL. We are going
> to run lots of queries with our data, etc. But first we wanted to see just
> how they perform on basic kernel tasks, i.e. math and branching since SQL is
> built on that.
>
> In AWS RDS we created two db.m3.2xlarge instances one with oracle
> 11.2.0.4.v1 license included, the other with PostgreSQL (9.3.3)
>
> In both we ran code that did 1 million square roots (from 1 to 1 mill). Then
> did the same but within an If..Then statement.
>
> The results were a bit troubling:
>
> Oracle 4.8 seconds
>
> PostgreSQL 21.803 seconds
>
> adding an if statement:
>
> Oracle 4.78 seconds
>
> PostgreSQL 24.4 seconds
>
> code Oracle square root
>
> SET SERVEROUTPUT ON
> SET TIMING ON
>
> DECLARE
> n NUMBER := 0;
> BEGIN
> FOR f IN 1..10000000
> LOOP
> n := SQRT (f);
> END LOOP;
> END;
>
> PostgreSQL
>
> 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 $$;
>
> oracle adding if
>
> SET SERVEROUTPUT ON
> SET TIMING ON
>
> DECLARE
> n NUMBER := 0;
> BEGIN
> FOR f IN 1..10000000
> LOOP
> if 0 =0 then
> n := SQRT (f);
> end if;
> END LOOP;
>
> postgres adding if
>
> 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 $$;
>
> I used an anonymous block for PostgreSQL. I also did it as a function and
> got identical results
>
> CREATE OR REPLACE FUNCTION testpostgrescpu()
> RETURNS real AS
> $BODY$
> declare
> n real;
> f integer;
>
> BEGIN
> FOR f IN 1..10000000 LOOP
> n = SQRT (f);
> END LOOP;
>
>
> RETURN n;
> END;
> $BODY$
> LANGUAGE plpgsql VOLATILE
> COST 100;
> ALTER FUNCTION testpostgrescpu()
> OWNER TO xxx
>
> Based on what we had heard of PostgreSQL and how it is comparable to Oracle
> in many ways, we were taken aback by the results. Did we code PostgreSQL
> incorrectly? What are we missing or is this the way it is.
>
> Note: once we started running queries on the exact same data in Oracle and
> PostgreSQL we saw a similar pattern. On basic queries little difference, but
> as they started to get more and more complex Oracle was around 3-5 faster.
>
> Again, this was run on identical AWS RDS instances, we ran them many times
> during the day on different days and results were always the same
>
>
>
>
>

Looking at this guy:

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

Takes about 12s with with Postgres 9.4 running on Ubuntu 14.04 hosted on
real HW (Intel i7).

Changing n to be float8 rather than real, i.e:

DO LANGUAGE plpgsql $$ DECLARE n float8;
DECLARE f integer;
BEGIN
FOR f IN 1..10000000 LOOP
n = SQRT (f);
END LOOP;
RAISE NOTICE 'Result => %',n;
END $$;

...time drops to about 2s (which I'm guessing would get it to about
Oracle speed on your EC2 setup).

The moral of the story for this case is that mapping Oracle to Postgres
datatypes can require some careful thought. Using 'native' types (like
integer, float8 etc) will generally give vastly quicker performance.

Adding in the 'if' in the float8 case increases run time to 4s. So looks
like plpgsql might have a slightly higher cost for handling added
conditionals. Be interesting to dig a bit more and see what is taking
the time.

Regards

Mark