summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/aggregates.out202
-rw-r--r--src/test/regress/expected/sysviews.out3
-rw-r--r--src/test/regress/sql/aggregates.sql75
3 files changed, 279 insertions, 1 deletions
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index f635c5a1afb..67dd20f3751 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -2728,6 +2728,208 @@ SELECT balk(hundred) FROM tenk1;
(1 row)
ROLLBACK;
+-- GROUP BY optimization by reorder columns
+CREATE TABLE btg AS SELECT
+ i % 100 AS x,
+ i % 100 AS y,
+ 'abc' || i % 10 AS z,
+ i AS w
+FROM generate_series(1,10000) AS i;
+CREATE INDEX abc ON btg(x,y);
+ANALYZE btg;
+-- GROUP BY optimization by reorder columns by frequency
+SET enable_hashagg=off;
+SET max_parallel_workers= 0;
+SET max_parallel_workers_per_gather = 0;
+-- Utilize index scan ordering to avoid a Sort operation
+EXPLAIN (COSTS OFF) SELECT count(*) FROM btg GROUP BY x,y;
+ QUERY PLAN
+----------------------------------------
+ GroupAggregate
+ Group Key: x, y
+ -> Index Only Scan using abc on btg
+(3 rows)
+
+EXPLAIN (COSTS OFF) SELECT count(*) FROM btg GROUP BY y,x;
+ QUERY PLAN
+----------------------------------------
+ GroupAggregate
+ Group Key: x, y
+ -> Index Only Scan using abc on btg
+(3 rows)
+
+-- Engage incremental sort
+explain (COSTS OFF) SELECT x,y FROM btg GROUP BY x,y,z,w;
+ QUERY PLAN
+-----------------------------------------
+ Group
+ Group Key: x, y, z, w
+ -> Incremental Sort
+ Sort Key: x, y, z, w
+ Presorted Key: x, y
+ -> Index Scan using abc on btg
+(6 rows)
+
+explain (COSTS OFF) SELECT x,y FROM btg GROUP BY z,y,w,x;
+ QUERY PLAN
+-----------------------------------------
+ Group
+ Group Key: x, y, z, w
+ -> Incremental Sort
+ Sort Key: x, y, z, w
+ Presorted Key: x, y
+ -> Index Scan using abc on btg
+(6 rows)
+
+explain (COSTS OFF) SELECT x,y FROM btg GROUP BY w,z,x,y;
+ QUERY PLAN
+-----------------------------------------
+ Group
+ Group Key: x, y, w, z
+ -> Incremental Sort
+ Sort Key: x, y, w, z
+ Presorted Key: x, y
+ -> Index Scan using abc on btg
+(6 rows)
+
+explain (COSTS OFF) SELECT x,y FROM btg GROUP BY w,x,z,y;
+ QUERY PLAN
+-----------------------------------------
+ Group
+ Group Key: x, y, w, z
+ -> Incremental Sort
+ Sort Key: x, y, w, z
+ Presorted Key: x, y
+ -> Index Scan using abc on btg
+(6 rows)
+
+-- Subqueries
+explain (COSTS OFF) SELECT x,y
+FROM (SELECT * FROM btg ORDER BY x,y,w,z) AS q1
+GROUP BY (w,x,z,y);
+ QUERY PLAN
+----------------------------------------------
+ Group
+ Group Key: btg.x, btg.y, btg.w, btg.z
+ -> Incremental Sort
+ Sort Key: btg.x, btg.y, btg.w, btg.z
+ Presorted Key: btg.x, btg.y
+ -> Index Scan using abc on btg
+(6 rows)
+
+explain (COSTS OFF) SELECT x,y
+FROM (SELECT * FROM btg ORDER BY x,y,w,z LIMIT 100) AS q1
+GROUP BY (w,x,z,y);
+ QUERY PLAN
+----------------------------------------------------
+ Group
+ Group Key: btg.x, btg.y, btg.w, btg.z
+ -> Limit
+ -> Incremental Sort
+ Sort Key: btg.x, btg.y, btg.w, btg.z
+ Presorted Key: btg.x, btg.y
+ -> Index Scan using abc on btg
+(7 rows)
+
+-- Should work with and without GROUP-BY optimization
+explain (COSTS OFF) SELECT x,y FROM btg GROUP BY w,x,z,y ORDER BY y,x,z,w;
+ QUERY PLAN
+------------------------------
+ Group
+ Group Key: y, x, z, w
+ -> Sort
+ Sort Key: y, x, z, w
+ -> Seq Scan on btg
+(5 rows)
+
+-- Utilize incremental sort to make the ORDER BY rule a bit cheaper
+explain (COSTS OFF) SELECT x,w FROM btg GROUP BY w,x,y,z ORDER BY x*x,z;
+ QUERY PLAN
+-----------------------------------------------
+ Sort
+ Sort Key: ((x * x)), z
+ -> Group
+ Group Key: x, y, w, z
+ -> Incremental Sort
+ Sort Key: x, y, w, z
+ Presorted Key: x, y
+ -> Index Scan using abc on btg
+(8 rows)
+
+SET enable_incremental_sort = off;
+-- The case when the number of incoming subtree path keys is more than
+-- the number of grouping keys.
+CREATE INDEX idx_y_x_z ON btg(y,x,w);
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT y,x,array_agg(distinct w) FROM btg WHERE y < 0 GROUP BY x,y;
+ QUERY PLAN
+-----------------------------------------------------
+ GroupAggregate
+ Output: y, x, array_agg(DISTINCT w)
+ Group Key: btg.y, btg.x
+ -> Index Only Scan using idx_y_x_z on public.btg
+ Output: y, x, w
+ Index Cond: (btg.y < 0)
+(6 rows)
+
+RESET enable_incremental_sort;
+DROP TABLE btg;
+-- The case, when scanning sort order correspond to aggregate sort order but
+-- can not be found in the group-by list
+CREATE TABLE t1 (c1 int PRIMARY KEY, c2 int);
+CREATE UNIQUE INDEX ON t1(c2);
+explain (costs off)
+SELECT array_agg(c1 ORDER BY c2),c2
+FROM t1 WHERE c2 < 100 GROUP BY c1 ORDER BY 2;
+ QUERY PLAN
+--------------------------------------------------------
+ Sort
+ Sort Key: c2
+ -> GroupAggregate
+ Group Key: c1
+ -> Sort
+ Sort Key: c1, c2
+ -> Bitmap Heap Scan on t1
+ Recheck Cond: (c2 < 100)
+ -> Bitmap Index Scan on t1_c2_idx
+ Index Cond: (c2 < 100)
+(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;
-- Secondly test the case of a parallel aggregate combiner function
-- returning NULL. For that use normal transition function, but a
-- combiner function returning NULL.
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 271313ebf86..9be7aca2b8a 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -114,6 +114,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_async_append | on
enable_bitmapscan | on
enable_gathermerge | on
+ enable_group_by_reordering | on
enable_hashagg | on
enable_hashjoin | on
enable_incremental_sort | on
@@ -133,7 +134,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_seqscan | on
enable_sort | on
enable_tidscan | on
-(22 rows)
+(23 rows)
-- There are always wait event descriptions for various types.
select type, count(*) > 0 as ok FROM pg_wait_events
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index cc8f0efad55..524bdfa67d6 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -1181,6 +1181,81 @@ SELECT balk(hundred) FROM tenk1;
ROLLBACK;
+-- GROUP BY optimization by reorder columns
+CREATE TABLE btg AS SELECT
+ i % 100 AS x,
+ i % 100 AS y,
+ 'abc' || i % 10 AS z,
+ i AS w
+FROM generate_series(1,10000) AS i;
+CREATE INDEX abc ON btg(x,y);
+ANALYZE btg;
+
+-- GROUP BY optimization by reorder columns by frequency
+
+SET enable_hashagg=off;
+SET max_parallel_workers= 0;
+SET max_parallel_workers_per_gather = 0;
+
+-- Utilize index scan ordering to avoid a Sort operation
+EXPLAIN (COSTS OFF) SELECT count(*) FROM btg GROUP BY x,y;
+EXPLAIN (COSTS OFF) SELECT count(*) FROM btg GROUP BY y,x;
+
+-- Engage incremental sort
+explain (COSTS OFF) SELECT x,y FROM btg GROUP BY x,y,z,w;
+explain (COSTS OFF) SELECT x,y FROM btg GROUP BY z,y,w,x;
+explain (COSTS OFF) SELECT x,y FROM btg GROUP BY w,z,x,y;
+explain (COSTS OFF) SELECT x,y FROM btg GROUP BY w,x,z,y;
+
+-- Subqueries
+explain (COSTS OFF) SELECT x,y
+FROM (SELECT * FROM btg ORDER BY x,y,w,z) AS q1
+GROUP BY (w,x,z,y);
+explain (COSTS OFF) SELECT x,y
+FROM (SELECT * FROM btg ORDER BY x,y,w,z LIMIT 100) AS q1
+GROUP BY (w,x,z,y);
+
+-- Should work with and without GROUP-BY optimization
+explain (COSTS OFF) SELECT x,y FROM btg GROUP BY w,x,z,y ORDER BY y,x,z,w;
+
+-- Utilize incremental sort to make the ORDER BY rule a bit cheaper
+explain (COSTS OFF) SELECT x,w FROM btg GROUP BY w,x,y,z ORDER BY x*x,z;
+
+SET enable_incremental_sort = off;
+-- The case when the number of incoming subtree path keys is more than
+-- the number of grouping keys.
+CREATE INDEX idx_y_x_z ON btg(y,x,w);
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT y,x,array_agg(distinct w) FROM btg WHERE y < 0 GROUP BY x,y;
+RESET enable_incremental_sort;
+
+DROP TABLE btg;
+
+-- The case, when scanning sort order correspond to aggregate sort order but
+-- can not be found in the group-by list
+CREATE TABLE t1 (c1 int PRIMARY KEY, c2 int);
+CREATE UNIQUE INDEX ON t1(c2);
+explain (costs off)
+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;
+
-- Secondly test the case of a parallel aggregate combiner function
-- returning NULL. For that use normal transition function, but a
-- combiner function returning NULL.
generated by cgit v1.2.3 (git 2.46.0) at 2025-10-15 18:32:39 +0000