Numeric performances

Vincenzo Romano escribi�:

Hi all.
I'd like to know whether there is any "real world" evaluation (aka test) on
performances of the NUMERIC data type when compared to FLOAT8 and FLOAT4.
The documentation simply says that the former is "much slower" than the latter
ones.

It is. But why do you care? You either have the correctness that
NUMERIC gives, or you don't.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Thursday 31 May 2007 19:42:20 Alvaro Herrera wrote:

It is. But why do you care? You either have the correctness that
NUMERIC gives, or you don't.

Because FLOAT8 can be precise enough for some application and not
enough for other ones.

--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]

On Thu, May 31, 2007 at 09:25:27PM +0200, Vincenzo Romano wrote:

On Thursday 31 May 2007 19:42:20 Alvaro Herrera wrote:

It is. But why do you care? You either have the correctness that
NUMERIC gives, or you don't.

Because FLOAT8 can be precise enough for some application and not
enough for other ones.

If you can use float, use it. There's hardware support for that,
whereas there's none for numeric...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Show quoted text

From each according to his ability. To each according to his ability to litigate.

At 01:42 AM 6/1/2007, Alvaro Herrera wrote:

Vincenzo Romano escribió:

Hi all.
I'd like to know whether there is any "real world" evaluation (aka test) on
performances of the NUMERIC data type when compared to FLOAT8 and FLOAT4.
The documentation simply says that the former

is "much slower" than the latter

ones.

It is. But why do you care? You either have the correctness that
NUMERIC gives, or you don't.

I suspect it's still useful to know what order of
magnitude slower it is. After all if it is 1000x
slower (not saying it is), some people may decide
it's not worth it or roll their own.

Any hints/gotchas for/when doing such performance tests?

Regards,
Link.

It is. But why do you care? You either have the correctness that
NUMERIC gives, or you don't.

I suspect it's still useful to know what order of magnitude slower it
is. After all if it is 1000x slower (not saying it is), some people may
decide it's not worth it or roll their own.

Any hints/gotchas for/when doing such performance tests?

forum_bench=> CREATE TEMPORARY TABLE test AS SELECT a::FLOAT AS f,
a::NUMERIC AS n, a::INTEGER AS i, a::BIGINT AS b FROM
generate_series( 1,100000 ) AS a;
SELECT
Temps : 1169,125 ms

forum_bench=> SELECT sum(i) FROM test;
Temps : 46,589 ms

forum_bench=> SELECT sum(b) FROM test;
Temps : 157,018 ms

forum_bench=> SELECT sum(f) FROM test;
Temps : 63,865 ms

forum_bench=> SELECT sum(n) FROM test;
Temps : 124,816 ms

SELECT * FROM test ORDER BY i LIMIT 1; SELECT * FROM test ORDER BY b LIMIT
1; SELECT * FROM test ORDER BY f LIMIT 1; SELECT * FROM test ORDER BY n
LIMIT 1;
Temps : 68,996 ms
Temps : 68,917 ms
Temps : 62,321 ms
Temps : 71,880 ms

BEGIN; CREATE INDEX test_i ON test(i); CREATE INDEX test_b ON test(b);
CREATE INDEX test_f ON test(f); CREATE INDEX test_n ON test(n); ROLLBACK;
CREATE INDEX
Temps : 102,901 ms
CREATE INDEX
Temps : 123,406 ms
CREATE INDEX
Temps : 105,255 ms
CREATE INDEX
Temps : 134,468 ms

Hmmm ...

It sounds quite strange to me that numeric is faster than bigint.

Even if bigint didn't get hw support in the CPU it should have been
faster that numeric as it should be mapped in 2 32-bits integers.

Numeric algorithms should be linear (according to the number of digits) in
complexity when compared to float, float8, integer and bigint (that should be
constant in my mind).

Nonetheless the suggested "fast test" makes some sense in my mind.

On Monday 04 June 2007 12:06:47 PFC wrote:

It is. But why do you care? You either have the correctness that
NUMERIC gives, or you don't.

I suspect it's still useful to know what order of magnitude slower it
is. After all if it is 1000x slower (not saying it is), some people may
decide it's not worth it or roll their own.

Any hints/gotchas for/when doing such performance tests?

forum_bench=> CREATE TEMPORARY TABLE test AS SELECT a::FLOAT AS f,
a::NUMERIC AS n, a::INTEGER AS i, a::BIGINT AS b FROM
generate_series( 1,100000 ) AS a;
SELECT
Temps : 1169,125 ms

forum_bench=> SELECT sum(i) FROM test;
Temps : 46,589 ms

forum_bench=> SELECT sum(b) FROM test;
Temps : 157,018 ms

forum_bench=> SELECT sum(f) FROM test;
Temps : 63,865 ms

forum_bench=> SELECT sum(n) FROM test;
Temps : 124,816 ms

SELECT * FROM test ORDER BY i LIMIT 1; SELECT * FROM test ORDER BY b LIMIT
1; SELECT * FROM test ORDER BY f LIMIT 1; SELECT * FROM test ORDER BY n
LIMIT 1;
Temps : 68,996 ms
Temps : 68,917 ms
Temps : 62,321 ms
Temps : 71,880 ms

BEGIN; CREATE INDEX test_i ON test(i); CREATE INDEX test_b ON test(b);
CREATE INDEX test_f ON test(f); CREATE INDEX test_n ON test(n); ROLLBACK;
CREATE INDEX
Temps : 102,901 ms
CREATE INDEX
Temps : 123,406 ms
CREATE INDEX
Temps : 105,255 ms
CREATE INDEX
Temps : 134,468 ms

--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]

This is a 32 bit CPU by the way.

Consider this :

- There are 100K rows
- The CPU executes about 3 billion instructions per second if everything
is optimum
- "SELECT sum(n) FROM test", takes, say 60 ms

This gives about 1800 CPU ops per row.
A Float addition versus an Int addition is a drop in the sea.

I believe the marked difference between Floats/Ints (faster) and
Numeric/Bigint (slower) comes from being passed by value or by pointers.

A single access which misses the CPU cache and has to go fetch data from
the real RAM spends a lot more cycles than the simple loops in a NUMERIC
addition which will hit L1 cache.
Nowadays cache access patterns matter more than how many actual CPU
instructions are executed...

Show quoted text

forum_bench=> SELECT sum(i) FROM test;
Temps : 46,589 ms
forum_bench=> SELECT sum(f) FROM test;
Temps : 63,865 ms

forum_bench=> SELECT sum(b) FROM test;
Temps : 157,018 ms
forum_bench=> SELECT sum(n) FROM test;
Temps : 124,816 ms

I'm getting more confused.

If the algorithm used to do the sum is a drop in the sea,
then the resources needed to pass a pointer on the stack are
a molecule in the drop! :-)

Nonetheless I think that your directions are right:
doing actual queries instead of inspecting the algorithms themselves
should yeld numbers that are (by definition) coherent with real world usage!

Another point is related to storage.
I think that as far as the storage for a numeric is within few bytes, the
difference should be related only to the algorithm.
But with larger size, you have no option with floats!

So, finally, the question should have been:

When used in the same ranges as FLOAT8 or FLOAT, what'd be the performance
impact of NUMERIC?

Sorry for having been unclear. And thanks for the hints.

On Monday 04 June 2007 13:17:49 PFC wrote:

This is a 32 bit CPU by the way.

Consider this :

- There are 100K rows
- The CPU executes about 3 billion instructions per second if everything
is optimum
- "SELECT sum(n) FROM test", takes, say 60 ms

This gives about 1800 CPU ops per row.
A Float addition versus an Int addition is a drop in the sea.

I believe the marked difference between Floats/Ints (faster) and
Numeric/Bigint (slower) comes from being passed by value or by pointers.

A single access which misses the CPU cache and has to go fetch data from
the real RAM spends a lot more cycles than the simple loops in a NUMERIC
addition which will hit L1 cache.
Nowadays cache access patterns matter more than how many actual CPU
instructions are executed...

forum_bench=> SELECT sum(i) FROM test;
Temps : 46,589 ms
forum_bench=> SELECT sum(f) FROM test;
Temps : 63,865 ms

forum_bench=> SELECT sum(b) FROM test;
Temps : 157,018 ms
forum_bench=> SELECT sum(n) FROM test;
Temps : 124,816 ms

--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]

Vincenzo Romano <vincenzo.romano@gmail.com> writes:

It sounds quite strange to me that numeric is faster than bigint.

This test is 100% faulty, because it fails to consider the fact that the
accumulator used by sum() isn't necessarily the same type as the input
data. In fact we sum ints in a bigint and bigints in a numeric to avoid
overflow.

If you try it with max() you'd likely get less-surprising answers.

regards, tom lane

If you try it with max() you'd likely get less-surprising answers.

So it was in fact the type conversions that got timed.
Damn. I got outsmarted XDD

Rewind :

CREATE TEMPORARY TABLE test AS SELECT a::FLOAT AS f,
(a::NUMERIC)*100000000000000 AS n, a::INTEGER AS i, a::BIGINT AS b FROM
generate_series( 1,100000 ) AS a;

Max and Sort will use comparisons :

SELECT max(i) FROM test; SELECT max(b) FROM test; SELECT max(f) FROM test;
SELECT max(n) FROM test;
Temps : 42,132 ms
Temps : 59,499 ms
Temps : 58,808 ms
Temps : 54,197 ms

SELECT * FROM test ORDER BY i LIMIT 1; SELECT * FROM test ORDER BY b LIMIT
1; SELECT * FROM test ORDER BY f LIMIT 1; SELECT * FROM test ORDER BY n
LIMIT 1;
Temps : 58,723 ms
Temps : 60,520 ms
Temps : 53,188 ms
Temps : 61,779 ms

SELECT count(*) FROM test a JOIN test b ON (a.i=b.i);
Temps : 275,411 ms

SELECT count(*) FROM test a JOIN test b ON (a.b=b.b);
Temps : 286,132 ms

SELECT count(*) FROM test a JOIN test b ON (a.f=b.f);
Temps : 295,956 ms

SELECT count(*) FROM test a JOIN test b ON (a.n=b.n);
Temps : 321,292 ms

SELECT count(*) FROM test a JOIN test b ON (a.i=b.b);
Temps : 281,162 ms

SELECT count(*) FROM test a JOIN test b ON
(a.n=b.i::NUMERIC*100000000000000);
Temps : 454,706 ms

Now, addition :

SELECT count(i+1) FROM test;
Temps : 46,973 ms

SELECT count(b+1) FROM test;
Temps : 60,027 ms

SELECT count(f+1) FROM test;
Temps : 56,829 ms

SELECT count(n+1) FROM test;
Temps : 103,316 ms

Multiplication :

SELECT count(i*1) FROM test;
Temps : 46,950 ms

SELECT count(b*1) FROM test;
Temps : 58,670 ms

SELECT count(f*1) FROM test;
Temps : 57,058 ms

SELECT count(n*1) FROM test;
Temps : 108,379 ms

SELECT count(i) FROM test;
Temps : 38,351 ms

SELECT count(i/1234) FROM test;
Temps : 48,961 ms

SELECT count(b/1234) FROM test;
Temps : 62,496 ms

SELECT count(n/1234) FROM test;
Temps : 186,674 ms

Conclusion : numeric seems a bit slower (division being slowest
obviously) but nothing that can't be swamped by a few disk seeks...