[HACKERS] union all query consumes all memory

[Pavel Stehule]

Hello,

one my customer reported a out of memory issue. After investigation he
found a main problem in large query that uses a lot of union all queries.
He wrote a self test:

do $$

declare i integer;  str text='';

begin

for i in 1..1000 loop

str := str || 'union all select i,i,i from generate_series(1,5) g(i) ';

end loop;

execute 'select 1,2,3 ' || str;

end;
$$

is it expected behave?

Tested on PostgreSQL 9.1, 9.2, 9.3

It looks so all generated data are saved in memory only.

Regards

Pavel Stehule

Re: [HACKERS] union all query consumes all memory

[Tom Lane]

Pavel Stehule pavel.steh...@gmail.com writes:
 one my customer reported a out of memory issue. After investigation he
 found a main problem in large query that uses a lot of union all queries.
 He wrote a self test:

 do $$

 declare i integer;  str text='';

 begin

 for i in 1..1000 loop

 str := str || 'union all select i,i,i from generate_series(1,5) g(i) ';

 end loop;

 execute 'select 1,2,3 ' || str;

 end;
 $$

 is it expected behave?

Well, that query is entitled to use 1000 * work_mem for the
generate_series output tuplestores.  What's your work_mem setting?
Perhaps more to the point, what was your customer doing?

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers