Lists: | pgsql-hackers |
---|
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | "union all" query consumes all memory |
Date: | 2013-11-14 21:43:54 |
Message-ID: | CAFj8pRDsvwsg4DZRAvE=5jouBoF11q5vU1OW9dSFxh1AVgpq6Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: "union all" query consumes all memory |
Date: | 2013-11-14 22:40:43 |
Message-ID: | 32507.1384468843@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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