"union all" query consumes all memory
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,50000) 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
Pavel Stehule <pavel.stehule@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,50000) 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