Re: Numeric performances - Mailing list pgsql-general

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