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