Restore preprocess_groupclause()

0452b461bc made optimizer explore alternative orderings of group-by pathkeys.
It eliminated preprocess_groupclause(), which was intended to match items
between GROUP BY and ORDER BY.  Instead, get_useful_group_keys_orderings()
function generates orderings of GROUP BY elements at the time of grouping
paths generation.  The get_useful_group_keys_orderings() function takes into
account 3 orderings of GROUP BY pathkeys and clauses: original order as written
in GROUP BY, matching ORDER BY clauses as much as possible, and matching the
input path as much as possible.  Given that even before 0452b461b,
preprocess_groupclause() could change the original order of GROUP BY clauses
we don't need to consider it apart from ordering matching ORDER BY clauses.

This commit restores preprocess_groupclause() to provide an ordering of
GROUP BY elements matching ORDER BY before generation of paths.  The new
version of preprocess_groupclause() takes into account an incremental sort.
The get_useful_group_keys_orderings() function now takes into 2 orderings of
GROUP BY elements: the order generated preprocess_groupclause() and the order
matching the input path as much as possible.

Discussion: https://postgr.es/m/CAPpHfdvyWLMGwvxaf%3D7KAp-z-4mxbSH8ti2f6mNOQv5metZFzg%40mail.gmail.com
Author: Alexander Korotkov
Reviewed-by: Andrei Lepikhov, Pavel Borisov

1 files changed, 5 insertions, 50 deletions

diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 0a25b6384bb..416fc4e240b 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -448,26 +448,6 @@ group_keys_reorder_by_pathkeys(List *pathkeys, List **group_pathkeys,
 }
 
 /*
- * pathkeys_are_duplicate
- *		Check if give pathkeys are already contained the list of
- *		GroupByOrdering's.
- */
-static bool
-pathkeys_are_duplicate(List *infos, List *pathkeys)
-{
-	ListCell   *lc;
-
-	foreach(lc, infos)
-	{
-		GroupByOrdering *info = lfirst_node(GroupByOrdering, lc);
-
-		if (compare_pathkeys(pathkeys, info->pathkeys) == PATHKEYS_EQUAL)
-			return true;
-	}
-	return false;
-}
-
-/*
  * get_useful_group_keys_orderings
  *		Determine which orderings of GROUP BY keys are potentially interesting.
  *
@@ -475,11 +455,11 @@ pathkeys_are_duplicate(List *infos, List *pathkeys)
  * ordering of GROUP BY keys.  Each item stores pathkeys and clauses in the
  * matching order.
  *
- * The function considers (and keeps) multiple GROUP BY orderings:
+ * The function considers (and keeps) following GROUP BY orderings:
  *
- * - the original ordering, as specified by the GROUP BY clause,
- * - GROUP BY keys reordered to match 'path' ordering (as much as possible),
- * - GROUP BY keys to match target ORDER BY clause (as much as possible).
+ * - GROUP BY keys as ordered by preprocess_groupclause() to match target
+ *   ORDER BY clause (as much as possible),
+ * - GROUP BY keys reordered to match 'path' ordering (as much as possible).
  */
 List *
 get_useful_group_keys_orderings(PlannerInfo *root, Path *path)
@@ -526,32 +506,7 @@ get_useful_group_keys_orderings(PlannerInfo *root, Path *path)
 
 		if (n > 0 &&
 			(enable_incremental_sort || n == root->num_groupby_pathkeys) &&
-			!pathkeys_are_duplicate(infos, pathkeys))
-		{
-			info = makeNode(GroupByOrdering);
-			info->pathkeys = pathkeys;
-			info->clauses = clauses;
-
-			infos = lappend(infos, info);
-		}
-	}
-
-	/*
-	 * Try reordering pathkeys to minimize the sort cost (this time consider
-	 * the ORDER BY clause).
-	 */
-	if (root->sort_pathkeys &&
-		!pathkeys_contained_in(root->sort_pathkeys, root->group_pathkeys))
-	{
-		int			n;
-
-		n = group_keys_reorder_by_pathkeys(root->sort_pathkeys, &pathkeys,
-										   &clauses,
-										   root->num_groupby_pathkeys);
-
-		if (n > 0 &&
-			(enable_incremental_sort || n == list_length(root->sort_pathkeys)) &&
-			!pathkeys_are_duplicate(infos, pathkeys))
+			compare_pathkeys(pathkeys, root->group_pathkeys) != PATHKEYS_EQUAL)
 		{
 			info = makeNode(GroupByOrdering);
 			info->pathkeys = pathkeys;