Thread: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?
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.
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
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 05/08/14 17:56, Mark Kirkwood wrote: > > 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. > Thinking about this a bit more, I wonder if the 'big O' has added some optimizations in PL/SQL for trivial conditionals - i.e you are adding: IF (0 = 0) THEN END IF; ...it may be going...'Ah yes, always true...so remove'! So it might be interesting to try some (hopefully not so easily removable) non trivial ones like: DO LANGUAGE plpgsql $$ DECLARE DECLARE i integer; BEGIN FOR i IN 1..10000000 LOOP IF (i%100 = 0) THEN NULL; END IF; END LOOP; END $$; Now I guess there is the chance that PL/SQL might understand that NULL inside a loop means it can remove it...so you may need to experiment further. The point to take away here is that for interesting loops and conditions - there may be not such a significant difference! Regards Mark
On Mon, Aug 4, 2014 at 11:48 PM, testman1316 <danilo.ramirez@hmhco.com> wrote: > 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. > 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. Looks like from the test cases you posted, you're not actually benchmarking any *queries*, you're comparing the speeds of the procedural languages. And yes, PL/pgSQL is known to be a farily slow language. If you want fair benchmark results, you should instead concentrate on what databases are supposed to do: store and retrieve data; finding the most optimal way to execute complicated SQL queries. In most setups, that's where the majority of database processor time is spent, not procedure code. Regards, Marti
Em segunda-feira, 4 de agosto de 2014, testman1316 <danilo.ramirez@hmhco.com> escreveu:
SET SERVEROUTPUT ON
SET TIMING ON
DECLARE
n NUMBER := 0;
BEGIN
FOR f IN 1..10000000
LOOP
n := SQRT (f);
END LOOP;
Roberto Mello <roberto.mello@gmail.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
On Tue, Aug 5, 2014 at 9:50 AM, Kevin Grittner <kgrittn@ymail.com> wrote: > > Since that is outside the loop, the difference should be nominal; Apologies. I misread on my phone and though it was within the loop. > and in a quick test it was. On the other hand, reducing the > procedural code made a big difference. <snip> > test=# DO LANGUAGE plpgsql $$ DECLARE n real; > BEGIN > PERFORM SQRT(f) FROM generate_series(1, 10000000) x(f); > END $$; > DO > Time: 3916.815 ms That is a big difference. Are you porting a lot of code from PL/SQL, and therefore evaluating the performance difference of running this code? Or is this just a general test where you wish to assess the performance difference? PL/pgSQL could definitely use some loving, as far as optimization goes, but my feeling is that it hasn't happened because there are other suitable backends that give the necessary flexibility for the different use cases. Roberto
On 08/05/2014 12:56 AM, Mark Kirkwood wrote: > 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. We've seen a lot of this ourselves. Oracle's NUMERIC is a native type, whereas ours is emulated. From the performance, it would appear that REAL is another calculated type. At least you used INT though. I've seen too many Oracle shops using NUMERIC in PostgreSQL because it's there, and suffering major performance hits because of it. That said, the documentation here says FLOAT4 is an alias for REAL, so it's somewhat nonintuitive for FLOAT4 to be so much slower than FLOAT8, which is an alias for DOUBLE PRECISION. http://www.postgresql.org/docs/9.3/static/datatype.html Not sure why that would be. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
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+[hidden email]]
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..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
--
Sent via pgsql-hackers mailing list ([hidden email])
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:
To unsubscribe from PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?, click here.
NAML
View this message in context: RE: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
postgres=# 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 $$;
NOTICE: Result => 3162.28
DO
Time: 31988.720 ms
postgres=# DO LANGUAGE plpgsql $$ DECLARE n real;
DECLARE f integer;
BEGIN
FOR f IN 1..10000000 LOOP
if 0=0 then
n = SQRT (f)::real;
end if;
END LOOP;
RAISE NOTICE 'Result => %',n;
END $$;
NOTICE: Result => 3162.28
DO
Time: 9660.592 ms
https://github.com/okbob/plpgsql_check/ can raise a performance warning in this situation, but we cannot do too much now without possible breaking compatibility
On Tue, Aug 5, 2014 at 9:50 AM, Kevin Grittner <kgrittn@ymail.com> wrote:Apologies. I misread on my phone and though it was within the loop.
>
> Since that is outside the loop, the difference should be nominal;<snip>
> and in a quick test it was. On the other hand, reducing the
> procedural code made a big difference.That is a big difference. Are you porting a lot of code from PL/SQL,
> test=# DO LANGUAGE plpgsql $$ DECLARE n real;
> BEGIN
> PERFORM SQRT(f) FROM generate_series(1, 10000000) x(f);
> END $$;
> DO
> Time: 3916.815 ms
and therefore evaluating the performance difference of running this
code? Or is this just a general test where you wish to assess the
performance difference?
PL/pgSQL could definitely use some loving, as far as optimization
goes, but my feeling is that it hasn't happened because there are
other suitable backends that give the necessary flexibility for the
different use cases.
Roberto
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
postgres=# DO LANGUAGE plpgsql $$ DECLARE n real;
DECLARE f integer;
BEGIN
FOR f IN 1..10000000 LOOP
--if 0=0 then
n = SQRT (f)::real;
--end if;
END LOOP;
RAISE NOTICE 'Result => %',n;
END $$;
NOTICE: Result => 3162.28
DO
Time: 5787.797 ms
PavelIt is 3x fasterLittle bit modifiedOriginal text:HiI returned to this issue and maybe I found a root issue. It is PL/pgSQL implicit IO cast
postgres=# DO LANGUAGE plpgsql $$ DECLARE n real;
DECLARE f integer;
BEGIN
FOR f IN 1..10000000 LOOPif 0=0 then
n = SQRT (f);
end if;END LOOP;Time: 31988.720 ms
RAISE NOTICE 'Result => %',n;
END $$;
NOTICE: Result => 3162.28
DO
postgres=# DO LANGUAGE plpgsql $$ DECLARE n real;if 0=0 then
DECLARE f integer;
BEGIN
FOR f IN 1..10000000 LOOP
n = SQRT (f)::real;
end if;Time: 9660.592 ms
END LOOP;
RAISE NOTICE 'Result => %',n;
END $$;
NOTICE: Result => 3162.28
DOthere is invisible IO conversion from double precision::real via libc vfprintfRegards
https://github.com/okbob/plpgsql_check/ can raise a performance warning in this situation, but we cannot do too much now without possible breaking compatibility2014-08-05 16:02 GMT+02:00 Roberto Mello <roberto.mello@gmail.com>:On Tue, Aug 5, 2014 at 9:50 AM, Kevin Grittner <kgrittn@ymail.com> wrote:Apologies. I misread on my phone and though it was within the loop.
>
> Since that is outside the loop, the difference should be nominal;<snip>
> and in a quick test it was. On the other hand, reducing the
> procedural code made a big difference.That is a big difference. Are you porting a lot of code from PL/SQL,
> test=# DO LANGUAGE plpgsql $$ DECLARE n real;
> BEGIN
> PERFORM SQRT(f) FROM generate_series(1, 10000000) x(f);
> END $$;
> DO
> Time: 3916.815 ms
and therefore evaluating the performance difference of running this
code? Or is this just a general test where you wish to assess the
performance difference?
PL/pgSQL could definitely use some loving, as far as optimization
goes, but my feeling is that it hasn't happened because there are
other suitable backends that give the necessary flexibility for the
different use cases.
Roberto
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
>>>>> "ST" == Shaun Thomas <sthomas@optionshouse.com> writes: ST> That said, the documentation here says FLOAT4 is an alias for REAL, ST> so it's somewhat nonintuitive for FLOAT4 to be so much slower than ST> FLOAT8, which is an alias for DOUBLE PRECISION. There are some versions of glibc where doing certain math on double is faster than doing it on float, depending on how things are compiled. Maybe this is one of them? -JimC -- James Cloos <cloos@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6
>>>>> "ST" == Shaun Thomas <sthomas@optionshouse.com> writes:
ST> That said, the documentation here says FLOAT4 is an alias for REAL,
ST> so it's somewhat nonintuitive for FLOAT4 to be so much slower than
ST> FLOAT8, which is an alias for DOUBLE PRECISION.
There are some versions of glibc where doing certain math on double is
faster than doing it on float, depending on how things are compiled.
Maybe this is one of them?
see https://github.com/postgres/postgres/blob/master/src/pl/plpgsql/src/pl_exec.c function
exec_cast_value
-JimC
--
James Cloos <cloos@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 08/05/2014 10:44 PM, Shaun Thomas wrote: > On 08/05/2014 12:56 AM, Mark Kirkwood wrote: > >> 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. > > We've seen a lot of this ourselves. Oracle's NUMERIC is a native type, > whereas ours is emulated. I'm not sure what you mean by "native" vs "emulated" here. PostgreSQL's NUMERIC is binary-coded decimal with mathematical operations performed in software. According to the docs, my impression is that Oracle's NUMBER is stored more like a decfloat: http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i22289 but my Oracle expertise is admittedly lacking. New Intel hardware supports IEEE 754:2008 decimal floating point in hardware, and I'm quite interested in implementing DECFLOAT(n) for PostgreSQL to take advantage of that. A DECFLOAT type would also be more compatible with things like the C# "Decimal" type than our current NUMERIC is. > At least you used INT though. I've seen too many Oracle shops using > NUMERIC in PostgreSQL because it's there, and suffering major > performance hits because of it. In retrospect it might be a bit of a loss that the numeric type format doesn't reserve a couple of bits for short-value flags, so we could store and work with native integers for common values. There's NumericShort and NumericLong, but no NumericNative or NumericInt32 or whatever. OTOH, by the time you handle alignment and padding requirements and the cost of deciding which numeric format the input is, it might not've been much faster. Presumably it was looked at during the introduction of NumericShort. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Thu, Aug 7, 2014 at 5:12 AM, Craig Ringer <craig@2ndquadrant.com> wrote: > New Intel hardware supports IEEE 754:2008 decimal floating point in > hardware, and I'm quite interested in implementing DECFLOAT(n) for > PostgreSQL to take advantage of that. +1 merlin
James Cloos <cloos@jhcloos.com> writes: > "ST" == Shaun Thomas <sthomas@optionshouse.com> writes: > ST> That said, the documentation here says FLOAT4 is an alias for REAL, > ST> so it's somewhat nonintuitive for FLOAT4 to be so much slower than > ST> FLOAT8, which is an alias for DOUBLE PRECISION. > There are some versions of glibc where doing certain math on double is > faster than doing it on float, depending on how things are compiled. > Maybe this is one of them? No, it isn't. The problem here is that the result of SQRT() is float8 (a/k/a double precision) while the variable that it is to be assigned to is float4 (a/k/a real). As was noted upthread, changing the variable's declared type to eliminate the run-time type coercion removes just about all the discrepancy between PG and Oracle runtimes. The original comparison is not apples-to-apples because the Oracle coding required no type coercions. (Or at least, so I assume; I'm not too familiar with Oracle's math functions.) plpgsql is not efficient at all about coercions performed as a side effect of assignments; if memory serves, it always handles them by converting to text and back. So basically the added cost here came from float8out() and float4in(). There has been some talk of trying to do such coercions via SQL casts, but nothing's been done for fear of compatibility problems. regards, tom lane
On 08/07/2014 04:48 PM, Tom Lane wrote: > plpgsql is not efficient at all about coercions performed as a side > effect of assignments; if memory serves, it always handles them by > converting to text and back. So basically the added cost here came > from float8out() and float4in(). There has been some talk of trying > to do such coercions via SQL casts, but nothing's been done for fear > of compatibility problems. Yeah, that's a weeks-long project for someone. And would require a lot of tests ... -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 08/07/2014 04:48 PM, Tom Lane wrote:Yeah, that's a weeks-long project for someone. And would require a lot
> plpgsql is not efficient at all about coercions performed as a side
> effect of assignments; if memory serves, it always handles them by
> converting to text and back. So basically the added cost here came
> from float8out() and float4in(). There has been some talk of trying
> to do such coercions via SQL casts, but nothing's been done for fear
> of compatibility problems.
of tests ...
a) we can enhance plpgsql exec_assign_value to accept pointer to cache on tupmap - it is relative invasive in plpgsql - and without benefits to other PL
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers