Postgres Performance: To avg or To sum/count

http://www.citusdata.com/blog/53-postgres-performance-to-avg-or-to-sum-divided-by-count

by ozgune • 14 years ago

27 4 14 years ago

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.

Someone• 14 years ago

According to the post, it does not. That array has two elements:

"the numeric average function uses an array of two numerics to keep track of the current sum and count."

tedchs• 14 years ago

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.

ozgune• 14 years ago

Hey, just updated the conclusion to clarify. Thanks for the feedback.

Postgres Performance: To avg or To sum/count

Related Stories