Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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: avg1.patch
Description: text/x-patch (7.9 KB)

Responses

pgsql-hackers by date

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

pgsql-patches by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group