PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?
От | testman1316 |
---|---|
Тема | PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong? |
Дата | |
Msg-id | 1407185312045-5813732.post@n5.nabble.com обсуждение исходный текст |
Ответы |
Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing
it wrong?
(Mark Kirkwood <mark.kirkwood@catalyst.net.nz>)
Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong? (Pavel Stehule <pavel.stehule@gmail.com>) Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong? (Marti Raudsepp <marti@juffo.org>) Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong? (Roberto Mello <roberto.mello@gmail.com>) |
Список | 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.
В списке pgsql-hackers по дате отправления:
Следующее
От: Mark KirkwoodДата:
Сообщение: Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?