From: | Alexey Bashtanov <bashtanov(at)imap(dot)cc> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | OOM on EXPLAIN with lots of nodes |
Date: | 2015-01-13 12:08:45 |
Message-ID: | 54B50ACD.400@imap.cc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hello!
I found that EXPLAIN command takes very much memory to execute when huge
unions are used.
For example the following sql
-- begin sql
create table t (a000 int, a001 int, ... a099 int);
explain select * from (
select a001 a from t
union all
select a001 a from t
union all
... (1000 times) ...
union all
select a001 a from t
) _ where a = 1;
-- end sql
took more than 1GB of memory to execute.
Namely converting of the plan to a human-readable form causes excessive
memory usage, not planning itself.
By varying the parameters and reading source code I determined that
memory usage linearly depends on (plan nodes count)*(overall columns
count), thus it quadratically depends on number of tables unionized.
To remove this excessive memory usage I propose
to run deparse_context_for_planstate+deparse_expression in a separate
memory context and free it after a plan node is generated.
Any reasons to treat this idea as bad?
BTW in this case explain execution is also quite long (I got tens of
seconds). But I have no immediate ideas how to improve it.
Regards,
Alexey Bashtanov
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2015-01-13 12:11:31 | Re: Escaping from blocked send() reprised. |
Previous Message | John Gorman | 2015-01-13 12:08:41 | Re: Parallel Seq Scan |