Re: Avg performance for int8/numeric - Mailing list pgsql-patches

From Bruce Momjian
Subject Re: Avg performance for int8/numeric
Date
Msg-id 200611241527.kAOFRAS11857@momjian.us
Whole thread Raw
In response to Avg performance for int8/numeric  (Mark Kirkwood <markir@paradise.net.nz>)
List pgsql-patches
This has been saved for the 8.3 release:

    http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Mark Kirkwood wrote:
> 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


>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

pgsql-patches by date:

Previous
From: Tom Lane
Date:
Subject: Re: Avg performance for int8/numeric
Next
From: Bruce Momjian
Date:
Subject: Re: [PERFORM] Direct I/O issues