From: | Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> |
---|---|
To: | Alexey Bashtanov <bashtanov(at)imap(dot)cc>, <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: OOM on EXPLAIN with lots of nodes |
Date: | 2015-01-13 13:47:02 |
Message-ID: | 54B521D6.8050600@vmware.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 01/13/2015 02:08 PM, Alexey Bashtanov wrote:
> 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.
Hmm, something like the attached? Seems reasonable...
- Heikki
Attachment | Content-Type | Size |
---|---|---|
limit-explain-memory-usage-1.patch | text/x-diff | 3.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2015-01-13 13:50:19 | Re: WITH CHECK and Column-Level Privileges |
Previous Message | Dean Rasheed | 2015-01-13 13:25:22 | Re: WITH CHECK and Column-Level Privileges |