From: | testman1316 <danilo(dot)ramirez(at)hmhco(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong? |
Date: | 2014-08-04 20:48:32 |
Message-ID: | 1407185312045-5813732.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
--
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2014-08-04 21:07:47 | Re: pg_ctl non-idempotent behavior change |
Previous Message | Alexander Korotkov | 2014-08-04 20:43:41 | Re: KNN-GiST with recheck |