Thread: How much slower are numerics?

Another thing I've always wondered about ;), as I use
numerics far more than floats. From the docs:

"However, arithmetic on numeric values is very slow
compared to the integer types, or to the
floating-point types"

How much slower are numerics? And why (I guess it has
to do with potentially different sizes)?

Thanks,
CSN



__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com

CSN <cool_screen_name90001@yahoo.com> writes:

> Another thing I've always wondered about ;), as I use
> numerics far more than floats. From the docs:
>
> "However, arithmetic on numeric values is very slow
> compared to the integer types, or to the
> floating-point types"
>
> How much slower are numerics? And why (I guess it has
> to do with potentially different sizes)?

Integer and floating-point arithmetic are done in hardware.  NUMERIC
is done in software, and is variable size (as you note).  I would
guess that the difference in speed is at least an order of magnitude.

-Doug

-----Original Message-----
From: pgsql-general-owner@postgresql.org on behalf of CSN
Sent: Fri 10/21/2005 5:30 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How much slower are numerics?

Another thing I've always wondered about ;), as I use
numerics far more than floats. From the docs:

"However, arithmetic on numeric values is very slow
compared to the integer types, or to the
floating-point types"

How much slower are numerics? And why (I guess it has
to do with potentially different sizes)?

I think that there was a time when numerics were MUCH slower than floats, but looking at a very simple benchmark I just threw together, I'd say they're pretty close nowadays.  Keep in mind, numerics get EXACT answers, no rounding, while floats get approximate numbers, and are subject to rounding errors.

I created two tables, filled with the same sequence to 10,000 pairs of numbers, one numeric and one float, and ran a simple series of queries across them:

Division:

smarlowe=> insert into n1 select n1/n2 from n;
INSERT 0 10000
Time: 984.549 ms
smarlowe=> insert into f1 select f1/f2 from f;
INSERT 0 10000
Time: 955.376 ms

Multiplcation with division as well:

smarlowe=> insert into n1 select n1*(n2/1000) from n;
INSERT 0 10000
Time: 1939.125 ms
smarlowe=> insert into f1 select f1*(f2/1000) from f;
INSERT 0 10000
Time: 1055.402 ms

Note that I'm also inserting them into a dummy table instead of waiting for them to scroll by in psql etc...  so I'm sure there's some overhead there.  But the difference looks to be anywhere from nearly nothing to maybe twice as slow.  I'd do some more intense testing if I were you, but in reality, the reason to choose one over the other should be whether or not you need a: arbitrarily large numbers (numeric is better) or whether or not you need exact precision (again, numeric is best).

Floats are fine for simple stuff where exactness isn't critical.   But for accounting applications, it's numerics all the way.

"Scott Marlowe" <smarlowe@g2switchworks.com> writes:
>> How much slower are numerics? And why (I guess it has
>> to do with potentially different sizes)?

> I think that there was a time when numerics were MUCH slower than =
> floats, but looking at a very simple benchmark I just threw together, =
> I'd say they're pretty close nowadays.

I think your benchmark is mostly measuring insert overhead (WAL etc).

On modern hardware, I'd expect float operations to be at least an order
of magnitude faster than numerics, if you measure only the arithmetic
operation itself and not any of the generic data-movement overhead.
Here's a trivial example, which is still mostly dominated by
plpgsql's looping and assignment overhead:

regression=# create or replace function timeit(int) returns void as $$
regression$# declare x float8 := 0;
regression$# begin
regression$#   for i in 1..$1 loop
regression$#     x := x + 1;
regression$#   end loop;
regression$# end $$ language plpgsql;
CREATE FUNCTION
regression=# \timing
Timing is on.
regression=# select timeit(1000000);
 timeit
--------

(1 row)

Time: 13700.960 ms
regression=# create or replace function timeit(int) returns void as $$
regression$# declare x numeric := 0;
regression$# begin
regression$#   for i in 1..$1 loop
regression$#     x := x + 1;
regression$#   end loop;
regression$# end $$ language plpgsql;
CREATE FUNCTION
regression=# select timeit(1000000);
 timeit
--------

(1 row)

Time: 22145.408 ms

So the question is basically whether your application is sensitive to
the actual speed of arithmetic or not ...

            regards, tom lane

Tom Lane wrote:
> On modern hardware, I'd expect float operations to be at least an order
> of magnitude faster than numerics
> [snip]
> regression$# declare x float8 := 0;
> regression=# select timeit(1000000);
> Time: 13700.960 ms
 > [snip]
> regression$# declare x numeric := 0;
> regression=# select timeit(1000000);
> Time: 22145.408 ms

But, this is less than a factor of 2 difference, similar to what one of
the previous INSERT examples showed.  Or am I missing something?

--
-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
  Jon Lapham  <lapham@jandr.org>                Rio de Janeiro, Brasil
  Personal: http://www.jandr.org/
***-*--*----*-------*------------*--------------------*---------------

Jon Lapham <lapham@jandr.org> writes:
> Tom Lane wrote:
>> On modern hardware, I'd expect float operations to be at least an order
>> of magnitude faster than numerics

> But, this is less than a factor of 2 difference, similar to what one of
> the previous INSERT examples showed.  Or am I missing something?

You're not thinking about the overhead imposed by the plpgsql loop
construct.

            regards, tom lane