Attaching test cases for this (+ small change in doc).
Tested this in one of WIP branch where I had modified
select_active_windows and it failed
as expected.
Please let me know if something can be improved in this.
Regards,
Ankit Kumar Pandey
From 7647759eb92e1a0560bcff73b4169be8694f83d8 Mon Sep 17 00:00:00 2001
From: Ankit Kumar Pandey <itsanki...@gmail.com>
Date: Wed, 4 Jan 2023 19:57:57 +0530
Subject: [PATCH] Add test cases for optimal ordering of window function order
by clauses
In case of multiple orderby clauses for window function, it is desired
to have minimum sort operations. This is already implemented in code but
corresponding test cases were missing.
This patch adds test cases covering various cases where order by clause
get optimized and some additional comments in function which implements
this feature.
---
src/backend/optimizer/plan/planner.c | 5 +
src/test/regress/expected/window.out | 140 +++++++++++++++++++++++++++
src/test/regress/sql/window.sql | 51 ++++++++++
3 files changed, 196 insertions(+)
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index d6ba7589f3..e3989a7b44 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -5592,6 +5592,11 @@ optimize_window_clauses(PlannerInfo *root, WindowFuncLists *wflists)
* select_active_windows
* Create a list of the "active" window clauses (ie, those referenced
* by non-deleted WindowFuncs) in the order they are to be executed.
+ * Window clauses are ordered by the highest tleSortGroupRef first,
+ * resulting in the lowest order tleSortGroupRefs being the last WindowAgg to be
+ * processed. This reduces requirement for sort in lower order WindowAgg
+ * as it is quite likely that required column is already sorted and if not,
+ * there is possibility of incremental sort.
*/
static List *
select_active_windows(PlannerInfo *root, WindowFuncLists *wflists)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 5505e9a2da..71d0cff587 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -4733,3 +4733,143 @@ SELECT * FROM pg_temp.f(2);
{5}
(5 rows)
+-- test optimal ordering for minimal sort operations
+CREATE temp TABLE abcd (a int, b int, c int, d int);
+INSERT INTO abcd
+ SELECT p,q,r,s FROM
+ generate_series(1,5) p,
+ generate_Series(1,5) q,
+ generate_Series(1,5) r,
+ generate_Series(1,5) s;
+EXPLAIN (COSTS OFF)
+ SELECT row_number() OVER (ORDER BY b),
+ count(*) OVER (ORDER BY a,b)
+ FROM abcd ORDER BY a,b,c;
+ QUERY PLAN
+------------------------------------------------
+ Incremental Sort
+ Sort Key: a, b, c
+ Presorted Key: a, b
+ -> WindowAgg
+ -> Sort
+ Sort Key: a, b
+ -> WindowAgg
+ -> Sort
+ Sort Key: b
+ -> Seq Scan on abcd
+(10 rows)
+
+EXPLAIN (COSTS OFF)
+ SELECT row_number() OVER (ORDER BY a, b),
+ count(*) OVER (ORDER BY a)
+ FROM abcd ORDER BY a,b,c;
+ QUERY PLAN
+------------------------------------------
+ Incremental Sort
+ Sort Key: a, b, c
+ Presorted Key: a, b
+ -> WindowAgg
+ -> WindowAgg
+ -> Sort
+ Sort Key: a, b
+ -> Seq Scan on abcd
+(8 rows)
+
+EXPLAIN (COSTS OFF)
+ SELECT row_number() OVER (ORDER BY a),
+ count(*) OVER (ORDER BY a,b,c)
+ FROM abcd ORDER BY a,b;
+ QUERY PLAN
+------------------------------------
+ WindowAgg
+ -> WindowAgg
+ -> Sort
+ Sort Key: a, b, c
+ -> Seq Scan on abcd
+(5 rows)
+
+EXPLAIN (COSTS OFF)
+ SELECT row_number() OVER (ORDER BY a),
+ count(*) OVER (ORDER BY a,c),
+ count(*) OVER (ORDER BY a,b)
+ FROM abcd ORDER BY a,d;
+ QUERY PLAN
+------------------------------------------------------
+ Incremental Sort
+ Sort Key: a, d
+ Presorted Key: a
+ -> WindowAgg
+ -> WindowAgg
+ -> Incremental Sort
+ Sort Key: a, c
+ Presorted Key: a
+ -> WindowAgg
+ -> Sort
+ Sort Key: a, b
+ -> Seq Scan on abcd
+(12 rows)
+
+EXPLAIN (COSTS OFF)
+ SELECT row_number() OVER (ORDER BY a),
+ count(*) OVER (ORDER BY a,b),
+ count(*) OVER (ORDER BY a,c)
+ FROM abcd ORDER BY a,d;
+ QUERY PLAN
+------------------------------------------------------
+ Incremental Sort
+ Sort Key: a, d
+ Presorted Key: a
+ -> WindowAgg
+ -> WindowAgg
+ -> Incremental Sort
+ Sort Key: a, b
+ Presorted Key: a
+ -> WindowAgg
+ -> Sort
+ Sort Key: a, c
+ -> Seq Scan on abcd
+(12 rows)
+
+EXPLAIN (COSTS OFF)
+ SELECT row_number() OVER (ORDER BY a),
+ count(*) OVER (ORDER BY a,b),
+ count(*) OVER (ORDER BY a,c)
+ FROM abcd ORDER BY a,b,c;
+ QUERY PLAN
+------------------------------------------------------
+ Incremental Sort
+ Sort Key: a, b, c
+ Presorted Key: a, b
+ -> WindowAgg
+ -> WindowAgg
+ -> Incremental Sort
+ Sort Key: a, b
+ Presorted Key: a
+ -> WindowAgg
+ -> Sort
+ Sort Key: a, c
+ -> Seq Scan on abcd
+(12 rows)
+
+EXPLAIN (COSTS OFF) SELECT row_number() OVER (ORDER BY a),
+ count(*) OVER (ORDER BY a,c),
+ count(*) OVER (ORDER BY a,b)
+ FROM abcd ORDER BY a,b,c;
+ QUERY PLAN
+------------------------------------------------------
+ Incremental Sort
+ Sort Key: a, b, c
+ Presorted Key: a, b
+ -> WindowAgg
+ -> WindowAgg
+ -> Incremental Sort
+ Sort Key: a, b
+ Presorted Key: a
+ -> WindowAgg
+ -> Sort
+ Sort Key: a, c
+ -> Seq Scan on abcd
+(12 rows)
+
+-- cleanup
+DROP TABLE abcd;
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 10f450fee4..f17b8cedfd 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1673,3 +1673,54 @@ $$ LANGUAGE SQL STABLE;
EXPLAIN (costs off) SELECT * FROM pg_temp.f(2);
SELECT * FROM pg_temp.f(2);
+
+-- test optimal ordering for minimal sort operations
+
+CREATE temp TABLE abcd (a int, b int, c int, d int);
+INSERT INTO abcd
+ SELECT p,q,r,s FROM
+ generate_series(1,5) p,
+ generate_Series(1,5) q,
+ generate_Series(1,5) r,
+ generate_Series(1,5) s;
+
+EXPLAIN (COSTS OFF)
+ SELECT row_number() OVER (ORDER BY b),
+ count(*) OVER (ORDER BY a,b)
+ FROM abcd ORDER BY a,b,c;
+
+EXPLAIN (COSTS OFF)
+ SELECT row_number() OVER (ORDER BY a, b),
+ count(*) OVER (ORDER BY a)
+ FROM abcd ORDER BY a,b,c;
+
+EXPLAIN (COSTS OFF)
+ SELECT row_number() OVER (ORDER BY a),
+ count(*) OVER (ORDER BY a,b,c)
+ FROM abcd ORDER BY a,b;
+
+EXPLAIN (COSTS OFF)
+ SELECT row_number() OVER (ORDER BY a),
+ count(*) OVER (ORDER BY a,c),
+ count(*) OVER (ORDER BY a,b)
+ FROM abcd ORDER BY a,d;
+
+EXPLAIN (COSTS OFF)
+ SELECT row_number() OVER (ORDER BY a),
+ count(*) OVER (ORDER BY a,b),
+ count(*) OVER (ORDER BY a,c)
+ FROM abcd ORDER BY a,d;
+
+EXPLAIN (COSTS OFF)
+ SELECT row_number() OVER (ORDER BY a),
+ count(*) OVER (ORDER BY a,b),
+ count(*) OVER (ORDER BY a,c)
+ FROM abcd ORDER BY a,b,c;
+
+EXPLAIN (COSTS OFF) SELECT row_number() OVER (ORDER BY a),
+ count(*) OVER (ORDER BY a,c),
+ count(*) OVER (ORDER BY a,b)
+ FROM abcd ORDER BY a,b,c;
+
+-- cleanup
+DROP TABLE abcd;
--
2.37.2
