Re: Improving avg performance for numeric

Hello

2013/3/20 Hadi Moshayedi <hadi@moshayedi.net>:
> Hi Tom,
>
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> After thinking about that for awhile: if we pursue this type of
>> optimization, what would probably be appropriate is to add an aggregate
>> property (stored in pg_aggregate) that allows direct specification of
>> the size that the planner should assume for the aggregate's transition
>> value.  We were getting away with a hardwired assumption of 8K for
>> "internal" because the existing aggregates that used that transtype all
>> had similar properties, but it was always really a band-aid not a proper
>> solution.  A per-aggregate override could be useful in other cases too.
>
> Cool.
>
> I created a patch which adds an aggregate property to pg_aggregate, so
> the transition space is can be overridden. This patch doesn't contain
> the numeric optimizations. It uses "0" (meaning not-set) for all
> existing aggregates.
>
> I manual-tested it a bit, by changing this value for aggregates and
> observing the changes in plan. I also updated some docs and pg_dump.
>
> Does this look like something along the lines of what you meant?

please, can you subscribe your patch to next commitfest?

I tested this patch, and it increase performance about 20% what is
interesting. More - it allows more comfortable custom aggregates for
custom types with better hash agg support.

Regards

Pavel

>
> Thanks,
>   -- Hadi