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
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 8a0be5d..4509aab 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -27,6 +27,7 @@
#include "utils/builtins.h"
#include "utils/json.h"
#include "utils/lsyscache.h"
+#include "utils/memutils.h"
#include "utils/rel.h"
#include "utils/ruleutils.h"
#include "utils/snapmgr.h"
@@ -263,6 +264,15 @@ ExplainInitState(ExplainState *es)
es->costs = true;
/* Prepare output buffer. */
es->str = makeStringInfo();
+
+ /*
+ * Create a temporary memory context, used for deparsing expressions.
+ */
+ es->tmpCxt = AllocSetContextCreate(CurrentMemoryContext,
+ "Temporary EXPLAIN context",
+ ALLOCSET_DEFAULT_MINSIZE,
+ ALLOCSET_DEFAULT_INITSIZE,
+ ALLOCSET_DEFAULT_MAXSIZE);
}
/*
@@ -1664,6 +1674,7 @@ show_plan_tlist(PlanState *planstate, List *ancestors, ExplainState *es)
List *result = NIL;
bool useprefix;
ListCell *lc;
+ MemoryContext oldcxt;
/* No work if empty tlist (this occurs eg in bitmap indexscans) */
if (plan->targetlist == NIL)
@@ -1677,6 +1688,9 @@ show_plan_tlist(PlanState *planstate, List *ancestors, ExplainState *es)
if (IsA(plan, RecursiveUnion))
return;
+ /* Switch to a temporary memory context to limit memory usage */
+ oldcxt = MemoryContextSwitchTo(es->tmpCxt);
+
/* Set up deparsing context */
context = deparse_context_for_planstate((Node *) planstate,
ancestors,
@@ -1696,6 +1710,10 @@ show_plan_tlist(PlanState *planstate, List *ancestors, ExplainState *es)
/* Print results */
ExplainPropertyList("Output", result, es);
+
+ /* Clean up */
+ MemoryContextSwitchTo(oldcxt);
+ MemoryContextReset(es->tmpCxt);
}
/*
@@ -1708,6 +1726,10 @@ show_expression(Node *node, const char *qlabel,
{
List *context;
char *exprstr;
+ MemoryContext oldcxt;
+
+ /* Switch to a temporary memory context to limit memory usage */
+ oldcxt = MemoryContextSwitchTo(es->tmpCxt);
/* Set up deparsing context */
context = deparse_context_for_planstate((Node *) planstate,
@@ -1720,6 +1742,10 @@ show_expression(Node *node, const char *qlabel,
/* And add to es->str */
ExplainPropertyText(qlabel, exprstr, es);
+
+ /* Clean up */
+ MemoryContextSwitchTo(oldcxt);
+ MemoryContextReset(es->tmpCxt);
}
/*
@@ -1850,10 +1876,14 @@ show_sort_group_keys(PlanState *planstate, const char *qlabel,
bool useprefix;
int keyno;
char *exprstr;
+ MemoryContext oldcxt;
if (nkeys <= 0)
return;
+ /* Switch to a temporary memory context to limit memory usage */
+ oldcxt = MemoryContextSwitchTo(es->tmpCxt);
+
/* Set up deparsing context */
context = deparse_context_for_planstate((Node *) planstate,
ancestors,
@@ -1877,6 +1907,10 @@ show_sort_group_keys(PlanState *planstate, const char *qlabel,
}
ExplainPropertyList(qlabel, result, es);
+
+ /* Clean up */
+ MemoryContextSwitchTo(oldcxt);
+ MemoryContextReset(es->tmpCxt);
}
/*
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index 6e26950..0a819e9 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -41,6 +41,8 @@ typedef struct ExplainState
List *rtable_names; /* alias names for RTEs */
int indent; /* current indentation level */
List *grouping_stack; /* format-specific grouping state */
+
+ MemoryContext tmpCxt; /* working context for deparsing expressions */
} ExplainState;
/* Hook for plugins to get control in ExplainOneQuery() */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
