Re: Combining Aggregates

Hi,

On 01/19/2016 05:00 AM, David Rowley wrote:
> On 19 January 2016 at 06:03, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com
> <mailto:pavel(dot)stehule(at)gmail(dot)com>> wrote:
>
...
>
> It is strange, why hashaggregate is too slow?
>
>
> Good question. I looked at this and found my VM was swapping like crazy.
> Upon investigation it appears that's because, since the patch creates a
> memory context per aggregated group, and in this case I've got 1 million
> of them, it means we create 1 million context, which are
> ALLOCSET_SMALL_INITSIZE (1KB) in size, which means about 1GB of memory,
> which is more than my VM likes.

Really? Where do we create the memory context? IIRC string_agg uses the
aggcontext directly, and indeed that's what I see in string_agg_transfn
and makeStringAggState.

Perhaps you mean that initStringInfo() allocates 1kB buffers by default?

>
> set work_mem = '130MB' does coax the planner into a GroupAggregate plan,
> which is faster, but due to the the hash agg executor code not giving
> any regard to work_mem. If I set work_mem to 140MB (which is more
> realistic for this VM), it does cause the same swapping problems to
> occur. Probably setting aggtransspace for this aggregate to 1024 would
> help the costing problem, but it would also cause hashagg to be a less
> chosen option during planning.

I'm not quite sure I understand - the current code ends up using 8192
for the transition space (per count_agg_clauses_walker). Are you
suggesting lowering the value, despite the danger of OOM issues?

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services