From b7f719c04243bc0f166faa8a7623215819c8ab65 Mon Sep 17 00:00:00 2001 From: Henson Choi 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)