Re: Performance options for CPU bound multi-SUM query

On 25 January 2016 at 15:45, Matt <bsg075(at)gmail(dot)com> wrote:
> I have a warehousing case where data is bucketed by a key of an hourly
> timestamp and 3 other columns. In addition there are 32 numeric columns. The
> tables are partitioned on regular date ranges, and aggregated to the lowest
> resolution usable.
>
> The principal use case is to select over a range of dates or hours, filter
> by the other key columns, and SUM() all 32 of the other columns. The
> execution plan shows the primary key index limits row selection efficiently,
> but the query appears CPU bound in performing all of those 32 SUM()
> aggregates.
>

SUM(numeric) also has to work quite a bit harder than an an aggregate
like sum(float8) too since the addition in numeric is implemented in
software.
It depends on the use case, but for some cases the float4 or float8
types might be an option and it will offer much faster aggregation.
There is also https://github.com/2ndQuadrant/fixeddecimal which may be
of some use if you need fixed precision up to a predefined scale. We
found that using fixeddecimal instead of numeric for the TPC-H
benchmark improved performance of query 1 significantly.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services