The following bug has been logged on the website: Bug reference: 7916 Logged by: Sergey Burladyan Email address: eshkin...@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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs