Thread

1. PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

   Ramirez, Danilo <danilo.ramirez@hmhco.com> — 2014-08-04T20:48:32Z

   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.
   
   
   

2. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

   Mark Kirkwood <mark.kirkwood@catalyst.net.nz> — 2014-08-05T05:56:18Z

   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
   
   
   
   

3. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

   Pavel Stehule <pavel.stehule@gmail.com> — 2014-08-05T06:46:57Z

   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.ramirez@hmhco.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@postgresql.org)
   > To make changes to your subscription:
   > http://www.postgresql.org/mailpref/pgsql-hackers
   >
   

4. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

   Mark Kirkwood <mark.kirkwood@catalyst.net.nz> — 2014-08-05T07:37:58Z

   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
   
   
   

5. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

   Marti Raudsepp <marti@juffo.org> — 2014-08-05T10:16:32Z

   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
   
   
   

6. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

   Ramirez, Danilo <danilo.ramirez@hmhco.com> — 2014-08-05T10:53:33Z

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

7. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

   Roberto Mello <roberto.mello@gmail.com> — 2014-08-05T12:46:49Z

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

8. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

   Kevin Grittner <kgrittn@ymail.com> — 2014-08-05T13:50:49Z

   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
   
   
   

9. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

   Roberto Mello <roberto.mello@gmail.com> — 2014-08-05T14:02:21Z

   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
   
   
   

10. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

    Shaun Thomas <sthomas@optionshouse.com> — 2014-08-05T14:44:19Z

    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
    
    
    

11. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

    Pavel Stehule <pavel.stehule@gmail.com> — 2014-08-06T19:41:27Z

    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.mello@gmail.com>:
    
    > 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
    >
    >
    > --
    > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
    > To make changes to your subscription:
    > http://www.postgresql.org/mailpref/pgsql-hackers
    >
    

12. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

    Pavel Stehule <pavel.stehule@gmail.com> — 2014-08-06T19:46:18Z

    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.stehule@gmail.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.mello@gmail.com>:
    >
    > 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
    >>
    >>
    >> --
    >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
    >> To make changes to your subscription:
    >> http://www.postgresql.org/mailpref/pgsql-hackers
    >>
    >
    >
    

13. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

    James Cloos <cloos@jhcloos.com> — 2014-08-06T20:07:07Z

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

14. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

    Pavel Stehule <pavel.stehule@gmail.com> — 2014-08-06T20:18:37Z

    2014-08-06 22:07 GMT+02:00 James Cloos <cloos@jhcloos.com>:
    
    > >>>>> "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 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@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
    >
    

15. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

    Craig Ringer <craig@2ndquadrant.com> — 2014-08-07T10:12:13Z

    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
    
    
    

16. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

    Merlin Moncure <mmoncure@gmail.com> — 2014-08-07T14:24:06Z

    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
    
    
    

17. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

    Tom Lane <tgl@sss.pgh.pa.us> — 2014-08-07T23:48:12Z

    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
    
    
    

18. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

    Josh Berkus <josh@agliodbs.com> — 2014-08-08T00:13:51Z

    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
    
    
    

19. Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

    Pavel Stehule <pavel.stehule@gmail.com> — 2014-08-13T19:27:39Z

    2014-08-08 2:13 GMT+02:00 Josh Berkus <josh@agliodbs.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@postgresql.org)
    > To make changes to your subscription:
    > http://www.postgresql.org/mailpref/pgsql-hackers
    >