Re: OOM on EXPLAIN with lots of nodes

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