BUG #7916: memory leak with array_agg

[e***@gmail.com]

The following bug has been logged on the website:

Bug reference: 7916
Logged by: Sergey Burladyan
Email address: ***@gmail.com
PostgreSQL version: 9.2.3
Operating system: Debian GNU/Linux 7.0 (wheezy)
Description:

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.

PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
4.7.2-5) 4.7.2, 64-bit
set work_mem to '5MB';

Just query without aggregate:

$ ps axu | grep ^postgres | grep idle
postgres 8910 0.0 0.0 3337028 5156 ? Ss 02:21 0:00 postgres:
seb seb [local] idle

seb=> explain (analyze,verbose,buffers) select n, (random() * 10)::int from
generate_series(1, 700000) n, generate_series(1, 10);
QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.01..27510.01 rows=1000000 width=4) (actual
time=82.694..2827.922 rows=7000000 loops=1)
Output: n.n, ((random() * 10::double precision))::integer
Buffers: temp read=1198 written=1197
-> Function Scan on pg_catalog.generate_series n (cost=0.00..10.00
rows=1000 width=4) (actual time=82.653..190.192 rows=700000 loops=1)
Output: n.n
Function Call: generate_series(1, 700000)
Buffers: temp read=1198 written=1197
-> Function Scan on pg_catalog.generate_series (cost=0.00..10.00
rows=1000 width=0) (actual time=0.000..0.001 rows=10 loops=700000)
Output: generate_series.generate_series
Function Call: generate_series(1, 10)
Total runtime: 3318.348 ms

$ ps axu | grep ^postgres | grep idle
postgres 8910 5.0 0.0 3337176 6376 ? Ss 02:21 0:03 postgres:
seb seb [local] idle

RSS 6376 after it

Ok, now problematic query with array_agg:

seb=> explain (analyze,verbose,buffers) select n, array_agg((random() *
10)::int) from generate_series(1, 700000) n, generate_series(1, 10) group by
1;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=32510.01..32512.51 rows=200 width=4) (actual
time=7276.445..7853.732 rows=700000 loops=1)
Output: n.n, array_agg(((random() * 10::double precision))::integer)
Buffers: temp read=1198 written=1197
-> Nested Loop (cost=0.01..20010.01 rows=1000000 width=4) (actual
time=83.624..2318.832 rows=7000000 loops=1)
Output: n.n
Buffers: temp read=1198 written=1197
-> Function Scan on pg_catalog.generate_series n
(cost=0.00..10.00 rows=1000 width=4) (actual time=83.606..217.716
rows=700000 loops=1)
Output: n.n
Function Call: generate_series(1, 700000)
Buffers: temp read=1198 written=1197
-> Function Scan on pg_catalog.generate_series (cost=0.00..10.00
rows=1000 width=0) (actual time=0.000..0.001 rows=10 loops=700000)
Output: generate_series.generate_series
Function Call: generate_series(1, 10)
Total runtime: 8110.143 ms

$ ps axu | grep ^postgres | grep idle
postgres 8910 6.8 21.0 9160796 3474628 ? Ss 02:21 0:11 postgres:
seb seb [local] idle

RSS _3474628_ = 3Gb allocated after it

reconnect and use user defined aggregate:

create aggregate myagg (int) ( sfunc = array_append, stype = int[]);
set work_mem to '5MB';

$ ps axu | grep ^postgres | grep idle
postgres 8962 0.0 0.0 3337036 5728 ? Ss 02:25 0:00 postgres:
seb seb [local] idle

seb=> explain (analyze,verbose,buffers) select n, myagg((random() *
10)::int) from generate_series(1, 700000) n, generate_series(1, 10) group by
1;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=32510.01..32512.01 rows=200 width=4) (actual
time=8589.773..8917.671 rows=700000 loops=1)
Output: n.n, myagg(((random() * 10::double precision))::integer)
Buffers: temp read=1198 written=1197
-> Nested Loop (cost=0.01..20010.01 rows=1000000 width=4) (actual
time=83.273..2364.212 rows=7000000 loops=1)
Output: n.n
Buffers: temp read=1198 written=1197
-> Function Scan on pg_catalog.generate_series n
(cost=0.00..10.00 rows=1000 width=4) (actual time=83.247..196.978
rows=700000 loops=1)
Output: n.n
Function Call: generate_series(1, 700000)
Buffers: temp read=1198 written=1197
-> Function Scan on pg_catalog.generate_series (cost=0.00..10.00
rows=1000 width=0) (actual time=0.000..0.001 rows=10 loops=700000)
Output: generate_series.generate_series
Function Call: generate_series(1, 10)
Total runtime: 8978.695 ms

$ ps axu | grep ^postgres | grep idle
postgres 8962 12.9 0.0 3337832 7928 ? Ss 02:25 0:08 postgres:
seb seb [local] idle

RSS 7928 = 7Mb after it

Current git master (542eeba26992305d872be699158cb3ab1c2be6e6) also have this
problem.

[Tom Lane]

Post by e***@gmail.com
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

[Sergey Burladyan]

Post by Tom Lane
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.

Aha, I found this "8K per hashtable entry" with ltrace and now understand
what is going on here:
=== array_agg malloc calls count === === myagg malloc calls count===
3 malloc(1024) 3 malloc(1024)
3 malloc(1048576) 3 malloc(1048576)
3 malloc(131072) 3 malloc(131072)
1 malloc(16056) 4 malloc(16384)
5 malloc(16384) 2 malloc(16440)
2 malloc(16440) 1 malloc(2048)
1 malloc(2048) 3 malloc(2097152)
3 malloc(2097152) 3 malloc(262144)
3 malloc(262144) 3 malloc(32768)
3 malloc(32768) 1 malloc(32824)
1 malloc(32824) 1 malloc(4096)
1 malloc(4096) 3 malloc(4194304)
3 malloc(4194304) 3 malloc(524288)
3 malloc(524288) 3 malloc(65536)
3 malloc(65536) 12 malloc(8192)
724151 malloc(8192) 1 malloc(8296)
1 malloc(8296) 29 malloc(8360)
44 malloc(8360) 16 malloc(8388608)
8 malloc(8388608)

Thank you for answer Tom!

Post by Tom Lane
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.

Looks good.

Post by Tom Lane
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.

Yes you are right, Tom. My mistake.