Jiri Sadek <jiri.sadek@gmail.com> writes:
> On 27.3.2017 15:46, Tom Lane wrote:
>> I think you'll find that 9.6.2 makes this significantly better.
>> https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=48a6592da
> Actually we did all the testing on 9.6.2-1.pgdg16.04+1 from
> http://apt.postgresql.org/pub/repos/apt/ repository.
Hm, well, that commit adjusted the default SSPACE assumption for a
user-defined aggregate using array_append as transition function
to be 1KB, which should be enough to keep you out of trouble in
most cases. If you're actually accumulating more than that per
group, you might need to specify a larger SSPACE parameter for your
custom aggregate.
The core problem with your toy example is that the planner has no
understanding that "GROUP BY n % 100000" is going to lead to 100000
distinct groups, so it uses hash aggregation even though it knows
that the per-group space consumption will be significant. Hopefully
in your real case it's estimating something closer to the true number
of groups. But one way or another, if you want grouped array_accum
queries not to consume lots of memory, you need to ensure you don't
get a hashed-aggregation plan. Ideally that would happen because the
planner has an accurate idea of the number of groups and the space
needed per group. If you can't get the number-of-groups estimate
to be more or less in line with reality, and you don't want to bump
the SSPACE parameter up enough to compensate, you could resort to
turning off "enable_hashagg" for the query.
regards, tom lane