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

Hi

plpgsql has zero optimization for this kind of functions. It is best glue
for SQL statements and relative bad for high expensive numeric
calculations. It is very simple AST interpret only.

Try to use PLPerl, PLPython, PLLua instead for this purposes.

Pavel

2014-08-04 22:48 GMT+02:00 testman1316 <danilo(dot)ramirez(at)hmhco(dot)com>:

> 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(at)postgresql(dot)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(dot)ramirez(at)hmhco(dot)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

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+s1045698n5813763h99(at)n5(dot)nabble(dot)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..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]</user/SendEmail.jtp?type=node&node=5813763&i=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 here<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5813732&code=ZGFuaWxvLnJhbWlyZXpAaG1oY28uY29tfDU4MTM3MzJ8LTE4ODkzODQwNDc=>.
NAML<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>

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

Em segunda-feira, 4 de agosto de 2014, testman1316 <danilo(dot)ramirez(at)hmhco(dot)com>
escreveu:

>
> SET SERVEROUTPUT ON
> SET TIMING ON
>
> DECLARE
> n NUMBER := 0;
> BEGIN
> FOR f IN 1..10000000
> LOOP
> n := SQRT (f);
> END LOOP;
>
>
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.

Roberto

Roberto Mello <roberto(dot)mello(at)gmail(dot)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(at)ymail(dot)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(at)optionshouse(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

Hi

I returned to this issue and maybe I found a root issue. It is PL/pgSQL
implicit IO cast

Original text:

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

Little bit modified

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

It is 3x faster

there is invisible IO conversion from double precision::real via libc
vfprintf

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

Regards

Pavel

2014-08-05 16:02 GMT+02:00 Roberto Mello <roberto(dot)mello(at)gmail(dot)com>:

> On Tue, Aug 5, 2014 at 9:50 AM, Kevin Grittner <kgrittn(at)ymail(dot)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
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Hi

this code is +/- equal to Oracle (it should be eliminate a useless code)

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

2014-08-06 21:41 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

> Hi
>
> I returned to this issue and maybe I found a root issue. It is PL/pgSQL
> implicit IO cast
>
> Original text:
>
> 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
>
> Little bit modified
>
> 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
>
> It is 3x faster
>
> there is invisible IO conversion from double precision::real via libc
> vfprintf
>
> 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
>
> Regards
>
> Pavel
>
>
> 2014-08-05 16:02 GMT+02:00 Roberto Mello <roberto(dot)mello(at)gmail(dot)com>:
>
> On Tue, Aug 5, 2014 at 9:50 AM, Kevin Grittner <kgrittn(at)ymail(dot)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
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>
>

>>>>> "ST" == Shaun Thomas <sthomas(at)optionshouse(dot)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(at)jhcloos(dot)com> OpenPGP: 0x997A9F17ED7DAEA6

2014-08-06 22:07 GMT+02:00 James Cloos <cloos(at)jhcloos(dot)com>:

> >>>>> "ST" == Shaun Thomas <sthomas(at)optionshouse(dot)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 is plpgsql issue only. PL/pgSQL uses a generic cast via serialization to
string and new parsing

It doesn't use a effective libc casting functions.

see
https://github.com/postgres/postgres/blob/master/src/pl/plpgsql/src/pl_exec.c
function

exec_cast_value

>
> -JimC
> --
> James Cloos <cloos(at)jhcloos(dot)com> OpenPGP: 0x997A9F17ED7DAEA6
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)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(at)2ndquadrant(dot)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(at)jhcloos(dot)com> writes:
> "ST" == Shaun Thomas <sthomas(at)optionshouse(dot)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

2014-08-08 2:13 GMT+02:00 Josh Berkus <josh(at)agliodbs(dot)com>:

> 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 ...
>

It is not trivial task. There are two possible direction and both are not
trivial (I am not sure about practical benefits for users - maybe for some
PostGIS users - all for some trivial very synthetic benchmarks)

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

b) we can enhance SPI API to accept target TupDesc (with reusing
transformInsertRow) - it should be little bit less invasive in plpgsql, but
require change in SPI API. This path should be much more preferable - it
can be used in SQL/PSM and it can be used in lot of C extensions - It can
be more simply to specify expected TupDesc than enforce casting via
manipulation with SQL string. I missed this functionality more times. I
designed PL/pgPSM with same type strict level as PostgreSQL has - and this
functionality can simplify code.

PLpgSQL uses spi_prepare_params .. we can enhance this function or we can
introduce new spi_prepare_params_enforce_result_type

Regards

Pavel

>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>