Re: array_agg() on a set larger than some arbitrary(?) limit causes runaway memory usage and eventually memory exhaustion

Hi,

Op zondag 20 oktober 2013 12:57:43 schreef Tomas Vondra:
> Attached is a quick patch removing the local memory context and using
> aggcontext instead. I've also tuned down the preallocation. It's against
> 9.2 stable, so it should apply fine against your 9.2.4. But be careful,
> it's not really tested.

I applied the patch and ran my queries again with the following results:

> with g as (select * from f limit 1e5)
> select array_agg(id), min(value)

used to be:
Time: 361,242 ms
now:
Time: 363,767 ms

> with g as (select * from f limit 1e6)
> select array_agg(id), min(value)

used to be:
Time: 3310,347 ms
now:
Time: 2134,688 ms

> with g as (select * from f limit 1e7)
> select array_agg(id), min(value)

used to be:
Time: <none, fails>
now:
Time: 23234,045 ms

The last query now uses up ~3.5GB of memory.

and as for the comcat() / string_agg() comparison:

> with g as (select * from f limit 1e7)
> select comcat(id::text), min(value)

used to be / still is:
Time: ~18.5 seconds
Mem: ~6,5 GB

> with g as (select * from f limit 1e7)
> select string_agg(id::text, ', '), min(value)

used to be:
Time: ~28.5 seconds
Mem: ~16 GB
now:
Time: ~28.5 seconds
Mem: ~12 GB

So, the patch seems to have the desired effect ;)

It looks like this didn't go into git yet. Will it be in v9.2.6/v9.3.1? Since
it's still a 'rough' patch, I reversed it on our development server for now.

--

Best,

Frank.