]> granicus.if.org Git - postgresql/commit
Improve the accuracy of floating point statistical aggregates.
authorDean Rasheed <dean.a.rasheed@gmail.com>
Sat, 6 Oct 2018 10:20:09 +0000 (11:20 +0100)
committerDean Rasheed <dean.a.rasheed@gmail.com>
Sat, 6 Oct 2018 10:20:09 +0000 (11:20 +0100)
commite954a727f0c8872bf5203186ad0f5312f6183746
treee115f9452c258b546af0713cd4970577a294bd70
parent38921d1416c62bb743f6cc5439d0462efefdb286
Improve the accuracy of floating point statistical aggregates.

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
src/backend/utils/adt/float.c
src/test/regress/expected/aggregates.out
src/test/regress/sql/aggregates.sql