Hi, On 01/19/2016 05:00 AM, David Rowley wrote:
On 19 January 2016 at 06:03, Pavel Stehule <pavel.steh...@gmail.com <mailto:pavel.steh...@gmail.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 -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers