OOM on EXPLAIN with lots of nodes

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

Responses

Browse pgsql-hackers by date

  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