The faster version looks like it might not be numerically stable; if sum(l_quantity) is greater than the maximum value that can be represented it would overflow and give meaningless results. Given the arrays mentioned by the average function, it could also be maintaining a heap and adding the numbers from smallest to largest for more stability.
This is great research. However I completely disagree that this work supports their conclusion statement:
> In summary, partitioning SQL queries into smaller commutative ones helps you get linear performance improvements as you scale out. And in some cases, they may help you get even more. :)
The time difference between avg() and sum()/count() is merely a performance tuning opportunity in this specific case in specifically Postgres. MySQL, Oracle, etc. are going to have completely different implementations of these functions, and hypothetically could be the exact OPPOSITE performance. However this is not to degrade the insight presented in the actual body of this blog post.
4 comments
[ 2.7 ms ] story [ 25.1 ms ] thread"the numeric average function uses an array of two numerics to keep track of the current sum and count."
> In summary, partitioning SQL queries into smaller commutative ones helps you get linear performance improvements as you scale out. And in some cases, they may help you get even more. :)
The time difference between avg() and sum()/count() is merely a performance tuning opportunity in this specific case in specifically Postgres. MySQL, Oracle, etc. are going to have completely different implementations of these functions, and hypothetically could be the exact OPPOSITE performance. However this is not to degrade the insight presented in the actual body of this blog post.