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

2014-08-05 Thread testman1316
You are correct sir, 4.1 seconds.  Are you a consulant?  We ae looking for a 
Postgresql guru for advice.   We are doing a proof of concept of Postgresql on 
AWS

From: Mark Kirkwood-2 [via PostgreSQL] 
[mailto:ml-node+s1045698n5813763...@n5.nabble.com]
Sent: Tuesday, August 05, 2014 12:58 AM
To: Ramirez, Danilo
Subject: 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..1000
 LOOP
  n := SQRT (f);
END LOOP;
 END;

 PostgreSQL

 DO LANGUAGE plpgsql $$ DECLARE n real;
 DECLARE f integer;
 BEGIN
 FOR f IN 1..1000 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..1000
 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..1000 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..1000 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..1000 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..1000 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



--
Sent via pgsql-hackers mailing list ([hidden 
email]/user/SendEmail.jtp?type=nodenode=5813763i=0)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/PostrgeSQL-vs-oracle-doing-1-million-sqrts-am-I-doing-it-wrong-tp5813732p5813763.html
To unsubscribe from PostrgeSQL vs oracle doing 1 million sqrts am I doing it 
wrong?, click 
herehttp://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=5813732code=ZGFuaWxvLnJhbWlyZXpAaG1oY28uY29tfDU4MTM3MzJ8LTE4ODkzODQwNDc=.
NAMLhttp://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml

[HACKERS] PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

2014-08-04 Thread testman1316
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..1000
LOOP
n := SQRT (f);
  END LOOP;
END;

PostgreSQL

DO LANGUAGE plpgsql $$ DECLARE n real;
DECLARE f integer;
BEGIN
FOR f IN 1..1000 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..1000
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..1000 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..1000 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




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PostrgeSQL-vs-oracle-doing-1-million-sqrts-am-I-doing-it-wrong-tp5813732.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers