Allow window functions to adjust their frameOptions

WindowFuncs such as row_number() don't care if it's called with ROWS
UNBOUNDED PRECEDING AND CURRENT ROW or with RANGE UNBOUNDED PRECEDING AND
CURRENT ROW.  The latter is less efficient as the RANGE option requires
that the executor check for peer rows, so using the ROW option instead
would cause less overhead.  Because RANGE is part of the default frame
options for WindowClauses, it means WindowAgg is, by default, working much
harder than it needs to for window functions where the ROWS / RANGE option
has no effect on the window function's result.

On a test query from the discussion thread, a performance improvement of
344% was seen by using ROWS instead of RANGE.

Here we add a new support function node type to allow support functions to
be called for window functions so that the most optimal version of the
frame options can be set.  The planner has been adjusted so that the frame
options are changed only if all window functions sharing the same window
clause agree on what the optimized frame options are.

Here we give the ability for row_number(), rank(), dense_rank(),
percent_rank(), cume_dist() and ntile() to alter their WindowClause's
frameOptions.

Reviewed-by: Vik Fearing, Erwin Brandstetter, Zhihong Yu
Discussion: https://postgr.es/m/CAGHENJ7LBBszxS+SkWWFVnBmOT2oVsBhDMB1DFrgerCeYa_DyA@mail.gmail.com
Discussion: https://postgr.es/m/CAApHDvohAKEtTXxq7Pc-ic2dKT8oZfbRKeEJP64M0B6+S88z+A@mail.gmail.com

3 files changed, 57 insertions, 4 deletions

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 091ad94c5e..eba0146390 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202212201
+#define CATALOG_VERSION_NO	202212231
 
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 98d90d9338..9e9de75a43 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10186,32 +10186,41 @@
   proname => 'row_number', prosupport => 'window_row_number_support',
   prokind => 'w', proisstrict => 'f', prorettype => 'int8', proargtypes => '',
   prosrc => 'window_row_number' },
-{ oid => '6233', descr => 'planner support for row_number run condition',
+{ oid => '6233', descr => 'planner support for row_number',
   proname => 'window_row_number_support', prorettype => 'internal',
   proargtypes => 'internal', prosrc => 'window_row_number_support' },
 { oid => '3101', descr => 'integer rank with gaps',
   proname => 'rank', prosupport => 'window_rank_support', prokind => 'w',
   proisstrict => 'f', prorettype => 'int8', proargtypes => '',
   prosrc => 'window_rank' },
-{ oid => '6234', descr => 'planner support for rank run condition',
+{ oid => '6234', descr => 'planner support for rank',
   proname => 'window_rank_support', prorettype => 'internal',
   proargtypes => 'internal', prosrc => 'window_rank_support' },
 { oid => '3102', descr => 'integer rank without gaps',
   proname => 'dense_rank', prosupport => 'window_dense_rank_support',
   prokind => 'w', proisstrict => 'f', prorettype => 'int8', proargtypes => '',
   prosrc => 'window_dense_rank' },
-{ oid => '6235', descr => 'planner support for dense rank run condition',
+{ oid => '6235', descr => 'planner support for dense_rank',
   proname => 'window_dense_rank_support', prorettype => 'internal',
   proargtypes => 'internal', prosrc => 'window_dense_rank_support' },
 { oid => '3103', descr => 'fractional rank within partition',
   proname => 'percent_rank', prokind => 'w', proisstrict => 'f',
   prorettype => 'float8', proargtypes => '', prosrc => 'window_percent_rank' },
+{ oid => '9773', descr => 'planner support for percent_rank',
+  proname => 'window_percent_rank_support', prorettype => 'internal',
+  proargtypes => 'internal', prosrc => 'window_percent_rank_support' },
 { oid => '3104', descr => 'fractional row number within partition',
   proname => 'cume_dist', prokind => 'w', proisstrict => 'f',
   prorettype => 'float8', proargtypes => '', prosrc => 'window_cume_dist' },
+{ oid => '9774', descr => 'planner support for cume_dist',
+  proname => 'window_cume_dist_support', prorettype => 'internal',
+  proargtypes => 'internal', prosrc => 'window_cume_dist_support' },
 { oid => '3105', descr => 'split rows into N groups',
   proname => 'ntile', prokind => 'w', prorettype => 'int4',
   proargtypes => 'int4', prosrc => 'window_ntile' },
+{ oid => '9775', descr => 'planner support for ntile',
+  proname => 'window_ntile_support', prorettype => 'internal',
+  proargtypes => 'internal', prosrc => 'window_ntile_support' },
 { oid => '3106', descr => 'fetch the preceding row value',
   proname => 'lag', prokind => 'w', prorettype => 'anyelement',
   proargtypes => 'anyelement', prosrc => 'window_lag' },
diff --git a/src/include/nodes/supportnodes.h b/src/include/nodes/supportnodes.h
index 9fcbc39949..b446125b2b 100644
--- a/src/include/nodes/supportnodes.h
+++ b/src/include/nodes/supportnodes.h
@@ -299,4 +299,48 @@ typedef struct SupportRequestWFuncMonotonic
 	MonotonicFunction monotonic;
 } SupportRequestWFuncMonotonic;
 
+/*
+ * Some WindowFunc behavior might not be affected by certain variations in
+ * the WindowClause's frameOptions.  For example, row_number() is coded in
+ * such a way that the frame options don't change the returned row number.
+ * nodeWindowAgg.c will have less work to do if the ROWS option is used
+ * instead of the RANGE option as no check needs to be done for peer rows.
+ * Since RANGE is included in the default frame options, window functions
+ * such as row_number() might want to change that to ROW.
+ *
+ * Here we allow a WindowFunc's support function to determine which, if
+ * anything, can be changed about the WindowClause which the WindowFunc
+ * belongs to.  Currently only the frameOptions can be modified.  However,
+ * we may want to allow more optimizations in the future.
+ *
+ * The support function is responsible for ensuring the optimized version of
+ * the frameOptions doesn't affect the result of the window function.  The
+ * planner is responsible for only changing the frame options when all
+ * WindowFuncs using this particular WindowClause agree on what the optimized
+ * version of the frameOptions are.  If a particular WindowFunc being used
+ * does not have a support function then the planner will not make any changes
+ * to the WindowClause's frameOptions.
+ *
+ * 'window_func' and 'window_clause' are set by the planner before calling the
+ * support function so that the support function has these fields available.
+ * These may be required in order to determine which optimizations are
+ * possible.
+ *
+ * 'frameOptions' is set by the planner to WindowClause.frameOptions.  The
+ * support function must only adjust this if optimizations are possible for
+ * the given WindowFunc.
+ */
+typedef struct SupportRequestOptimizeWindowClause
+{
+	NodeTag		type;
+
+	/* Input fields: */
+	WindowFunc *window_func;	/* Pointer to the window function data */
+	struct WindowClause *window_clause; /* Pointer to the window clause data */
+
+	/* Input/Output fields: */
+	int			frameOptions;	/* New frameOptions, or left untouched if no
+								 * optimizations are possible. */
+} SupportRequestOptimizeWindowClause;
+
 #endif							/* SUPPORTNODES_H */