Re: Improving avg performance for numeric

Hello

2013/3/19 Tom Lane <tgl@sss.pgh.pa.us>:
> I wrote:
>> [ looks at patch... ]  Oh, I see what's affecting the plan: you changed
>> the aggtranstypes to internal for a bunch of aggregates.  That's not
>> very good, because right now the planner takes that to mean that the
>> aggregate could eat a lot of space.  We don't want that to happen for
>> these aggregates, I think.
>
> 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.
>
> This was looking like 9.4 material already, but adding such a property
> would definitely put it over the top of what we could think about
> squeezing into 9.3, IMO.
>

Postgres is not a "in memory" OLAP database, but lot of companies use
it for OLAP queries due pg comfortable usage. This feature can be very
interesting for these users - and can introduce interesting speedup
with relative low price.

Regards

Pavel

>                         regards, tom lane