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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
