On Sat, 29 Jul 2006, Tom Lane wrote:
Peter Eisentraut <[EMAIL PROTECTED]> writes:
... From a mathematician's point of view, however, some of these
functions normally produce irrational numbers anyway, so it seems
unlikely that numeric will be useful. But looking at the definition
of, say, regr_avgx(Y, X), if all the input values are integers, it
might be useful if I could get an exact integer or rational number as
output, instead of a float, that is.
The question is whether this is useful enough to justify adding a
thousand lines to numeric.c.
I believe also that the numeric forms of the aggregates will be
enormously slower than the float forms, at least on most modern
machines with decent floating-point performance. I don't have time
to do any performance testing though.
Based on just simple tests On my laptop (1.8Ghz Centrino) the numeric
versions are 5-10 times slower (depending on what 2arg function is used)
The example:
template1=# \d test
Table "public.test"
Column | Type | Modifiers
--------+------------------+-----------
na | numeric |
nb | numeric |
da | double precision |
db | double precision |
template1=# select count(*) from test;
count
---------
1000000
(1 row)
template1=# select regr_avgx(da,db) from test;
regr_avgx
-----------------
0.5002412120227
(1 row)
Time: 1052,893 ms
template1=# select regr_avgx(na,nb) from test;
regr_avgx
-------------------------
0.500040167263887822939
(1 row)
Time: 4459,739 ms
template1=# select regr_sxx(da,db) from test;
regr_sxx
------------------
83303.6317359119
(1 row)
Time: 1043,891 ms
template1=# select regr_sxx(na,nb) from test;
regr_sxx
--------------------------------------------------
83342.044294954140912267902323775495680113567986
(1 row)
Time: 8514,843 ms
template1=# select corr(da,db) from test;
corr
----------------------
0.000527588261283456
(1 row)
Time: 1074,948 ms
template1=# select corr(na,nb) from test;
corr
----------------------------------------------------------------------------------------
0.000759857150984988517883855238363403977440313567465424735082001422354119457463407737
(1 row)
Time: 18327,376 ms
Yes, that's significantly slower, but I don't think that it make the
functions unusable...
Regards,
Sergey
*******************************************************************
Sergey E. Koposov
Max Planck Institute for Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
