Moving from NUMERIC to FLOAT(8) did indeed lower query times by about
20%.
I will try fixeddecimal and agg() as time permits.
On 25 Jan 2016, at 4:44, David Rowley wrote:
On 25 January 2016 at 15:45, Matt <bsg075@xxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
