Avg performance for int8/numeric

From: Mark Kirkwood <markir(at)paradise(dot)net(dot)nz>
To: PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org>
Cc: "eng(at)intranet(dot)greenplum(dot)com" <eng(at)intranet(dot)greenplum(dot)com>
Subject: Avg performance for int8/numeric
Date: 2006-11-23 22:08:39
Message-ID: 45661BE7.4050205@paradise.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

Avg performance for these two datatypes can be improved by *not*
calculating the sum of squares in the shared accumulator
(do_numeric_accum). However there is a little subtlety as this function
is also the shared by variance and stddev!

This patch:

- Modifies do_numeric_accum to have an extra bool parameter and does not
calc sumX2 when it is false.
- Amends all the accumulators that call it to include the bool (set to
true).
- Adds new functions [int8|numeric]_avg_accum that call do_numeric_accum
with the bool set to false.
- Amends the the bootstrap entries for pg_aggregate to use the new
accumulators for avg(int8|numeric).
- Adds the new accumulators into the bootstrap entries for pg_proc.

Performance gain is approx 33% (it is still slower than doing sum/count
- possibly due to the construct/deconstruct overhead of the numeric
transition array).

Cheers

Mark

Attachment Content-Type Size
avg1.patch text/x-patch 7.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Larry Rosenman 2006-11-24 01:55:04 Re: Vacuum columns in statistics tables
Previous Message Dave Page 2006-11-23 19:52:38 Re: 8.2 open items list

Browse pgsql-patches by date

  From Date Subject
Next Message Bruce Momjian 2006-11-24 04:06:05 Re: Direct I/O issues
Previous Message Greg Smith 2006-11-23 18:09:54 Re: Direct I/O issues