Re: BUG #17329: Aggregate Functions Precision Error

> You might be happier using the numeric type

Postgres defines aggregate functions for the numeric type only for 6
functions of 18.

> Another possibility, for some aggregates, is to order the inputs
> in a way that minimizes error accumulation.

That is not always possible. As I understand this thread
https://postgrespro.com/list/thread-id/1858486#CAKJS1f9L95TySOtBf0AgeZhiLf60BcrgXjOA4NtWptLGkNJFZw(at)mail(dot)gmail(dot)com
,
the parallel calculation for the aggregates was introduced, so the result
depends on the order of float8_regr_combine functions.

BR,
Max

On Thu, Dec 9, 2021 at 8:41 AM Max Neverov <neverov(dot)max(at)gmail(dot)com> wrote:

> > You might be happier using the numeric type
>
> Postgres defines aggregate functions for the numeric type only for 6
> functions of 18.
>
> > Another possibility, for some aggregates, is to order the inputs
> > in a way that minimizes error accumulation.
>
> That is not always possible. As I understand this thread
> https://postgrespro.com/list/thread-id/1858486#CAKJS1f9L95TySOtBf0AgeZhiLf60BcrgXjOA4NtWptLGkNJFZw(at)mail(dot)gmail(dot)com
> ,
> the parallel calculation for the aggregates was introduced, so the result
> depends on the order of float8_regr_combine functions.
>
> BR,
> Max
>
>
> On Wed, Dec 8, 2021 at 10:45 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
>> > Aggregate functions (described here
>> >
>> https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE
>> )
>> > that are defined for double precision type suffer from loss of
>> > significance.
>>
>> This is pretty much inherent in all uses of float arithmetic.
>> You might be happier using the numeric type (of course, that's
>> much slower).
>>
>> Another possibility, for some aggregates, is to order the inputs
>> in a way that minimizes error accumulation. For example,
>>
>> select sum(f1 order by abs(f1)) from ...
>>
>> I don't know offhand what the best such incantation is for covar_pop;
>> it might depend on the problem.
>>
>> regards, tom lane
>>
>