Jiri Sadek <jiri.sadek@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
