Re: BUG #7916: memory leak with array_agg

eshkinkot(at)gmail(dot)com writes:
> Looks like array_agg have memory leak. In my example it eat 3 Gb RSS, but
> equal user defined aggregate
> created by myself use only 7Mb RSS.

AFAICT there's no actual leak here; array_agg is just optimized for
speed rather than space. It eats about 8K per hashtable entry.
While the planner knows that, it's got no good idea how many groups
will be produced by the query, so it underestimates the space needed
--- and the HashAggregate code is not currently capable of spilling
the hashtable to disk, so the table balloons well past the intended
work_mem limit.

Although no real fix for this is within easy reach, it strikes me
that we could possibly ameliorate things a bit by tweaking the
memory context size parameters used by accumArrayResult().
It would likely be reasonable to set the min size to 1K or so not 8K.
This would make things a trifle slower when the actual space requirement
exceeds 1K, but probably not by enough to notice.

BTW, I don't believe your assertion that the handmade aggregate does
this in 7MB. Even a very optimistic calculation puts the space needed
for 700000 10-element integer arrays at forty-some MB, and when I try
it I see more like 100MB consumed thanks to hashtable overhead.

regards, tom lane