Allow the planner's estimate of the fraction of a cursor's rows that will be

retrieved to be controlled through a GUC variable.

Robert Hell

4 files changed, 37 insertions, 7 deletions

diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 74e7258bba2..a6ad9dbdcec 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.232 2008/04/17 21:22:14 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.233 2008/05/02 21:26:09 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -42,6 +42,9 @@
 #include "utils/syscache.h"
 
 
+/* GUC parameter */
+double cursor_tuple_fraction = DEFAULT_CURSOR_TUPLE_FRACTION;
+
 /* Hook for plugins to get control in planner() */
 planner_hook_type planner_hook = NULL;
 
@@ -142,11 +145,23 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
 	{
 		/*
 		 * We have no real idea how many tuples the user will ultimately FETCH
-		 * from a cursor, but it seems a good bet that he doesn't want 'em
-		 * all.  Optimize for 10% retrieval (you gotta better number?  Should
-		 * this be a SETtable parameter?)
+		 * from a cursor, but it is often the case that he doesn't want 'em
+		 * all, or would prefer a fast-start plan anyway so that he can
+		 * process some of the tuples sooner.  Use a GUC parameter to decide
+		 * what fraction to optimize for.
+		 */
+		tuple_fraction = cursor_tuple_fraction;
+
+		/*
+		 * We document cursor_tuple_fraction as simply being a fraction,
+		 * which means the edge cases 0 and 1 have to be treated specially
+		 * here.  We convert 1 to 0 ("all the tuples") and 0 to a very small
+		 * fraction.
 		 */
-		tuple_fraction = 0.10;
+		if (tuple_fraction >= 1.0)
+			tuple_fraction = 0.0;
+		else if (tuple_fraction <= 0.0)
+			tuple_fraction = 1e-10;
 	}
 	else
 	{
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 58b1a469acf..993cf971a93 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -10,7 +10,7 @@
  * Written by Peter Eisentraut <peter_e@gmx.net>.
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.450 2008/05/01 19:55:40 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.451 2008/05/02 21:26:10 tgl Exp $
  *
  *--------------------------------------------------------------------
  */
@@ -1892,6 +1892,16 @@ static struct config_real ConfigureNamesReal[] =
 	},
 
 	{
+		{"cursor_tuple_fraction", PGC_USERSET, QUERY_TUNING_OTHER,
+			gettext_noop("Sets the planner's estimate of the fraction of "
+						 "a cursor's rows that will be retrieved."),
+			NULL
+		},
+		&cursor_tuple_fraction,
+		DEFAULT_CURSOR_TUPLE_FRACTION, 0.0, 1.0, NULL, NULL
+	},
+
+	{
 		{"geqo_selection_bias", PGC_USERSET, QUERY_TUNING_GEQO,
 			gettext_noop("GEQO: selective pressure within the population."),
 			NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 9fb567bd62b..ce784530832 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -219,6 +219,7 @@
 
 #default_statistics_target = 10		# range 1-1000
 #constraint_exclusion = off
+#cursor_tuple_fraction = 0.1		# range 0.0-1.0
 #from_collapse_limit = 8
 #join_collapse_limit = 8		# 1 disables collapsing of explicit 
 					# JOIN clauses
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index 74ba5131cc6..0b46a173706 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/optimizer/planmain.h,v 1.107 2008/04/17 21:22:14 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/optimizer/planmain.h,v 1.108 2008/05/02 21:26:10 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -17,6 +17,10 @@
 #include "nodes/plannodes.h"
 #include "nodes/relation.h"
 
+/* GUC parameters */
+#define DEFAULT_CURSOR_TUPLE_FRACTION 0.1
+extern double cursor_tuple_fraction;
+
 /*
  * prototypes for plan/planmain.c
  */