From: | eshkinkot(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #7916: memory leak with array_agg |
Date: | 2013-03-04 22:51:46 |
Message-ID: | E1UCeEU-0004hY-Pq@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 7916
Logged by: Sergey Burladyan
Email address: eshkinkot(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Euler Taveira | 2013-03-05 04:30:03 | Re: BUG #7913: TO_CHAR Function & Turkish collate |
Previous Message | Tom Lane | 2013-03-04 20:37:14 | Re: BUG #7914: pg_dump aborts occasionally |