Re: [HACKERS] Sum aggregate calculation for single precsion real

Robert Haas writes: > On Fri, Feb 17, 2017 at 6:51 AM, Greg Stark wrote: >> Moreover, it wouldn't be hard to make sum(float4) use a float8 as an >> accumulator and then cast to float4 for the final state. That would be >> 100% compatible with the existing

Re: [HACKERS] Sum aggregate calculation for single precsion real

On Fri, Feb 17, 2017 at 6:51 AM, Greg Stark wrote: > On 15 February 2017 at 12:52, Robert Haas wrote: >> Personally, I find it somewhere in the middle: I think the way it >> works now is reasonable, and I think what he wants would have been >> reasonable as

Re: [HACKERS] Sum aggregate calculation for single precsion real

On 15 February 2017 at 12:52, Robert Haas wrote: > Personally, I find it somewhere in the middle: I think the way it > works now is reasonable, and I think what he wants would have been > reasonable as well. However, I find it hard to believe it would be > worth changing

Re: [HACKERS] Sum aggregate calculation for single precsion real

On Wed, Feb 15, 2017 at 9:52 AM, Robert Haas wrote: > On Tue, Feb 14, 2017 at 11:45 PM, Tom Lane wrote: >> You could perhaps make an argument that sum(float4) would have less risk >> of overflow if it accumulated in and returned float8, but frankly that

Re: [HACKERS] Sum aggregate calculation for single precsion real

Robert Haas writes: > I think that's more or less the argument Konstantin is in fact making. > Whether it's a good argument or a thin one is a value judgement. > Personally, I find it somewhere in the middle: I think the way it > works now is reasonable, and I think what he

Re: [HACKERS] Sum aggregate calculation for single precsion real

On Tue, Feb 14, 2017 at 11:45 PM, Tom Lane wrote: > You could perhaps make an argument that sum(float4) would have less risk > of overflow if it accumulated in and returned float8, but frankly that > seems a bit thin. I think that's more or less the argument Konstantin is in

Re: [HACKERS] Sum aggregate calculation for single precsion real

On 14.02.2017 16:59, Jim Nasby wrote: On 2/13/17 10:45 AM, Konstantin Knizhnik wrote: It is not true - please notice query execution time of this two queries: I bet you'd get even less difference if you simply cast to float8 instead of adding 0.0. Same result, no floating point addition.

Re: [HACKERS] Sum aggregate calculation for single precsion real

Robert Haas writes: > Well put. Although it's worth noting that we aren't 100% consistent > about this stuff: sum(smallint), sum(integer), and sum(bigint) all use > an output data type different from the input data type, but other > versions of sum() don't. In those cases

Re: [HACKERS] Sum aggregate calculation for single precsion real

On Tue, Feb 14, 2017 at 8:59 AM, Jim Nasby wrote: >> From my point of your it is strange and wrong expectation. >> I am choosing "float4" type for a column just because it is enough to >> represent range of data I have and I need to minimize size of record. > > In other

Re: [HACKERS] Sum aggregate calculation for single precsion real

On 2/13/17 10:45 AM, Konstantin Knizhnik wrote: It is not true - please notice query execution time of this two queries: I bet you'd get even less difference if you simply cast to float8 instead of adding 0.0. Same result, no floating point addition. The expectation for SUM(float4) is that

Re: [HACKERS] Sum aggregate calculation for single precsion real

On 13.02.2017 19:20, Tom Lane wrote: Konstantin Knizhnik writes: I wonder why SUM aggregate is calculated for real (float4) type using floating point accumulator? If you can't deal with the vagaries of floating-point arithmetic, you shouldn't be storing your data

Re: [HACKERS] Sum aggregate calculation for single precsion real

Konstantin Knizhnik writes: > I wonder why SUM aggregate is calculated for real (float4) type using > floating point accumulator? If you can't deal with the vagaries of floating-point arithmetic, you shouldn't be storing your data in float format. Use numeric. > Are

[HACKERS] Sum aggregate calculation for single precsion real

Hi hackers, I wonder why SUM aggregate is calculated for real (float4) type using floating point accumulator? It cause very confusing and unexpected behavior: -- postgres=# select sum(l_quantity) from lineitem where l_shipdate <= '1998-12-01'; sum - 1.52688e+09 (1 row)