Re: Sharing aggregate states between different aggregate functions

On 27 July 2015 at 18:15, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com> wrote:

> On Thu, Jul 9, 2015 at 7:44 PM, David Rowley
> <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> > On 15 June 2015 at 12:05, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
> wrote:
> >>
> >>
> >> This basically allows an aggregate's state to be shared between other
> >> aggregate functions when both aggregate's transition functions (and a
> few
> >> other things) match
> >> There's quite a number of aggregates in our standard set which will
> >> benefit from this optimisation.
> >>
> >
> > After compiling the original patch with another compiler, I noticed a
> couple
> > of warnings.
> >
> > The attached fixes these.
>
> I did some performance tests on the patch. This patch shown good
> improvement for same column aggregates. With int or bigint datatype
> columns,
> this patch doesn't show any visible performance difference. But with
> numeric
> datatype it shows good improvement.
>

Thanks for testing this.

You should only see an improvement on aggregates listed here:

select aggfnoid::oid, aggfnoid || '(' || typname ||
')',aggtransfn,agginitval
from pg_aggregate ag
inner join pg_proc pr on aggfnoid = pr.oid
inner join pg_type tp on pr.proargtypes[0] = tp.oid
where ag.aggtransfn in (select aggtransfn
from pg_aggregate
group by aggtransfn
having count(*)>1)
and ag.agginitval is null
order by ag.aggtransfn;

Regards

David Rowley

--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Training & Services