Re: Sum aggregate calculation for single precsion real

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Sum aggregate calculation for single precsion real
Date: 2017-02-13 16:20:49
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> 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 there are reasons of using float4pl function for SUM aggregate instead of float4_accum?

The latter is probably a good two orders of magnitude slower, and it
wouldn't really do much to solve the inherent accuracy problems of
adding float4 values that have a wide dynamic range.

The expectation for SUM(float4) is that you want speed and are
prepared to cope with the consequences. It's easy enough to cast your
input to float8 if you want a wider accumulator, or to numeric if
you'd like more stable (not necessarily more accurate :-() results.
I do not think it's the database's job to make those choices for you.

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Corey Huinker 2017-02-13 16:26:59 Re: \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless)
Previous Message Bernd Helmle 2017-02-13 16:13:10 Re: Should we cacheline align PGXACT?