Add an 'enable_material' GUC.

The logic for determining whether to materialize has been significantly
overhauled for 9.0.  In case there should be any doubt about whether
materialization is a win in any particular case, this should provide a
convenient way of seeing what happens without it; but even with enable_material
turned off, we still materialize in cases where it is required for
correctness.

Thanks to Tom Lane for the review.

3 files changed, 26 insertions, 11 deletions

diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 355db7f684..1f8dfa8547 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -59,7 +59,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.216 2010/02/26 02:00:44 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.217 2010/04/19 00:55:25 rhaas Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -114,6 +114,7 @@ bool		enable_tidscan = true;
 bool		enable_sort = true;
 bool		enable_hashagg = true;
 bool		enable_nestloop = true;
+bool		enable_material = true;
 bool		enable_mergejoin = true;
 bool		enable_hashjoin = true;
 
@@ -1852,8 +1853,11 @@ cost_mergejoin(MergePath *path, PlannerInfo *root, SpecialJoinInfo *sjinfo)
 	mat_inner_cost = inner_run_cost +
 		cpu_operator_cost * inner_path_rows * rescanratio;
 
-	/* Prefer materializing if it looks cheaper */
-	if (mat_inner_cost < bare_inner_cost)
+	/*
+	 * Prefer materializing if it looks cheaper, unless the user has asked
+	 * to suppress materialization.
+	 */
+	if (enable_material && mat_inner_cost < bare_inner_cost)
 		path->materialize_inner = true;
 
 	/*
@@ -1867,6 +1871,10 @@ cost_mergejoin(MergePath *path, PlannerInfo *root, SpecialJoinInfo *sjinfo)
 	 * merge joins can *preserve* the order of their inputs, so they can be
 	 * selected as the input of a mergejoin, and they don't support
 	 * mark/restore at present.
+	 *
+	 * We don't test the value of enable_material here, because materialization
+	 * is required for correctness in this case, and turning it off does not
+	 * entitle us to deliver an invalid plan.
 	 */
 	else if (innersortkeys == NIL &&
 			 !ExecSupportsMarkRestore(inner_path->pathtype))
@@ -1878,8 +1886,11 @@ cost_mergejoin(MergePath *path, PlannerInfo *root, SpecialJoinInfo *sjinfo)
 	 * pass can be done on-the-fly if it doesn't have to support mark/restore.
 	 * We don't try to adjust the cost estimates for this consideration,
 	 * though.
+	 *
+	 * Since materialization is a performance optimization in this case, rather
+	 * than necessary for correctness, we skip it if enable_material is off.
 	 */
-	else if (innersortkeys != NIL &&
+	else if (enable_material && innersortkeys != NIL &&
 			 relation_byte_size(inner_path_rows, inner_path->parent->width) >
 			 (work_mem * 1024L))
 		path->materialize_inner = true;
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 3247c73c01..1fbb2a4fe9 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/optimizer/path/joinpath.c,v 1.132 2010/03/28 22:59:32 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/optimizer/path/joinpath.c,v 1.133 2010/04/19 00:55:25 rhaas Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -437,10 +437,12 @@ match_unsorted_outer(PlannerInfo *root,
 	else if (nestjoinOK)
 	{
 		/*
-		 * Consider materializing the cheapest inner path, unless it is one
-		 * that materializes its output anyway.
+		 * Consider materializing the cheapest inner path, unless
+		 * enable_material is off or the path in question materializes its
+		 * output anyway.
 		 */
-		if (!ExecMaterializesOutput(inner_cheapest_total->pathtype))
+		if (enable_material &&
+			!ExecMaterializesOutput(inner_cheapest_total->pathtype))
 			matpath = (Path *)
 				create_material_path(innerrel, inner_cheapest_total);
 
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 16dbc3ad44..cf503d5113 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/optimizer/plan/subselect.c,v 1.161 2010/02/26 02:00:46 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/optimizer/plan/subselect.c,v 1.162 2010/04/19 00:55:25 rhaas Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -578,9 +578,11 @@ build_subplan(PlannerInfo *root, Plan *plan, List *rtable, List *rowmarks,
 		 * is pointless for a direct-correlated subplan, since we'd have to
 		 * recompute its results each time anyway.	For uncorrelated/undirect
 		 * correlated subplans, we add Material unless the subplan's top plan
-		 * node would materialize its output anyway.
+		 * node would materialize its output anyway.  Also, if enable_material
+		 * is false, then the user does not want us to materialize anything
+		 * unnecessarily, so we don't.
 		 */
-		else if (splan->parParam == NIL &&
+		else if (splan->parParam == NIL && enable_material &&
 				 !ExecMaterializesOutput(nodeTag(plan)))
 			plan = materialize_finished_plan(plan);