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.