Hi Zhenglong,

> v4 is attached.

Thanks for v4.  With both patches applied on master, make check
passes, the eager aggregation query from my last mail returns its
rows again, and the tb/ts query keeps master's merge join over the
index.  To wrap up my review, here is what the patch changes,
including the small incremental patch attached.

Sorting is one of the expensive steps of a plan.  It grows
faster than its input and spills to disk once it exceeds work_mem.
A query with GROUP BY, window functions and ORDER BY may sort up to
three times, and a sort goes away when the output order of one step
already satisfies the order the next step needs.  Grouping itself
only needs equal values to be adjacent, so choosing the grouping
order well costs nothing and can save sorts.  Today the grouping
order is matched only to the query's ORDER BY, and its sort
direction is taken from ORDER BY as well.  So an index sorted in
the other direction can't be used for grouping, and with window
functions one more sort happens for the window right above the
grouping step.

With the patch, users see three changes.

- An index or an already sorted input can be used for grouping even
  if its direction differs from ORDER BY.  For example, with an
  index on (b, c), "GROUP BY b, c ORDER BY b, c DESC" can group
  straight off the index and sort only the (typically far fewer)
  groups for ORDER BY, when grouping by sort is chosen over
  hashing.  The same goes for the partial aggregation that eager
  aggregation pushes below a join.
- With window functions, one sort can serve both the grouping and
  the first window.  "GROUP BY a, b" with "OVER (PARTITION BY b)"
  used to sort twice when grouping by sort, by (a, b) and by (b);
  now it sorts once, by (b, a).  In parallel plans the workers
  share this sort.
- This is only an option.  The planner compares the old and the new
  orderings and takes the cheaper one, and turning
  enable_group_by_reordering off gives the old plans.

To do this, a grouping key and a sort key are taken to match if they
are on the same column with the same comparison semantics (the same
equivalence class and operator family), even if their sort direction
and NULLS position differ.  When another direction is taken, it goes
only into the planner's own copy of the grouping clause, so the query
as written is unchanged, and so is the output of view definitions.  The
window-matched ordering is an extra candidate that puts the first
window's PARTITION BY and ORDER BY keys first and keeps the remaining
keys in ORDER BY order.  The ordering used to plan the scans and joins
below stays as before, so an index that provides the old ordering
cheaply is not lost.  In parallel plans the workers sort in either
ordering before the results are gathered.

The incremental patch changes two things, and adds tests.

> - The window ordering now follows enable_group_by_reordering, like the
>   other reorderings. In v3 it was applied always.

Not only the window ordering.  With the setting off, v4 still does
the direction-insensitive matching in truncate_useless_pathkeys()
and adjust_group_pathkeys_to_input(), so the d1/d2 query in
eager_aggregate.sql gets a different plan from master.  The
incremental patch checks the setting there too, and adds that query
with the setting off to the test.

> 1. The missing operator error still happens with eager aggregation.

> Fixed. The check in v3 was only in group_keys_reorder_by_pathkeys(),
> but the partial aggregation goes through adjust_group_pathkeys_to_input(),
> which took the input's DESC pathkey without checking the operator. Now
> it stops there if the opfamily don't have the operator for that
> direction.

Here I take back my request from the last mail.  The guard covers
only one path: with the same operator class, a merge join over the
backward index path still fails on v4 with "missing operator".  On
the other hand, a btree operator class must provide all five
comparison operators (btree.sgml), and master already fails on such
a class with ORDER BY c DESC.  So the incremental patch removes the
guard and its two tests, and the eager aggregation query from my
last mail fails again (it works with enable_group_by_reordering
off).

But that isn't mine to decide, so I'd like to know where the
consensus among committers is: should the planner keep working with
a btree operator class missing an operator, or can it be treated as
unsupported?

As for the tests, I wanted to know which parts of the patch the
regression tests actually pin, so I changed the patched code one
place at a time (mutation testing) and ran make check.  Of 11 such
changes only one, dropping the window candidate in
get_useful_group_keys_orderings(), made a test fail.  The
incremental patch adds tests that catch most of the others that
change a plan.

As for performance, these are from a release build, alternating
with master on a shared machine, so please read them as ranges.

                              master         v4
  your tb/ts query           74-86 ms     56-63 ms
  ea/eb, hashagg off        181-192 ms     82-88 ms

  43 queries from my last mail   42 change plans: 30 faster
                                 (0.18-0.94x), 3 within 3%,
                                 9 slower (1.05-2.28x)
  planning time                  +2-6 us without windows (up to
                                 +22 us on two join queries),
                                 +3-8 us with windows

The window gains all come from 0002.  The incremental patch doesn't
change plans with default settings, so these numbers still hold.

The nine slower ones get the same plans with 0001 alone; they are
mostly the Index Scan + Incremental Sort shape, the existing cost
estimate problem we agreed to leave for a separate patch.  CREATE
STATISTICS (ndistinct) on the grouping columns fixes two of them
(2.2x and 1.25x).  For three (1.1-1.3x) it only makes master pick
the same slower plan, and four (1.03-1.16x) stay, mostly because
uncorrelated Index Scans and Incremental Sort over many small groups
are costed too low.

One slower case is not in the matrix and comes from 0002: a window
query over 2M rows goes from HashAggregate + Sort (~220 ms) to Sort +
GroupAggregate (~900 ms), because x % 4 is estimated through x (2M
groups expected, 77k actual).  CREATE STATISTICS ON (x % 4) fixes it;
ndistinct on the plain columns doesn't.  With enable_hashagg off, the
window ordering can also put a low-cardinality key first in a large
sort, which runs 25-40% slower at the same cost, as the sort cost
doesn't depend on key order; master has the same weakness with
ORDER BY.

To sum up, the gains are broad and large, and I found no wrong
results.  The losses are rare and come not from the patch's logic
but from existing limits of the planner's estimates and cost model:
group count estimates, a sort cost that ignores key order, and Index
Scans and Incremental Sorts costed too low.  The patch does give
those limits more chances to show, since it offers more orderings,
and a badly wrong estimate can make a query several times slower at
default settings.  Some cases are fixed by extended statistics; the
rest need cost model work, which I'd keep separate.

Based on the above, with the attached incremental patch, +1 from
me.

Best regards,
Henson
From b7f719c04243bc0f166faa8a7623215819c8ab65 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Thu, 12 Nov 2026 11:42:17 +0900
Subject: [PATCH] Drop sort operator guard and honor enable_group_by_reordering

A btree operator class must provide all five comparison operators, and
the planner already treats a missing one as an internal error.  The
guard in adjust_group_pathkeys_to_input() covered only one of the paths
that can sort in the missing direction, so remove
pathkey_has_sort_operator() and the tests for such an operator class.
group_keys_reorder_by_pathkeys() still stops matching if the operator
is missing, as it has to look the operator up anyway to adjust the
clause.

Also check enable_group_by_reordering in
count_common_leading_group_pathkeys() and adjust_group_pathkeys_to_input(),
so that turning it off gives the same plans as before.

Add tests for code that the regression tests did not cover, found by
mutation testing: windows with different PARTITION BY keys, the window
ordering in gather_grouping_paths(), GROUP BY keys that differ from an
index only in NULLS ordering next to an aggregate's ORDER BY, and the
partial aggregation over a parallel join with the setting on and off.
---
 src/backend/optimizer/path/pathkeys.c         |  58 ++----
 src/test/regress/expected/aggregates.out      | 103 +++++++---
 src/test/regress/expected/eager_aggregate.out | 182 +++++++++++-------
 src/test/regress/expected/window.out          | 125 ++++++++++++
 src/test/regress/sql/aggregates.sql           |  51 ++---
 src/test/regress/sql/eager_aggregate.sql      | 105 +++++-----
 src/test/regress/sql/window.sql               |  47 +++++
 7 files changed, 445 insertions(+), 226 deletions(-)

diff --git a/src/backend/optimizer/path/pathkeys.c 
b/src/backend/optimizer/path/pathkeys.c
index 8e31668f982..ab6a975bd8c 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -37,7 +37,6 @@ static bool matches_boolean_partition_clause(RestrictInfo 
*rinfo,
                                                                                
         int partkeycol);
 static Var *find_var_for_subquery_tle(RelOptInfo *rel, TargetEntry *tle);
 static bool right_merge_direction(PlannerInfo *root, PathKey *pathkey);
-static bool pathkey_has_sort_operator(PathKey *pathkey);
 
 
 /****************************************************************************
@@ -500,11 +499,9 @@ group_keys_reorder_by_pathkeys(List *pathkeys, List 
**group_pathkeys,
                                                                                
                                 pathkey->pk_cmptype);
 
                                /*
-                                * Give up if the operator family lacks the 
operator to sort
-                                * in the input pathkey's direction.  That is 
possible only
-                                * with an operator class that doesn't follow 
the btree rules,
-                                * but we needn't fail on it, as the GROUP BY 
keys can still
-                                * be sorted in their own direction.
+                                * A btree operator class must provide the 
operators for both
+                                * directions, but if it doesn't, just stop 
here; the GROUP BY
+                                * keys can still be sorted in their own 
direction.
                                 */
                                if (!OidIsValid(sortop))
                                        break;
@@ -567,12 +564,8 @@ group_keys_reorder_by_pathkeys(List *pathkeys, List 
**group_pathkeys,
  * grouping keys only for equality and leaves the final aggregation to provide
  * any ordering the query requires.
  *
- * As the result may be used to sort the input explicitly, stop at a pathkey
- * whose operator family lacks the sort operator for the input pathkey's
- * direction.  That is possible only with an operator class that doesn't
- * follow the btree rules, as an index can provide such an ordering without
- * the operator; but we needn't fail on it, as the remaining grouping pathkeys
- * can still be sorted in their own direction.
+ * Like the reordering of the GROUP BY keys, this is done only if
+ * enable_group_by_reordering is on.
  */
 List *
 adjust_group_pathkeys_to_input(List *group_pathkeys, List *pathkeys)
@@ -581,6 +574,9 @@ adjust_group_pathkeys_to_input(List *group_pathkeys, List 
*pathkeys)
        ListCell   *lc1,
                           *lc2;
 
+       if (!enable_group_by_reordering)
+               return result;
+
        forboth(lc1, result, lc2, pathkeys)
        {
                PathKey    *group_pathkey = (PathKey *) lfirst(lc1);
@@ -589,43 +585,12 @@ adjust_group_pathkeys_to_input(List *group_pathkeys, List 
*pathkeys)
                if (group_pathkey->pk_eclass != pathkey->pk_eclass ||
                        group_pathkey->pk_opfamily != pathkey->pk_opfamily)
                        break;
-               if (group_pathkey->pk_cmptype != pathkey->pk_cmptype &&
-                       !pathkey_has_sort_operator(pathkey))
-                       break;
                lfirst(lc1) = pathkey;
        }
 
        return result;
 }
 
-/*
- * pathkey_has_sort_operator
- *             Check that an explicit sort by 'pathkey' could be performed.
- *
- * prepare_sort_from_pathkeys() looks up the sort operator in the pathkey's
- * operator family using the nominal type of the equivalence member it sorts
- * by.  We don't know which member that will be, so require the operator for
- * the type of every member.
- */
-static bool
-pathkey_has_sort_operator(PathKey *pathkey)
-{
-       ListCell   *lc;
-
-       foreach(lc, pathkey->pk_eclass->ec_members)
-       {
-               EquivalenceMember *em = (EquivalenceMember *) lfirst(lc);
-
-               if 
(!OidIsValid(get_opfamily_member_for_cmptype(pathkey->pk_opfamily,
-                                                                               
                                em->em_datatype,
-                                                                               
                                em->em_datatype,
-                                                                               
                                pathkey->pk_cmptype)))
-                       return false;
-       }
-
-       return true;
-}
-
 /*
  * get_window_group_keys_ordering
  *             Reorder the GROUP BY keys to match the ordering of the first 
window.
@@ -2505,7 +2470,8 @@ count_common_leading_pathkeys_unordered(List *keys1, List 
*keys2)
  *
  * This is like count_common_leading_pathkeys_unordered() applied to
  * root->group_pathkeys, except that a pathkey matching a grouping key in
- * everything but sort direction and NULLS ordering counts as useful too.
+ * everything but sort direction and NULLS ordering counts as useful too,
+ * unless enable_group_by_reordering is off.
  */
 static int
 count_common_leading_group_pathkeys(PlannerInfo *root, List *pathkeys)
@@ -2516,6 +2482,10 @@ count_common_leading_group_pathkeys(PlannerInfo *root, 
List *pathkeys)
        if (root->group_pathkeys == NIL)
                return 0;
 
+       if (!enable_group_by_reordering)
+               return 
count_common_leading_pathkeys_unordered(root->group_pathkeys,
+                                                                               
                           pathkeys);
+
        foreach_node(PathKey, pathkey, pathkeys)
        {
                if (!list_member_ptr(root->group_pathkeys, pathkey) &&
diff --git a/src/test/regress/expected/aggregates.out 
b/src/test/regress/expected/aggregates.out
index 5b6c50402f4..9104f00f11b 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -3506,37 +3506,82 @@ SELECT x, array_agg(DISTINCT y ORDER BY y DESC) FROM 
btg_dir GROUP BY x ORDER BY
 (4 rows)
 
 DROP TABLE btg_dir;
--- An index scan in the direction that the operator class has no operator for
--- must not cause an error, though such an operator class is not valid
-BEGIN;
-CREATE TYPE btg_color AS ENUM ('r', 'g', 'b');
-CREATE FUNCTION btg_color_lt(btg_color, btg_color) RETURNS bool
-  LANGUAGE internal IMMUTABLE STRICT AS 'enum_lt';
-CREATE FUNCTION btg_color_eq(btg_color, btg_color) RETURNS bool
-  LANGUAGE internal IMMUTABLE STRICT AS 'enum_eq';
-CREATE FUNCTION btg_color_cmp(btg_color, btg_color) RETURNS int
-  LANGUAGE internal IMMUTABLE STRICT AS 'enum_cmp';
-CREATE OPERATOR <<< (LEFTARG = btg_color, RIGHTARG = btg_color,
-  FUNCTION = btg_color_lt);
-CREATE OPERATOR === (LEFTARG = btg_color, RIGHTARG = btg_color,
-  FUNCTION = btg_color_eq);
-CREATE OPERATOR CLASS btg_color_ops DEFAULT FOR TYPE btg_color USING btree AS
-  OPERATOR 1 <<<, OPERATOR 3 ===,
-  FUNCTION 1 btg_color_cmp(btg_color, btg_color);
-CREATE TABLE btg_color_tab AS
-  SELECT (ARRAY['r', 'g', 'b'])[1 + i % 3]::btg_color AS c
-  FROM generate_series(1, 300) AS i;
-CREATE INDEX ON btg_color_tab (c);
-ANALYZE btg_color_tab;
-SELECT c, count(*) FROM btg_color_tab GROUP BY c ORDER BY c;
- c | count 
----+-------
- r |   100
- g |   100
- b |   100
+-- Utilize the ordering of index scan even if it differs only in NULLS
+-- ordering from the GROUP BY key
+CREATE TABLE btg_nf AS
+  SELECT nullif(i % 5, 0) AS x, nullif(i % 7, 3) AS y, i AS w
+  FROM generate_series(1, 1000) AS i;
+CREATE INDEX btg_nf_x_y_idx ON btg_nf(x NULLS FIRST, y);
+ANALYZE btg_nf;
+EXPLAIN (COSTS OFF)
+SELECT x, count(*) FROM btg_nf GROUP BY x;
+                      QUERY PLAN                      
+------------------------------------------------------
+ GroupAggregate
+   Group Key: x
+   ->  Index Only Scan using btg_nf_x_y_idx on btg_nf
 (3 rows)
 
-ROLLBACK;
+SELECT x, count(*) FROM btg_nf GROUP BY x ORDER BY x;
+ x | count 
+---+-------
+ 1 |   200
+ 2 |   200
+ 3 |   200
+ 4 |   200
+   |   200
+(5 rows)
+
+-- ... but the ordering required by an aggregate must match exactly, so the
+-- index is of no use for an aggregate's ORDER BY key that differs in NULLS
+-- ordering
+EXPLAIN (COSTS OFF)
+SELECT w % 2 AS w2, array_agg(DISTINCT x ORDER BY x) FROM btg_nf GROUP BY w2;
+           QUERY PLAN           
+--------------------------------
+ GroupAggregate
+   Group Key: ((w % 2))
+   ->  Sort
+         Sort Key: ((w % 2)), x
+         ->  Seq Scan on btg_nf
+               Disabled: true
+(6 rows)
+
+SELECT w % 2 AS w2, array_agg(DISTINCT x ORDER BY x) FROM btg_nf GROUP BY w2;
+ w2 |   array_agg    
+----+----------------
+  0 | {1,2,3,4,NULL}
+  1 | {1,2,3,4,NULL}
+(2 rows)
+
+SELECT x, array_agg(DISTINCT y ORDER BY y DESC NULLS LAST)
+  FROM btg_nf GROUP BY x ORDER BY x;
+ x |     array_agg      
+---+--------------------
+ 1 | {6,5,4,2,1,0,NULL}
+ 2 | {6,5,4,2,1,0,NULL}
+ 3 | {6,5,4,2,1,0,NULL}
+ 4 | {6,5,4,2,1,0,NULL}
+   | {6,5,4,2,1,0,NULL}
+(5 rows)
+
+-- ... and neither is it for the GROUP BY key with enable_group_by_reordering
+-- off
+SET enable_group_by_reordering = off;
+EXPLAIN (COSTS OFF)
+SELECT x, sum(w) FROM btg_nf GROUP BY x;
+           QUERY PLAN           
+--------------------------------
+ GroupAggregate
+   Group Key: x
+   ->  Sort
+         Sort Key: x
+         ->  Seq Scan on btg_nf
+               Disabled: true
+(6 rows)
+
+RESET enable_group_by_reordering;
+DROP TABLE btg_nf;
 -- Test the case where the number of incoming subtree path keys is more than
 -- the number of grouping keys.
 CREATE INDEX btg_y_x_w_idx ON btg(y, x, w);
diff --git a/src/test/regress/expected/eager_aggregate.out 
b/src/test/regress/expected/eager_aggregate.out
index 605a3ce122d..62793df3ecc 100644
--- a/src/test/regress/expected/eager_aggregate.out
+++ b/src/test/regress/expected/eager_aggregate.out
@@ -674,90 +674,126 @@ GROUP BY d1.a, d1.b ORDER BY d1.a, d1.b DESC LIMIT 5;
  0 | 95 | 54500
 (5 rows)
 
-RESET enable_hashagg;
-DROP TABLE eager_agg_d1;
-DROP TABLE eager_agg_d2;
--- An index scan in the direction that the operator class has no operator for
--- must not cause an error in the partial aggregation, though such an operator
--- class is not valid
-CREATE TYPE eager_agg_color AS ENUM ('r', 'g', 'b');
-CREATE FUNCTION eager_agg_color_lt(eager_agg_color, eager_agg_color)
-  RETURNS bool LANGUAGE internal IMMUTABLE STRICT AS 'enum_lt';
-CREATE FUNCTION eager_agg_color_eq(eager_agg_color, eager_agg_color)
-  RETURNS bool LANGUAGE internal IMMUTABLE STRICT AS 'enum_eq';
-CREATE FUNCTION eager_agg_color_cmp(eager_agg_color, eager_agg_color)
-  RETURNS int LANGUAGE internal IMMUTABLE STRICT AS 'enum_cmp';
-CREATE OPERATOR <<< (LEFTARG = eager_agg_color, RIGHTARG = eager_agg_color,
-  FUNCTION = eager_agg_color_lt);
-CREATE OPERATOR === (LEFTARG = eager_agg_color, RIGHTARG = eager_agg_color,
-  FUNCTION = eager_agg_color_eq);
--- FUNCTION 4 is needed for the grouping key to be usable in eager aggregation
-CREATE OPERATOR CLASS eager_agg_color_ops DEFAULT FOR TYPE eager_agg_color
-  USING btree AS
-  OPERATOR 1 <<<, OPERATOR 3 ===,
-  FUNCTION 1 eager_agg_color_cmp(eager_agg_color, eager_agg_color),
-  FUNCTION 4 btequalimage(oid);
-CREATE TABLE eager_agg_c1 (c eager_agg_color, d int, x int);
-CREATE TABLE eager_agg_c2 (k int);
-INSERT INTO eager_agg_c1
-  SELECT (ARRAY['r', 'g', 'b'])[1 + i % 3]::eager_agg_color, i % 50, i
-  FROM generate_series(1, 3000) i;
-INSERT INTO eager_agg_c2 SELECT generate_series(0, 49);
-CREATE INDEX ON eager_agg_c1 (c DESC) INCLUDE (d, x);
-VACUUM ANALYZE eager_agg_c1, eager_agg_c2;
-SET parallel_setup_cost = 0;
-SET parallel_tuple_cost = 0;
-SET min_parallel_index_scan_size = 0;
-SET enable_hashagg = off;
+-- ... but not with enable_group_by_reordering off
+SET enable_group_by_reordering TO off;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT d1.a, d1.b, sum(d1.c)
+  FROM eager_agg_d1 d1
+  JOIN eager_agg_d2 d2 ON d1.a = d2.a
+GROUP BY d1.a, d1.b ORDER BY d1.a, d1.b DESC LIMIT 5;
+                                          QUERY PLAN                           
                
+-----------------------------------------------------------------------------------------------
+ Limit
+   Output: d1.a, d1.b, (sum(d1.c))
+   ->  Finalize GroupAggregate
+         Output: d1.a, d1.b, sum(d1.c)
+         Group Key: d1.a, d1.b
+         ->  Nested Loop
+               Output: d1.a, d1.b, (PARTIAL sum(d1.c))
+               Join Filter: (d1.a = d2.a)
+               ->  Partial GroupAggregate
+                     Output: d1.a, d1.b, PARTIAL sum(d1.c)
+                     Group Key: d1.a, d1.b
+                     ->  Incremental Sort
+                           Output: d1.a, d1.b, d1.c
+                           Sort Key: d1.a, d1.b DESC
+                           Presorted Key: d1.a
+                           ->  Index Scan using eager_agg_d1_a_b_idx on 
public.eager_agg_d1 d1
+                                 Output: d1.a, d1.b, d1.c
+               ->  Materialize
+                     Output: d2.a
+                     ->  Seq Scan on public.eager_agg_d2 d2
+                           Output: d2.a
+(21 rows)
+
+RESET enable_group_by_reordering;
+-- Likewise for partial aggregation below a parallel join, with an index
+-- that differs from the grouping key in both sort direction and NULLS
+-- ordering
+DROP INDEX eager_agg_d1_a_b_idx;
+CREATE INDEX eager_agg_d1_a_idx ON eager_agg_d1 (a DESC NULLS LAST);
+SET enable_nestloop TO off;
+SET enable_hashjoin TO off;
+SET parallel_setup_cost TO 0;
+SET parallel_tuple_cost TO 0;
+SET min_parallel_table_scan_size TO 0;
+SET min_parallel_index_scan_size TO 0;
+SET max_parallel_workers_per_gather TO 2;
 EXPLAIN (COSTS OFF)
-SELECT c1.c, sum(c1.x)
-  FROM eager_agg_c1 c1
-  JOIN eager_agg_c2 c2 ON c1.d = c2.k
-GROUP BY c1.c;
-                                                  QUERY PLAN                   
                               
---------------------------------------------------------------------------------------------------------------
+SELECT d1.a, sum(d1.c)
+  FROM eager_agg_d1 d1
+  JOIN eager_agg_d2 d2 ON d1.a = d2.a
+GROUP BY d1.a;
+                                       QUERY PLAN                              
          
+-----------------------------------------------------------------------------------------
  Finalize GroupAggregate
-   Group Key: c1.c
+   Group Key: d1.a
    ->  Gather Merge
          Workers Planned: 2
-         ->  Sort
-               Sort Key: c1.c
-               ->  Hash Join
-                     Hash Cond: (c1.d = c2.k)
-                     ->  Partial GroupAggregate
-                           Group Key: c1.c, c1.d
-                           ->  Sort
-                                 Sort Key: c1.c, c1.d
-                                 ->  Parallel Index Only Scan using 
eager_agg_c1_c_d_x_idx on eager_agg_c1 c1
-                     ->  Hash
-                           ->  Seq Scan on eager_agg_c2 c2
-(15 rows)
+         ->  Merge Join
+               Merge Cond: (d1.a = d2.a)
+               ->  Partial GroupAggregate
+                     Group Key: d1.a
+                     ->  Parallel Index Scan using eager_agg_d1_a_idx on 
eager_agg_d1 d1
+               ->  Sort
+                     Sort Key: d2.a DESC NULLS LAST
+                     ->  Seq Scan on eager_agg_d2 d2
+(12 rows)
 
-SELECT c1.c, sum(c1.x)
-  FROM eager_agg_c1 c1
-  JOIN eager_agg_c2 c2 ON c1.d = c2.k
-GROUP BY c1.c;
- c |   sum   
+SELECT d1.a, sum(d1.c)
+  FROM eager_agg_d1 d1
+  JOIN eager_agg_d2 d2 ON d1.a = d2.a
+GROUP BY d1.a ORDER BY d1.a;
+ a |   sum   
 ---+---------
- r | 1501500
- g | 1499500
- b | 1500500
-(3 rows)
+ 0 | 5005000
+ 1 | 4996000
+ 2 | 4997000
+ 3 | 4998000
+ 4 | 4999000
+ 5 | 5000000
+ 6 | 5001000
+ 7 | 5002000
+ 8 | 5003000
+ 9 | 5004000
+(10 rows)
 
+-- ... but not with enable_group_by_reordering off
+SET enable_group_by_reordering TO off;
+EXPLAIN (COSTS OFF)
+SELECT d1.a, sum(d1.c)
+  FROM eager_agg_d1 d1
+  JOIN eager_agg_d2 d2 ON d1.a = d2.a
+GROUP BY d1.a;
+                             QUERY PLAN                             
+--------------------------------------------------------------------
+ Finalize GroupAggregate
+   Group Key: d1.a
+   ->  Gather Merge
+         Workers Planned: 2
+         ->  Merge Join
+               Merge Cond: (d1.a = d2.a)
+               ->  Partial GroupAggregate
+                     Group Key: d1.a
+                     ->  Sort
+                           Sort Key: d1.a
+                           ->  Parallel Seq Scan on eager_agg_d1 d1
+               ->  Sort
+                     Sort Key: d2.a
+                     ->  Seq Scan on eager_agg_d2 d2
+(14 rows)
+
+RESET enable_group_by_reordering;
+RESET enable_nestloop;
+RESET enable_hashjoin;
 RESET parallel_setup_cost;
 RESET parallel_tuple_cost;
+RESET min_parallel_table_scan_size;
 RESET min_parallel_index_scan_size;
+RESET max_parallel_workers_per_gather;
 RESET enable_hashagg;
-DROP TABLE eager_agg_c1;
-DROP TABLE eager_agg_c2;
-DROP OPERATOR CLASS eager_agg_color_ops USING btree;
-DROP OPERATOR FAMILY eager_agg_color_ops USING btree;
-DROP OPERATOR <<< (eager_agg_color, eager_agg_color);
-DROP OPERATOR === (eager_agg_color, eager_agg_color);
-DROP FUNCTION eager_agg_color_lt(eager_agg_color, eager_agg_color);
-DROP FUNCTION eager_agg_color_eq(eager_agg_color, eager_agg_color);
-DROP FUNCTION eager_agg_color_cmp(eager_agg_color, eager_agg_color);
-DROP TYPE eager_agg_color;
+DROP TABLE eager_agg_d1;
+DROP TABLE eager_agg_d2;
 DROP TABLE eager_agg_t1;
 DROP TABLE eager_agg_t2;
 DROP TABLE eager_agg_t3;
diff --git a/src/test/regress/expected/window.out 
b/src/test/regress/expected/window.out
index 19f1b0a21c3..1609ca29851 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -5112,6 +5112,76 @@ ORDER BY depname, empno;
      4 | sales     |  3 |  1
 (10 rows)
 
+-- Ensure the GROUP BY keys follow the sort order of the first window to be
+-- evaluated when the windows have different PARTITION BY keys, which need
+-- not be the first window written in the query.  Only the other window
+-- needs a sort of its own.  Check the results too.
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+  (SELECT empno, depname, enroll_date,
+          count(*) OVER (PARTITION BY depname) c1,
+          count(*) OVER (PARTITION BY enroll_date) c2
+   FROM empsalary
+   GROUP BY empno, depname, enroll_date) s
+ORDER BY empno;
+                                             QUERY PLAN                        
                      
+-----------------------------------------------------------------------------------------------------
+ Sort
+   Sort Key: empsalary.empno
+   ->  WindowAgg
+         Window: w2 AS (PARTITION BY empsalary.depname)
+         ->  Sort
+               Sort Key: empsalary.depname
+               ->  WindowAgg
+                     Window: w1 AS (PARTITION BY empsalary.enroll_date)
+                     ->  Group
+                           Group Key: empsalary.enroll_date, empsalary.empno, 
empsalary.depname
+                           ->  Sort
+                                 Sort Key: empsalary.enroll_date, 
empsalary.empno, empsalary.depname
+                                 ->  Seq Scan on empsalary
+(13 rows)
+
+SELECT * FROM
+  (SELECT empno, depname, enroll_date,
+          count(*) OVER (PARTITION BY depname) c1,
+          count(*) OVER (PARTITION BY enroll_date) c2
+   FROM empsalary
+   GROUP BY empno, depname, enroll_date) s
+ORDER BY empno;
+ empno |  depname  | enroll_date | c1 | c2 
+-------+-----------+-------------+----+----
+     1 | sales     | 10-01-2006  |  3 |  2
+     2 | personnel | 12-23-2006  |  2 |  1
+     3 | sales     | 08-01-2007  |  3 |  2
+     4 | sales     | 08-08-2007  |  3 |  1
+     5 | personnel | 12-10-2007  |  2 |  1
+     7 | develop   | 01-01-2008  |  5 |  2
+     8 | develop   | 10-01-2006  |  5 |  2
+     9 | develop   | 01-01-2008  |  5 |  2
+    10 | develop   | 08-01-2007  |  5 |  2
+    11 | develop   | 08-15-2007  |  5 |  1
+(10 rows)
+
+-- Ensure the GROUP BY keys are left alone when the window's leading sort key
+-- is not one of them.
+EXPLAIN (COSTS OFF)
+SELECT empno, depname,
+       count(*) OVER (PARTITION BY lower(depname) ORDER BY empno) c
+FROM empsalary
+GROUP BY empno, depname;
+                               QUERY PLAN                               
+------------------------------------------------------------------------
+ WindowAgg
+   Window: w1 AS (PARTITION BY (lower((depname)::text)) ORDER BY empno)
+   ->  Sort
+         Sort Key: (lower((depname)::text)), empno
+         ->  Group
+               Group Key: empno, depname
+               ->  Sort
+                     Sort Key: empno, depname
+                     ->  Seq Scan on empsalary
+(9 rows)
+
 -- Ensure the GROUP BY key does not adopt the window's sort ordering when
 -- the query's ORDER BY has given it a different equality operator.
 EXPLAIN (COSTS OFF)
@@ -5302,6 +5372,61 @@ RESET enable_hashjoin;
 DROP TABLE groupwin_s, groupwin_b;
 DROP TABLE groupwin;
 RESET enable_hashagg;
+-- Ensure partially aggregated rows are sorted in the first window's sort
+-- order below Gather Merge, so that the finalized groups need no further
+-- sort for the WindowAgg.
+SET max_parallel_workers_per_gather = 2;
+SET parallel_setup_cost = 0;
+SET parallel_tuple_cost = 0;
+SET min_parallel_table_scan_size = 0;
+EXPLAIN (COSTS OFF)
+SELECT four, ten, sum(unique1), rank() OVER (PARTITION BY ten ORDER BY four)
+FROM tenk1 GROUP BY four, ten;
+                                QUERY PLAN                                 
+---------------------------------------------------------------------------
+ WindowAgg
+   Window: w1 AS (PARTITION BY ten ORDER BY four ROWS UNBOUNDED PRECEDING)
+   ->  Finalize GroupAggregate
+         Group Key: ten, four
+         ->  Gather Merge
+               Workers Planned: 2
+               ->  Sort
+                     Sort Key: ten, four
+                     ->  Partial HashAggregate
+                           Group Key: four, ten
+                           ->  Parallel Seq Scan on tenk1
+(11 rows)
+
+SELECT four, ten, sum(unique1), rank() OVER (PARTITION BY ten ORDER BY four)
+FROM tenk1 GROUP BY four, ten;
+ four | ten |   sum   | rank 
+------+-----+---------+------
+    0 |   0 | 2495000 |    1
+    2 |   0 | 2500000 |    2
+    1 |   1 | 2495500 |    1
+    3 |   1 | 2500500 |    2
+    0 |   2 | 2501000 |    1
+    2 |   2 | 2496000 |    2
+    1 |   3 | 2501500 |    1
+    3 |   3 | 2496500 |    2
+    0 |   4 | 2497000 |    1
+    2 |   4 | 2502000 |    2
+    1 |   5 | 2497500 |    1
+    3 |   5 | 2502500 |    2
+    0 |   6 | 2503000 |    1
+    2 |   6 | 2498000 |    2
+    1 |   7 | 2503500 |    1
+    3 |   7 | 2498500 |    2
+    0 |   8 | 2499000 |    1
+    2 |   8 | 2504000 |    2
+    1 |   9 | 2499500 |    1
+    3 |   9 | 2504500 |    2
+(20 rows)
+
+RESET max_parallel_workers_per_gather;
+RESET parallel_setup_cost;
+RESET parallel_tuple_cost;
+RESET min_parallel_table_scan_size;
 CREATE INDEX empsalary_salary_empno_idx ON empsalary (salary, empno);
 SET enable_seqscan = 0;
 -- Ensure no sorting is done and that the IndexScan maintains all pathkeys
diff --git a/src/test/regress/sql/aggregates.sql 
b/src/test/regress/sql/aggregates.sql
index 942353d055e..97997d9a537 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -1491,30 +1491,33 @@ SELECT x, array_agg(y ORDER BY y DESC) FROM btg_dir 
GROUP BY x;
 SELECT x, array_agg(DISTINCT y ORDER BY y DESC) FROM btg_dir GROUP BY x ORDER 
BY x;
 DROP TABLE btg_dir;
 
--- An index scan in the direction that the operator class has no operator for
--- must not cause an error, though such an operator class is not valid
-BEGIN;
-CREATE TYPE btg_color AS ENUM ('r', 'g', 'b');
-CREATE FUNCTION btg_color_lt(btg_color, btg_color) RETURNS bool
-  LANGUAGE internal IMMUTABLE STRICT AS 'enum_lt';
-CREATE FUNCTION btg_color_eq(btg_color, btg_color) RETURNS bool
-  LANGUAGE internal IMMUTABLE STRICT AS 'enum_eq';
-CREATE FUNCTION btg_color_cmp(btg_color, btg_color) RETURNS int
-  LANGUAGE internal IMMUTABLE STRICT AS 'enum_cmp';
-CREATE OPERATOR <<< (LEFTARG = btg_color, RIGHTARG = btg_color,
-  FUNCTION = btg_color_lt);
-CREATE OPERATOR === (LEFTARG = btg_color, RIGHTARG = btg_color,
-  FUNCTION = btg_color_eq);
-CREATE OPERATOR CLASS btg_color_ops DEFAULT FOR TYPE btg_color USING btree AS
-  OPERATOR 1 <<<, OPERATOR 3 ===,
-  FUNCTION 1 btg_color_cmp(btg_color, btg_color);
-CREATE TABLE btg_color_tab AS
-  SELECT (ARRAY['r', 'g', 'b'])[1 + i % 3]::btg_color AS c
-  FROM generate_series(1, 300) AS i;
-CREATE INDEX ON btg_color_tab (c);
-ANALYZE btg_color_tab;
-SELECT c, count(*) FROM btg_color_tab GROUP BY c ORDER BY c;
-ROLLBACK;
+-- Utilize the ordering of index scan even if it differs only in NULLS
+-- ordering from the GROUP BY key
+CREATE TABLE btg_nf AS
+  SELECT nullif(i % 5, 0) AS x, nullif(i % 7, 3) AS y, i AS w
+  FROM generate_series(1, 1000) AS i;
+CREATE INDEX btg_nf_x_y_idx ON btg_nf(x NULLS FIRST, y);
+ANALYZE btg_nf;
+EXPLAIN (COSTS OFF)
+SELECT x, count(*) FROM btg_nf GROUP BY x;
+SELECT x, count(*) FROM btg_nf GROUP BY x ORDER BY x;
+
+-- ... but the ordering required by an aggregate must match exactly, so the
+-- index is of no use for an aggregate's ORDER BY key that differs in NULLS
+-- ordering
+EXPLAIN (COSTS OFF)
+SELECT w % 2 AS w2, array_agg(DISTINCT x ORDER BY x) FROM btg_nf GROUP BY w2;
+SELECT w % 2 AS w2, array_agg(DISTINCT x ORDER BY x) FROM btg_nf GROUP BY w2;
+SELECT x, array_agg(DISTINCT y ORDER BY y DESC NULLS LAST)
+  FROM btg_nf GROUP BY x ORDER BY x;
+
+-- ... and neither is it for the GROUP BY key with enable_group_by_reordering
+-- off
+SET enable_group_by_reordering = off;
+EXPLAIN (COSTS OFF)
+SELECT x, sum(w) FROM btg_nf GROUP BY x;
+RESET enable_group_by_reordering;
+DROP TABLE btg_nf;
 
 -- Test the case where the number of incoming subtree path keys is more than
 -- the number of grouping keys.
diff --git a/src/test/regress/sql/eager_aggregate.sql 
b/src/test/regress/sql/eager_aggregate.sql
index 34609c7257b..a9675c191d5 100644
--- a/src/test/regress/sql/eager_aggregate.sql
+++ b/src/test/regress/sql/eager_aggregate.sql
@@ -256,71 +256,64 @@ SELECT d1.a, d1.b, sum(d1.c)
   JOIN eager_agg_d2 d2 ON d1.a = d2.a
 GROUP BY d1.a, d1.b ORDER BY d1.a, d1.b DESC LIMIT 5;
 
-RESET enable_hashagg;
+-- ... but not with enable_group_by_reordering off
+SET enable_group_by_reordering TO off;
 
-DROP TABLE eager_agg_d1;
-DROP TABLE eager_agg_d2;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT d1.a, d1.b, sum(d1.c)
+  FROM eager_agg_d1 d1
+  JOIN eager_agg_d2 d2 ON d1.a = d2.a
+GROUP BY d1.a, d1.b ORDER BY d1.a, d1.b DESC LIMIT 5;
+
+RESET enable_group_by_reordering;
+
+-- Likewise for partial aggregation below a parallel join, with an index
+-- that differs from the grouping key in both sort direction and NULLS
+-- ordering
+DROP INDEX eager_agg_d1_a_b_idx;
+CREATE INDEX eager_agg_d1_a_idx ON eager_agg_d1 (a DESC NULLS LAST);
+
+SET enable_nestloop TO off;
+SET enable_hashjoin TO off;
+SET parallel_setup_cost TO 0;
+SET parallel_tuple_cost TO 0;
+SET min_parallel_table_scan_size TO 0;
+SET min_parallel_index_scan_size TO 0;
+SET max_parallel_workers_per_gather TO 2;
+
+EXPLAIN (COSTS OFF)
+SELECT d1.a, sum(d1.c)
+  FROM eager_agg_d1 d1
+  JOIN eager_agg_d2 d2 ON d1.a = d2.a
+GROUP BY d1.a;
 
--- An index scan in the direction that the operator class has no operator for
--- must not cause an error in the partial aggregation, though such an operator
--- class is not valid
-CREATE TYPE eager_agg_color AS ENUM ('r', 'g', 'b');
-CREATE FUNCTION eager_agg_color_lt(eager_agg_color, eager_agg_color)
-  RETURNS bool LANGUAGE internal IMMUTABLE STRICT AS 'enum_lt';
-CREATE FUNCTION eager_agg_color_eq(eager_agg_color, eager_agg_color)
-  RETURNS bool LANGUAGE internal IMMUTABLE STRICT AS 'enum_eq';
-CREATE FUNCTION eager_agg_color_cmp(eager_agg_color, eager_agg_color)
-  RETURNS int LANGUAGE internal IMMUTABLE STRICT AS 'enum_cmp';
-CREATE OPERATOR <<< (LEFTARG = eager_agg_color, RIGHTARG = eager_agg_color,
-  FUNCTION = eager_agg_color_lt);
-CREATE OPERATOR === (LEFTARG = eager_agg_color, RIGHTARG = eager_agg_color,
-  FUNCTION = eager_agg_color_eq);
--- FUNCTION 4 is needed for the grouping key to be usable in eager aggregation
-CREATE OPERATOR CLASS eager_agg_color_ops DEFAULT FOR TYPE eager_agg_color
-  USING btree AS
-  OPERATOR 1 <<<, OPERATOR 3 ===,
-  FUNCTION 1 eager_agg_color_cmp(eager_agg_color, eager_agg_color),
-  FUNCTION 4 btequalimage(oid);
-CREATE TABLE eager_agg_c1 (c eager_agg_color, d int, x int);
-CREATE TABLE eager_agg_c2 (k int);
-INSERT INTO eager_agg_c1
-  SELECT (ARRAY['r', 'g', 'b'])[1 + i % 3]::eager_agg_color, i % 50, i
-  FROM generate_series(1, 3000) i;
-INSERT INTO eager_agg_c2 SELECT generate_series(0, 49);
-CREATE INDEX ON eager_agg_c1 (c DESC) INCLUDE (d, x);
-VACUUM ANALYZE eager_agg_c1, eager_agg_c2;
-
-
-SET parallel_setup_cost = 0;
-SET parallel_tuple_cost = 0;
-SET min_parallel_index_scan_size = 0;
-SET enable_hashagg = off;
+SELECT d1.a, sum(d1.c)
+  FROM eager_agg_d1 d1
+  JOIN eager_agg_d2 d2 ON d1.a = d2.a
+GROUP BY d1.a ORDER BY d1.a;
+
+-- ... but not with enable_group_by_reordering off
+SET enable_group_by_reordering TO off;
 
 EXPLAIN (COSTS OFF)
-SELECT c1.c, sum(c1.x)
-  FROM eager_agg_c1 c1
-  JOIN eager_agg_c2 c2 ON c1.d = c2.k
-GROUP BY c1.c;
-
-SELECT c1.c, sum(c1.x)
-  FROM eager_agg_c1 c1
-  JOIN eager_agg_c2 c2 ON c1.d = c2.k
-GROUP BY c1.c;
+SELECT d1.a, sum(d1.c)
+  FROM eager_agg_d1 d1
+  JOIN eager_agg_d2 d2 ON d1.a = d2.a
+GROUP BY d1.a;
+
+RESET enable_group_by_reordering;
+RESET enable_nestloop;
+RESET enable_hashjoin;
 RESET parallel_setup_cost;
 RESET parallel_tuple_cost;
+RESET min_parallel_table_scan_size;
 RESET min_parallel_index_scan_size;
+RESET max_parallel_workers_per_gather;
+
 RESET enable_hashagg;
 
-DROP TABLE eager_agg_c1;
-DROP TABLE eager_agg_c2;
-DROP OPERATOR CLASS eager_agg_color_ops USING btree;
-DROP OPERATOR FAMILY eager_agg_color_ops USING btree;
-DROP OPERATOR <<< (eager_agg_color, eager_agg_color);
-DROP OPERATOR === (eager_agg_color, eager_agg_color);
-DROP FUNCTION eager_agg_color_lt(eager_agg_color, eager_agg_color);
-DROP FUNCTION eager_agg_color_eq(eager_agg_color, eager_agg_color);
-DROP FUNCTION eager_agg_color_cmp(eager_agg_color, eager_agg_color);
-DROP TYPE eager_agg_color;
+DROP TABLE eager_agg_d1;
+DROP TABLE eager_agg_d2;
 
 DROP TABLE eager_agg_t1;
 DROP TABLE eager_agg_t2;
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 479c91cd247..4649c49f380 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1785,6 +1785,34 @@ SELECT * FROM
    GROUP BY empno, depname) s
 ORDER BY depname, empno;
 
+-- Ensure the GROUP BY keys follow the sort order of the first window to be
+-- evaluated when the windows have different PARTITION BY keys, which need
+-- not be the first window written in the query.  Only the other window
+-- needs a sort of its own.  Check the results too.
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+  (SELECT empno, depname, enroll_date,
+          count(*) OVER (PARTITION BY depname) c1,
+          count(*) OVER (PARTITION BY enroll_date) c2
+   FROM empsalary
+   GROUP BY empno, depname, enroll_date) s
+ORDER BY empno;
+SELECT * FROM
+  (SELECT empno, depname, enroll_date,
+          count(*) OVER (PARTITION BY depname) c1,
+          count(*) OVER (PARTITION BY enroll_date) c2
+   FROM empsalary
+   GROUP BY empno, depname, enroll_date) s
+ORDER BY empno;
+
+-- Ensure the GROUP BY keys are left alone when the window's leading sort key
+-- is not one of them.
+EXPLAIN (COSTS OFF)
+SELECT empno, depname,
+       count(*) OVER (PARTITION BY lower(depname) ORDER BY empno) c
+FROM empsalary
+GROUP BY empno, depname;
+
 -- Ensure the GROUP BY key does not adopt the window's sort ordering when
 -- the query's ORDER BY has given it a different equality operator.
 EXPLAIN (COSTS OFF)
@@ -1877,6 +1905,25 @@ DROP TABLE groupwin;
 
 RESET enable_hashagg;
 
+-- Ensure partially aggregated rows are sorted in the first window's sort
+-- order below Gather Merge, so that the finalized groups need no further
+-- sort for the WindowAgg.
+SET max_parallel_workers_per_gather = 2;
+SET parallel_setup_cost = 0;
+SET parallel_tuple_cost = 0;
+SET min_parallel_table_scan_size = 0;
+
+EXPLAIN (COSTS OFF)
+SELECT four, ten, sum(unique1), rank() OVER (PARTITION BY ten ORDER BY four)
+FROM tenk1 GROUP BY four, ten;
+SELECT four, ten, sum(unique1), rank() OVER (PARTITION BY ten ORDER BY four)
+FROM tenk1 GROUP BY four, ten;
+
+RESET max_parallel_workers_per_gather;
+RESET parallel_setup_cost;
+RESET parallel_tuple_cost;
+RESET min_parallel_table_scan_size;
+
 CREATE INDEX empsalary_salary_empno_idx ON empsalary (salary, empno);
 
 SET enable_seqscan = 0;
-- 
2.54.0 (Apple Git-157)

Reply via email to