Re: How much slower are numerics?

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org on behalf of CSN
Sent: Fri 10/21/2005 5:30 PM
To: pgsql-general(at)postgresql(dot)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.