Re: BUG #12675: BIGINT Datatype performance

On 01/26/2015 11:04 PM, derek(dot)anderson(at)mspmac(dot)org wrote:
> The following bug has been logged on the website:
>
> Bug reference: 12675
> Logged by: Derek Anderson
> Email address: derek(dot)anderson(at)mspmac(dot)org
> PostgreSQL version: 9.4.0
> Operating system: Linux
> Description:
>
> I did some testing on bigint vs double precision datatype performance for a
> project we have going on here.
>
> To my surprise, I found the double precision (floating) was well over 200%
> faster than bigint in aggregate functions. I don't know if this qualifies
> as a bug, but it sure seems odd considering non-floating type datatypes
> usually perform much faster than floating.
>
> Please contact me if you wish to discuss.

The built-in aggregates on bigint, like sum and avg, use numeric
internally because the sum of bigints can easily be too large to fit in
a bigint. Their return type is also numeric for the same reason.
Arithmetic on numeric is much slower than on double.

There is a patch being discussed that would help with that, for 9.5. See
http://www.postgresql.org/message-id/544BB5F1.50709@proxel.se. With the
patch, the aggregates would use a 128-bit integer type for the internal
calculations, making them much faster.

- Heikki