OOM on EXPLAIN with lots of nodes
От | Alexey Bashtanov |
---|---|
Тема | OOM on EXPLAIN with lots of nodes |
Дата | |
Msg-id | 54B50ACD.400@imap.cc обсуждение исходный текст |
Ответы |
Re: OOM on EXPLAIN with lots of nodes
|
Список | 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
В списке pgsql-hackers по дате отправления: