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
