Re: [PATCH] Negative Transition Aggregate Functions (WIP)

On Sat, Dec 14, 2013 at 8:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Greg Stark <stark(at)mit(dot)edu> writes:
>> On 14 Dec 2013 15:40, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I think you *can't* cover them for the float types; roundoff error
>>> would mean you don't get the same answers as before.
>
>> I was going to say the same thing. But then I started to wonder.... What's
>> so special about the answers we used to give? They are also subject to
>> round off and the results are already quite questionable in those cases.
>
> Well, we can't easily do better than the old answers, and the new ones
> might be arbitrarily worse. Example: sum or average across single-row
> windows ought to be exact in any case, but it might be arbitrarily wrong
> with the negative-transition technique.
>
> More generally, this is supposed to be a performance enhancement only;
> it's not supposed to change the results.
>
> This consideration also makes me question whether we should apply the
> method for NUMERIC. Although in principle numeric addition/subtraction
> is exact, such a sequence could leave us with a different dscale than
> is returned by the existing code. I'm not sure if changing the number of
> trailing zeroes is a big enough behavior change to draw complaints.

I tend to think it is. I'm not sure if it's worth it, but you could
finesse this problem with a more complex transition state - keep track
of how many values with any given scale are part of the current
window.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company