• Dean Rasheed's avatar
    Improve the accuracy of floating point statistical aggregates. · e954a727
    Dean Rasheed authored
    When computing statistical aggregates like variance, the common
    schoolbook algorithm which computes the sum of the squares of the
    values and subtracts the square of the mean can lead to a large loss
    of precision when using floating point arithmetic, because the
    difference between the two terms is often very small relative to the
    terms themselves.
    
    To avoid this, re-work these aggregates to use the Youngs-Cramer
    algorithm, which is a proven, numerically stable algorithm that
    directly aggregates the sum of the squares of the differences of the
    values from the mean in a single pass over the data.
    
    While at it, improve the test coverage to test the aggregate combine
    functions used during parallel aggregation.
    
    Per report and suggested algorithm from Erich Schubert.
    
    Patch by me, reviewed by Madeleine Thompson.
    
    Discussion: https://postgr.es/m/153313051300.1397.9594490737341194671@wrigleys.postgresql.org
    e954a727
aggregates.sql 30.2 KB