diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 0d46e096e5..ca38e78f21 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -2879,6 +2879,37 @@ FROM t1 WHERE c2 < 100 GROUP BY c1 ORDER BY 2; (10 rows) DROP TABLE t1 CASCADE; +-- Check, that GROUP-BY reordering optimization can operate with pathkeys, built +-- by planner itself. For example, by MergeJoin. +SET enable_hashjoin = off; +SET enable_nestloop = off; +explain (COSTS OFF) +SELECT c1.relname,c1.relpages +FROM pg_class c1 JOIN pg_class c2 ON (c1.relname=c2.relname AND c1.relpages=c2.relpages) +GROUP BY c1.reltuples,c1.relpages,c1.relname +ORDER BY c1.relpages, c1.relname, c1.relpages*c1.relpages; + QUERY PLAN +--------------------------------------------------------------------------------------------- + Incremental Sort + Sort Key: c1.relpages, c1.relname, ((c1.relpages * c1.relpages)) + Presorted Key: c1.relpages, c1.relname + -> Group + Group Key: c1.relpages, c1.relname, c1.reltuples + -> Incremental Sort + Sort Key: c1.relpages, c1.relname, c1.reltuples + Presorted Key: c1.relpages, c1.relname + -> Merge Join + Merge Cond: ((c1.relpages = c2.relpages) AND (c1.relname = c2.relname)) + -> Sort + Sort Key: c1.relpages, c1.relname + -> Seq Scan on pg_class c1 + -> Sort + Sort Key: c2.relpages, c2.relname + -> Seq Scan on pg_class c2 +(16 rows) + +RESET enable_hashjoin; +RESET enable_nestloop; RESET enable_hashagg; RESET max_parallel_workers; RESET max_parallel_workers_per_gather; diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index f99167ac9e..cf87b5d5dd 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -1233,6 +1233,18 @@ SELECT array_agg(c1 ORDER BY c2),c2 FROM t1 WHERE c2 < 100 GROUP BY c1 ORDER BY 2; DROP TABLE t1 CASCADE; +-- Check, that GROUP-BY reordering optimization can operate with pathkeys, built +-- by planner itself. For example, by MergeJoin. +SET enable_hashjoin = off; +SET enable_nestloop = off; +explain (COSTS OFF) +SELECT c1.relname,c1.relpages +FROM pg_class c1 JOIN pg_class c2 ON (c1.relname=c2.relname AND c1.relpages=c2.relpages) +GROUP BY c1.reltuples,c1.relpages,c1.relname +ORDER BY c1.relpages, c1.relname, c1.relpages*c1.relpages; +RESET enable_hashjoin; +RESET enable_nestloop; + RESET enable_hashagg; RESET max_parallel_workers; RESET max_parallel_workers_per_gather;