better_windowclause_sorting_dgr-v8.patch

diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index b9da588114..e13c8f1914 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1015,7 +1015,6 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
 								  orderbyclauses,
 								  orderbyclausecols,
 								  useful_pathkeys,
-								  index_pathkeys,
 								  index_is_ordered ?
 								  ForwardScanDirection :
 								  NoMovementScanDirection,
@@ -1038,7 +1037,6 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
 									  orderbyclauses,
 									  orderbyclausecols,
 									  useful_pathkeys,
-									  index_pathkeys,
 									  index_is_ordered ?
 									  ForwardScanDirection :
 									  NoMovementScanDirection,
@@ -1074,7 +1072,6 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
 									  NIL,
 									  NIL,
 									  useful_pathkeys,
-									  index_pathkeys,
 									  BackwardScanDirection,
 									  index_only_scan,
 									  outer_relids,
@@ -1092,7 +1089,6 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
 										  NIL,
 										  NIL,
 										  useful_pathkeys,
-										  index_pathkeys,
 										  BackwardScanDirection,
 										  index_only_scan,
 										  outer_relids,
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 28be495370..609df93dc9 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -1968,21 +1968,3 @@ has_useful_pathkeys(PlannerInfo *root, RelOptInfo *rel)
 		return true;			/* might be able to use them for ordering */
 	return false;				/* definitely useless */
 }
-
-/*
- * Returns True if key1 subset is key2 i.e.
- * atleast one key from keys1 is present on key2
- */
-bool
-is_pathkey_subset(List	*keys1, List* keys2)
-{
-	ListCell* l;
-	foreach(l, keys1)
-	{
-		PathKey    *pathkey1 = (PathKey *) lfirst(l);
-		if(pathkey_is_redundant(pathkey1, keys2)){
-			return true;
-		}
-	}
-	return false;
-}
\ No newline at end of file
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index ee883048e3..044fb24666 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -4421,14 +4421,8 @@ create_one_window_path(PlannerInfo *root,
 					   List *activeWindows)
 {
 	PathTarget *window_target;
-	Query	   *parse = root->parse;
 	ListCell   *l;
 	List	   *topqual = NIL;
-	List	   *window_pathkeys;
-	List	   *orderby_pathkeys = NIL;
-	int			sort_pushdown_idx = -1;
-	int			presorted_keys;
-	bool		is_sorted;
 
 	/*
 	 * Since each window clause could require a different sort order, we stack
@@ -4447,99 +4441,17 @@ create_one_window_path(PlannerInfo *root,
 	 */
 	window_target = input_target;
 
-	/*
-	 * Here we attempt to minimize the number of sorts which must be performed
-	 * for queries with an ORDER BY clause.
-	 *
-	 * It's possible due to select_active_windows() putting the WindowClauses
-	 * with the lowest tleSortGroupRef last in the activeWindows list that the
-	 * final WindowClause has a subset of the sort requirements that the
-	 * query's ORDER BY clause has.  Below we try to detect this case and if
-	 * we find this is true, we consider adjusting the sort that's done for
-	 * WindowAggs and include the additional clauses so that no additional
-	 * sorting is required for the query's ORDER BY clause.
-	 *
-	 * We don't try this optimization for the following cases:
-	 *
-	 * 1.	If the query has a DISTINCT clause.  We needn't waste any
-	 *		additional effort for the more strict sort order as if DISTINCT
-	 *		is done via Hash Aggregate then that's going to undo this work.
-	 *
-	 * 2.	If the query has a LIMIT clause.  The top-level sort will be
-	 *		able to use a top-n sort which might be more efficient than
-	 *		sorting	by the additional columns.  If the LIMIT does not reduce
-	 *		the	number of rows significantly then this might not be true, but
-	 *		we don't try to consider that here.
-	 *
-	 * 3.	If the top-level WindowClause has a runCondition then this can
-	 *		filter out tuples and make the final sort cheaper.  If we pushed
-	 *		the sort down below the WindowAgg then we'd need to sort all rows
-	 *		including ones that the runCondition might filter.  This may waste
-	 *		effort so we just don't try to push down the sort for this case.
-	 *
-	 * 4.	If the ORDER BY contains any expressions containing WindowFuncs
-	 *		then we can't push down the sort as these obviously must be
-	 *		evaluated before they can be sorted.
-	 */
-	if (parse->sortClause != NIL &&
-		parse->limitCount == NULL &&
-		linitial_node(WindowClause, activeWindows)->runCondition == NIL &&
-		!contain_window_function((Node *) get_sortgrouplist_exprs(parse->sortClause,
-								 root->processed_tlist)))
-	{
-		orderby_pathkeys = make_pathkeys_for_sortclauses(root,
-														 parse->sortClause,
-														 root->processed_tlist);
-
-		/*
-		 * Loop backwards over the WindowClauses looking for the first
-		 * WindowClause which has pathkeys not contained in the
-		 * orderby_pathkeys.  What we're looking for here is the lowest level
-		 * that we push the additional sort requirements down into.  If we
-		 * fail here then sort_pushdown_idx will remain at -1.
-		 */
-		foreach_reverse(l, activeWindows)
-		{
-			WindowClause *wc = lfirst_node(WindowClause, l);
-
-			window_pathkeys = make_pathkeys_for_window(root,
-														wc,
-														root->processed_tlist);
-
-			if (pathkeys_contained_in(window_pathkeys, orderby_pathkeys))
-				sort_pushdown_idx = foreach_current_index(l);
-			else
-				break;
-		}
-	}
-
 	foreach(l, activeWindows)
 	{
 		WindowClause *wc = lfirst_node(WindowClause, l);
+		List	   *window_pathkeys;
+		int			presorted_keys;
+		bool		is_sorted;
 		bool		topwindow;
 
 		window_pathkeys = make_pathkeys_for_window(root,
-													wc,
-													root->processed_tlist);
-
-		/*
-		 * When we get to the sort_pushdown_idx WindowClause, if the path is
-		 * not already correctly sorted, then just use the pathkeys for the
-		 * query's ORDER BY clause instead of the WindowClause's pathkeys.
-		 * When the path is already correctly sorted there's no point in
-		 * adjusting the pathkeys as this just moves the sort down without
-		 * actually reducing the number of sorts which are required in the
-		 * plan overall.  sort_pushdown_idx will be -1 and never match when
-		 * this optimization was disabled above.
-		 */
-		if (foreach_current_index(l) == sort_pushdown_idx &&
-			!pathkeys_contained_in(window_pathkeys, path->pathkeys))
-		{
-			/* check to make sure sort_pushdown_idx was set correctly */
-			Assert(pathkeys_contained_in(window_pathkeys, orderby_pathkeys));
-
-			window_pathkeys = orderby_pathkeys;
-		}
+												   wc,
+												   root->processed_tlist);
 
 		is_sorted = pathkeys_count_contained_in(window_pathkeys,
 												path->pathkeys,
@@ -4933,43 +4845,9 @@ create_final_distinct_paths(PlannerInfo *root, RelOptInfo *input_rel,
 			bool		is_sorted;
 			int			presorted_keys;
 
-
-			/*
-			 * Consider any any sorting which can be reutilized for distinct
-			 * even if ordering differs.
-			 */
-			if (IsA(lfirst(lc), IndexPath))
-			{
-				/*
-				 * Find if index pathkeys are subset of needed pathkeys
-				 * if so then prepend these keys so that sorting is not needed
-				 */
-				IndexPath *idx_path  = (IndexPath*) lfirst(lc);
-				if (is_pathkey_subset(needed_pathkeys, idx_path->indexpathkeys))
-				{
-
-					needed_pathkeys = list_concat_unique(list_copy(idx_path->indexpathkeys), needed_pathkeys);
-				}
-				is_sorted = pathkeys_count_contained_in(needed_pathkeys,
-													idx_path->indexpathkeys,
-													&presorted_keys);
-			}
-			else
-			{
-				/*
-				 * If needed pathkeys are subset of query_pathkeys,
-				 * if so, lower nodes will have sorted some columns which
-				 * we can reuse.
-				 */
-				if (is_pathkey_subset(needed_pathkeys, root->query_pathkeys))
-				{
-					needed_pathkeys = list_concat_unique(list_copy(root->query_pathkeys), needed_pathkeys);
-				}
-				is_sorted = pathkeys_count_contained_in(needed_pathkeys,
+			is_sorted = pathkeys_count_contained_in(needed_pathkeys,
 													input_path->pathkeys,
 													&presorted_keys);
-			}
-
 
 			if (is_sorted)
 				sorted_path = input_path;
@@ -6626,7 +6504,7 @@ plan_cluster_use_sort(Oid tableOid, Oid indexOid)
 
 	/* Estimate the cost of index scan */
 	indexScanPath = create_index_path(root, indexInfo,
-									  NIL, NIL, NIL, NIL, NIL,
+									  NIL, NIL, NIL, NIL,
 									  ForwardScanDirection, false,
 									  NULL, 1.0, false);
 
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index c666be4f4f..4478036bb6 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1000,7 +1000,6 @@ create_index_path(PlannerInfo *root,
 				  List *indexorderbys,
 				  List *indexorderbycols,
 				  List *pathkeys,
-				  List *indexpathkeys,
 				  ScanDirection indexscandir,
 				  bool indexonly,
 				  Relids required_outer,
@@ -1024,9 +1023,7 @@ create_index_path(PlannerInfo *root,
 	pathnode->indexclauses = indexclauses;
 	pathnode->indexorderbys = indexorderbys;
 	pathnode->indexorderbycols = indexorderbycols;
-
 	pathnode->indexscandir = indexscandir;
-	pathnode->indexpathkeys = indexpathkeys;
 
 	cost_index(pathnode, root, loop_count, partial_path);
 
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index a2abb40356..c20b7298a3 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1601,7 +1601,6 @@ typedef struct IndexPath
 	List	   *indexclauses;
 	List	   *indexorderbys;
 	List	   *indexorderbycols;
-	List	   *indexpathkeys;
 	ScanDirection indexscandir;
 	Cost		indextotalcost;
 	Selectivity indexselectivity;
diff --git a/src/include/nodes/pg_list.h b/src/include/nodes/pg_list.h
index 0de9d73dac..529a382d28 100644
--- a/src/include/nodes/pg_list.h
+++ b/src/include/nodes/pg_list.h
@@ -378,30 +378,14 @@ lnext(const List *l, const ListCell *c)
 		 (cell = NULL, false); \
 		 cell##__state.i++)
 
-/*
- * foreach_reverse -
- *	  a convenience macro for looping through a list in reverse
- *
- * This is equivalent to foreach, only it loops over the list starting with
- * the last element and ends on the first element.
- */
-#define foreach_reverse(cell, lst)	\
-	for (ForEachState cell##__state = {(lst), cell##__state.l->length - 1}; \
-		 (cell##__state.l != NIL && \
-		  cell##__state.i >= 0) ? \
-		 (cell = &cell##__state.l->elements[cell##__state.i], true) : \
-		 (cell = NULL, false); \
-		 cell##__state.i--)
-
 /*
  * foreach_delete_current -
  *	  delete the current list element from the List associated with a
- *	  surrounding foreach() or foreach_reverse() loop, returning the new List
-*	  pointer.
+ *	  surrounding foreach() loop, returning the new List pointer.
  *
  * This is equivalent to list_delete_cell(), but it also adjusts the foreach
- * or foreach_reverse loop's state so that no list elements will be missed.
- * Do not delete elements from an active foreach loop's list in any other way!
+ * loop's state so that no list elements will be missed.  Do not delete
+ * elements from an active foreach loop's list in any other way!
  */
 #define foreach_delete_current(lst, cell)	\
 	(cell##__state.i--, \
@@ -409,9 +393,8 @@ lnext(const List *l, const ListCell *c)
 
 /*
  * foreach_current_index -
- *	  get the zero-based index within the list of the current element in the
- *	  surrounding foreach() or foreach_reverse() loop; pass the name of the
- *	  "ListCell *" iterator variable.
+ *	  get the zero-based list index of a surrounding foreach() loop's
+ *	  current element; pass the name of the "ListCell *" iterator variable.
  *
  * Beware of using this after foreach_delete_current(); the value will be
  * out of sync for the rest of the current loop iteration.  Anyway, since
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 1833218efc..02305ef902 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -44,7 +44,6 @@ extern IndexPath *create_index_path(PlannerInfo *root,
 									List *indexorderbys,
 									List *indexorderbycols,
 									List *pathkeys,
-									List *indexpathkeys,
 									ScanDirection indexscandir,
 									bool indexonly,
 									Relids required_outer,
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 1204e9623f..65a3c35611 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -248,7 +248,6 @@ extern List *truncate_useless_pathkeys(PlannerInfo *root,
 									   RelOptInfo *rel,
 									   List *pathkeys);
 extern bool has_useful_pathkeys(PlannerInfo *root, RelOptInfo *rel);
-extern bool is_pathkey_subset(List	*keys1, List* keys2);
 extern List *append_pathkeys(List *target, List *source);
 extern PathKey *make_canonical_pathkey(PlannerInfo *root,
 									   EquivalenceClass *eclass, Oid opfamily,
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index ab94f810fc..b2c6605e60 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -3984,210 +3984,7 @@ ORDER BY depname, empno;
                                  ->  Seq Scan on empsalary
 (12 rows)
 
--- Do not perform sort pushdown if column is presorted
-CREATE INDEX depname_idx ON empsalary(depname);
-SET enable_seqscan=0;
-EXPLAIN (COSTS OFF)
-SELECT empno,
-        min(salary) OVER (PARTITION BY depname) depminsalary
-FROM empsalary
-ORDER BY depname, empno;
-                      QUERY PLAN                       
--------------------------------------------------------
- Incremental Sort
-   Sort Key: depname, empno
-   Presorted Key: depname
-   ->  WindowAgg
-         ->  Index Scan using depname_idx on empsalary
-(5 rows)
-
--- Do perform sort pushdown if columns are only partially sorted
-EXPLAIN (COSTS OFF)
-SELECT empno,
-        min(salary) OVER (PARTITION BY depname, empno) depminsalary
-FROM empsalary
-ORDER BY depname, empno, enroll_date;
-                      QUERY PLAN                       
--------------------------------------------------------
- WindowAgg
-   ->  Incremental Sort
-         Sort Key: depname, empno, enroll_date
-         Presorted Key: depname
-         ->  Index Scan using depname_idx on empsalary
-(5 rows)
-
-DROP INDEX depname_idx;
-RESET enable_seqscan;
 RESET enable_hashagg;
--- If window function's ORDER BY is subset of QUERY's ORDER BY, sort as per as
--- QUERY's ORDER BY to save additional sorting in the end
-EXPLAIN (COSTS OFF)
-SELECT empno,
-       depname,
-       min(salary) OVER (ORDER BY depname) depminsalary
-FROM empsalary
-ORDER BY depname, empno;
-            QUERY PLAN             
------------------------------------
- WindowAgg
-   ->  Sort
-         Sort Key: depname, empno
-         ->  Seq Scan on empsalary
-(4 rows)
-
--- Same as above, but PARTITION BY clause is included as well
-EXPLAIN (COSTS OFF)
-SELECT empno,
-       depname,
-       min(salary) OVER (PARTITION BY depname ORDER BY empno) depminsalary
-FROM empsalary
-ORDER BY depname, empno, enroll_date;
-                  QUERY PLAN                   
------------------------------------------------
- WindowAgg
-   ->  Sort
-         Sort Key: depname, empno, enroll_date
-         ->  Seq Scan on empsalary
-(4 rows)
-
--- Negation of above, when sort optimization is not needed to be performed
-EXPLAIN (COSTS OFF)
-SELECT empno,
-       depname,
-       min(salary) OVER (PARTITION BY depname ORDER BY empno) depminsalary
-FROM empsalary
-ORDER BY empno, enroll_date;
-               QUERY PLAN                
------------------------------------------
- Sort
-   Sort Key: empno, enroll_date
-   ->  WindowAgg
-         ->  Sort
-               Sort Key: depname, empno
-               ->  Seq Scan on empsalary
-(6 rows)
-
--- ORDER BY's in multiple Window functions can be combined into one
--- if they are subset of QUERY's ORDER BY
-EXPLAIN (COSTS OFF)
-SELECT empno,
-       depname,
-       min(salary) OVER (PARTITION BY depname ORDER BY empno) depminsalary,
-       sum(salary) OVER (PARTITION BY depname) depsalary,
-       count(*) OVER (ORDER BY enroll_date DESC) c
-FROM empsalary
-ORDER BY depname, empno, enroll_date;
-                      QUERY PLAN                      
-------------------------------------------------------
- WindowAgg
-   ->  WindowAgg
-         ->  Sort
-               Sort Key: depname, empno, enroll_date
-               ->  WindowAgg
-                     ->  Sort
-                           Sort Key: enroll_date DESC
-                           ->  Seq Scan on empsalary
-(8 rows)
-
--- order of window function shouldn't affect query's ORDER BY pushdown
-EXPLAIN (COSTS OFF)
-SELECT empno,
-       depname,
-       count(*) OVER (ORDER BY enroll_date DESC) c,
-       sum(salary) OVER (PARTITION BY depname) depsalary,
-       min(salary) OVER (PARTITION BY depname ORDER BY empno) depminsalary
-FROM empsalary
-ORDER BY depname, empno, enroll_date;
-                      QUERY PLAN                      
-------------------------------------------------------
- WindowAgg
-   ->  WindowAgg
-         ->  Sort
-               Sort Key: depname, empno, enroll_date
-               ->  WindowAgg
-                     ->  Sort
-                           Sort Key: enroll_date DESC
-                           ->  Seq Scan on empsalary
-(8 rows)
-
--- SORT OPTIMIZATION (as in cases above) is not needed to be performed
--- in cases, as mentioned below.
--- Case #1: When distinct clause is included
-EXPLAIN (COSTS OFF)
-SELECT DISTINCT empno,
-       depname,
-       min(salary) OVER (PARTITION BY depname) depminsalary,
-       sum(salary) OVER (PARTITION BY depname) depsalary,
-       count(*) OVER (ORDER BY enroll_date DESC) c
-FROM empsalary
-ORDER BY depname, empno;
-                                              QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
- Unique
-   ->  Sort
-         Sort Key: depname, empno, (min(salary) OVER (?)), (sum(salary) OVER (?)), (count(*) OVER (?))
-         ->  WindowAgg
-               ->  Sort
-                     Sort Key: depname
-                     ->  WindowAgg
-                           ->  Sort
-                                 Sort Key: enroll_date DESC
-                                 ->  Seq Scan on empsalary
-(10 rows)
-
--- Case #2: When Limit clause is specified
-EXPLAIN (COSTS OFF)
-SELECT empno,
-       depname,
-       min(salary) OVER (PARTITION BY empno) depminsalary,
-       sum(salary) OVER (PARTITION BY empno) depsalary
-FROM empsalary
-ORDER BY empno, depname LIMIT 1;
-                  QUERY PLAN                   
------------------------------------------------
- Limit
-   ->  Incremental Sort
-         Sort Key: empno, depname
-         Presorted Key: empno
-         ->  WindowAgg
-               ->  Sort
-                     Sort Key: empno
-                     ->  Seq Scan on empsalary
-(8 rows)
-
--- Case #3: When ORDER BY contains any WindowFuncs
-EXPLAIN (COSTS OFF)
-SELECT empno,
-       row_number() over (PARTITION BY empno) esalary
-FROM empsalary
-ORDER BY empno, esalary;
-                 QUERY PLAN                 
---------------------------------------------
- Incremental Sort
-   Sort Key: empno, (row_number() OVER (?))
-   Presorted Key: empno
-   ->  WindowAgg
-         ->  Sort
-               Sort Key: empno
-               ->  Seq Scan on empsalary
-(7 rows)
-
--- Case #4: When WindowFunc ORDER BY is superset of QUERY's ORDER BY
-EXPLAIN (COSTS OFF)
-SELECT empno,
-       sum(salary) OVER (PARTITION BY depname) depsalary,
-       min(salary) OVER (PARTITION BY depname ORDER BY empno) depminsalary
-FROM empsalary
-ORDER BY depname;
-               QUERY PLAN                
------------------------------------------
- WindowAgg
-   ->  WindowAgg
-         ->  Sort
-               Sort Key: depname, empno
-               ->  Seq Scan on empsalary
-(5 rows)
-
 -- Test Sort node reordering
 EXPLAIN (COSTS OFF)
 SELECT
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 665e156920..b7bd0a83da 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1324,110 +1324,8 @@ SELECT DISTINCT
 FROM empsalary
 ORDER BY depname, empno;
 
--- Do not perform sort pushdown if column is presorted
-CREATE INDEX depname_idx ON empsalary(depname);
-SET enable_seqscan=0;
-
-EXPLAIN (COSTS OFF)
-SELECT empno,
-        min(salary) OVER (PARTITION BY depname) depminsalary
-FROM empsalary
-ORDER BY depname, empno;
-
--- Do perform sort pushdown if columns are only partially sorted
-EXPLAIN (COSTS OFF)
-SELECT empno,
-        min(salary) OVER (PARTITION BY depname, empno) depminsalary
-FROM empsalary
-ORDER BY depname, empno, enroll_date;
-
-DROP INDEX depname_idx;
-RESET enable_seqscan;
 RESET enable_hashagg;
 
--- If window function's ORDER BY is subset of QUERY's ORDER BY, sort as per as
--- QUERY's ORDER BY to save additional sorting in the end
-EXPLAIN (COSTS OFF)
-SELECT empno,
-       depname,
-       min(salary) OVER (ORDER BY depname) depminsalary
-FROM empsalary
-ORDER BY depname, empno;
-
--- Same as above, but PARTITION BY clause is included as well
-EXPLAIN (COSTS OFF)
-SELECT empno,
-       depname,
-       min(salary) OVER (PARTITION BY depname ORDER BY empno) depminsalary
-FROM empsalary
-ORDER BY depname, empno, enroll_date;
-
--- Negation of above, when sort optimization is not needed to be performed
-EXPLAIN (COSTS OFF)
-SELECT empno,
-       depname,
-       min(salary) OVER (PARTITION BY depname ORDER BY empno) depminsalary
-FROM empsalary
-ORDER BY empno, enroll_date;
-
--- ORDER BY's in multiple Window functions can be combined into one
--- if they are subset of QUERY's ORDER BY
-EXPLAIN (COSTS OFF)
-SELECT empno,
-       depname,
-       min(salary) OVER (PARTITION BY depname ORDER BY empno) depminsalary,
-       sum(salary) OVER (PARTITION BY depname) depsalary,
-       count(*) OVER (ORDER BY enroll_date DESC) c
-FROM empsalary
-ORDER BY depname, empno, enroll_date;
-
--- order of window function shouldn't affect query's ORDER BY pushdown
-EXPLAIN (COSTS OFF)
-SELECT empno,
-       depname,
-       count(*) OVER (ORDER BY enroll_date DESC) c,
-       sum(salary) OVER (PARTITION BY depname) depsalary,
-       min(salary) OVER (PARTITION BY depname ORDER BY empno) depminsalary
-FROM empsalary
-ORDER BY depname, empno, enroll_date;
-
--- SORT OPTIMIZATION (as in cases above) is not needed to be performed
--- in cases, as mentioned below.
-
--- Case #1: When distinct clause is included
-EXPLAIN (COSTS OFF)
-SELECT DISTINCT empno,
-       depname,
-       min(salary) OVER (PARTITION BY depname) depminsalary,
-       sum(salary) OVER (PARTITION BY depname) depsalary,
-       count(*) OVER (ORDER BY enroll_date DESC) c
-FROM empsalary
-ORDER BY depname, empno;
-
--- Case #2: When Limit clause is specified
-EXPLAIN (COSTS OFF)
-SELECT empno,
-       depname,
-       min(salary) OVER (PARTITION BY empno) depminsalary,
-       sum(salary) OVER (PARTITION BY empno) depsalary
-FROM empsalary
-ORDER BY empno, depname LIMIT 1;
-
--- Case #3: When ORDER BY contains any WindowFuncs
-EXPLAIN (COSTS OFF)
-SELECT empno,
-       row_number() over (PARTITION BY empno) esalary
-FROM empsalary
-ORDER BY empno, esalary;
-
--- Case #4: When WindowFunc ORDER BY is superset of QUERY's ORDER BY
-EXPLAIN (COSTS OFF)
-SELECT empno,
-       sum(salary) OVER (PARTITION BY depname) depsalary,
-       min(salary) OVER (PARTITION BY depname ORDER BY empno) depminsalary
-FROM empsalary
-ORDER BY depname;
-
 -- Test Sort node reordering
 EXPLAIN (COSTS OFF)
 SELECT