aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/backend/utils/adt/int8.c15
-rw-r--r--src/backend/utils/adt/windowfuncs.c29
-rw-r--r--src/test/regress/expected/window.out94
-rw-r--r--src/test/regress/sql/window.sql43
4 files changed, 116 insertions, 65 deletions
diff --git a/src/backend/utils/adt/int8.c b/src/backend/utils/adt/int8.c
index 123b4eb6bfb..41fbeec8fd7 100644
--- a/src/backend/utils/adt/int8.c
+++ b/src/backend/utils/adt/int8.c
@@ -833,6 +833,21 @@ int8inc_support(PG_FUNCTION_ARGS)
SupportRequestWFuncMonotonic *req = (SupportRequestWFuncMonotonic *) rawreq;
MonotonicFunction monotonic = MONOTONICFUNC_NONE;
int frameOptions = req->window_clause->frameOptions;
+ WindowFunc *wfunc = req->window_func;
+
+ if (list_length(wfunc->args) == 1)
+ {
+ Node *expr = eval_const_expressions(NULL, linitial(wfunc->args));
+
+ /*
+ * Due to the Node representation of WindowClause runConditions in
+ * version prior to v17, we need to insist that the count arg is
+ * Const to allow safe application of the runCondition
+ * optimization.
+ */
+ if (!IsA(expr, Const))
+ PG_RETURN_POINTER(NULL);
+ }
/* No ORDER BY clause then all rows are peers */
if (req->window_clause->orderClause == NIL)
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index b87a624fb2f..0c7cc55845a 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -14,6 +14,7 @@
#include "postgres.h"
#include "nodes/supportnodes.h"
+#include "optimizer/optimizer.h"
#include "utils/builtins.h"
#include "windowapi.h"
@@ -486,13 +487,29 @@ window_ntile_support(PG_FUNCTION_ARGS)
if (IsA(rawreq, SupportRequestWFuncMonotonic))
{
SupportRequestWFuncMonotonic *req = (SupportRequestWFuncMonotonic *) rawreq;
+ WindowFunc *wfunc = req->window_func;
- /*
- * ntile() is monotonically increasing as the number of buckets cannot
- * change after the first call
- */
- req->monotonic = MONOTONICFUNC_INCREASING;
- PG_RETURN_POINTER(req);
+ if (list_length(wfunc->args) == 1)
+ {
+ Node *expr = eval_const_expressions(NULL, linitial(wfunc->args));
+
+ /*
+ * Due to the Node representation of WindowClause runConditions in
+ * version prior to v17, we need to insist that ntile arg is Const
+ * to allow safe application of the runCondition optimization.
+ */
+ if (IsA(expr, Const))
+ {
+ /*
+ * ntile() is monotonically increasing as the number of
+ * buckets cannot change after the first call
+ */
+ req->monotonic = MONOTONICFUNC_INCREASING;
+ PG_RETURN_POINTER(req);
+ }
+ }
+
+ PG_RETURN_POINTER(NULL);
}
if (IsA(rawreq, SupportRequestOptimizeWindowClause))
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 86127887022..1a9cc66a6ba 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -3577,13 +3577,13 @@ EXPLAIN (COSTS OFF)
SELECT * FROM
(SELECT empno,
salary,
- count(empno) OVER (ORDER BY salary DESC) c
+ count(1) OVER (ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------
WindowAgg
- Run Condition: (count(empsalary.empno) OVER (?) <= 3)
+ Run Condition: (count(1) OVER (?) <= 3)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
@@ -3592,7 +3592,7 @@ WHERE c <= 3;
SELECT * FROM
(SELECT empno,
salary,
- count(empno) OVER (ORDER BY salary DESC) c
+ count(1) OVER (ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
empno | salary | c
@@ -3704,19 +3704,19 @@ WHERE rn < 3;
-> Seq Scan on empsalary
(6 rows)
--- likewise with count(empno) instead of row_number()
+-- likewise with count(1) instead of row_number()
EXPLAIN (COSTS OFF)
SELECT * FROM
(SELECT empno,
depname,
salary,
- count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c
+ count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
QUERY PLAN
------------------------------------------------------------
WindowAgg
- Run Condition: (count(empsalary.empno) OVER (?) <= 3)
+ Run Condition: (count(1) OVER (?) <= 3)
-> Sort
Sort Key: empsalary.depname, empsalary.salary DESC
-> Seq Scan on empsalary
@@ -3727,7 +3727,7 @@ SELECT * FROM
(SELECT empno,
depname,
salary,
- count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c
+ count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
empno | depname | salary | c
@@ -3749,13 +3749,13 @@ SELECT * FROM
(SELECT empno,
depname,
salary,
- count(empno) OVER () c
+ count(1) OVER () c
FROM empsalary) emp
WHERE c = 1;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+------------------------------------------
WindowAgg
- Run Condition: (count(empsalary.empno) OVER (?) = 1)
+ Run Condition: (count(1) OVER (?) = 1)
-> Seq Scan on empsalary
(3 rows)
@@ -3763,7 +3763,7 @@ WHERE c = 1;
EXPLAIN (COSTS OFF)
SELECT * FROM
(SELECT *,
- count(salary) OVER (PARTITION BY depname || '') c1, -- w1
+ count(1) OVER (PARTITION BY depname || '') c1, -- w1
row_number() OVER (PARTITION BY depname) rn, -- w2
count(*) OVER (PARTITION BY depname) c2, -- w2
count(*) OVER (PARTITION BY '' || depname) c3, -- w3
@@ -3775,7 +3775,7 @@ SELECT * FROM
Subquery Scan on e
-> WindowAgg
Filter: (((row_number() OVER (?)) <= 1) AND ((ntile(2) OVER (?)) < 2))
- Run Condition: (count(empsalary.salary) OVER (?) <= 3)
+ Run Condition: (count(1) OVER (?) <= 3)
-> Sort
Sort Key: (((empsalary.depname)::text || ''::text))
-> WindowAgg
@@ -3791,7 +3791,7 @@ SELECT * FROM
-- Ensure we correctly filter out all of the run conditions from each window
SELECT * FROM
(SELECT *,
- count(salary) OVER (PARTITION BY depname || '') c1, -- w1
+ count(1) OVER (PARTITION BY depname || '') c1, -- w1
row_number() OVER (PARTITION BY depname) rn, -- w2
count(*) OVER (PARTITION BY depname) c2, -- w2
count(*) OVER (PARTITION BY '' || depname) c3, -- w3
@@ -3804,32 +3804,6 @@ SELECT * FROM
sales | 3 | 4800 | 08-01-2007 | 3 | 1 | 3 | 3 | 1
(2 rows)
--- Ensure we remove references to reduced outer joins as nulling rels in run
--- conditions
-EXPLAIN (COSTS OFF)
-SELECT 1 FROM
- (SELECT ntile(e2.salary) OVER (PARTITION BY e1.depname) AS c
- FROM empsalary e1 LEFT JOIN empsalary e2 ON TRUE
- WHERE e1.empno = e2.empno) s
-WHERE s.c = 1;
- QUERY PLAN
----------------------------------------------------------
- Subquery Scan on s
- Filter: (s.c = 1)
- -> WindowAgg
- Run Condition: (ntile(e2.salary) OVER (?) <= 1)
- -> Sort
- Sort Key: e1.depname
- -> Merge Join
- Merge Cond: (e1.empno = e2.empno)
- -> Sort
- Sort Key: e1.empno
- -> Seq Scan on empsalary e1
- -> Sort
- Sort Key: e2.empno
- -> Seq Scan on empsalary e2
-(14 rows)
-
-- Tests to ensure we don't push down the run condition when it's not valid to
-- do so.
-- Ensure we don't push down when the frame options show that the window
@@ -3889,6 +3863,42 @@ WHERE c = 1;
-> Seq Scan on empsalary
(6 rows)
+-- Ensure we don't use a run condition when the WindowFunc arg contains a Var
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY empno DESC) c
+ FROM empsalary) emp
+WHERE c = 1;
+ QUERY PLAN
+----------------------------------------------
+ Subquery Scan on emp
+ Filter: (emp.c = 1)
+ -> WindowAgg
+ -> Sort
+ Sort Key: empsalary.empno DESC
+ -> Seq Scan on empsalary
+(6 rows)
+
+-- As above but with ntile().
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ ntile(empno::int) OVER (ORDER BY empno DESC) nt
+ FROM empsalary) emp
+WHERE nt = 1;
+ QUERY PLAN
+----------------------------------------------
+ Subquery Scan on emp
+ Filter: (emp.nt = 1)
+ -> WindowAgg
+ -> Sort
+ Sort Key: empsalary.empno DESC
+ -> Seq Scan on empsalary
+(6 rows)
+
-- Ensure we don't use a run condition when the WindowFunc contains subplans
EXPLAIN (COSTS OFF)
SELECT * FROM
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index c0ad51ca544..e35c16a8276 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1120,14 +1120,14 @@ EXPLAIN (COSTS OFF)
SELECT * FROM
(SELECT empno,
salary,
- count(empno) OVER (ORDER BY salary DESC) c
+ count(1) OVER (ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
SELECT * FROM
(SELECT empno,
salary,
- count(empno) OVER (ORDER BY salary DESC) c
+ count(1) OVER (ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
@@ -1183,13 +1183,13 @@ SELECT empno, depname FROM
FROM empsalary) emp
WHERE rn < 3;
--- likewise with count(empno) instead of row_number()
+-- likewise with count(1) instead of row_number()
EXPLAIN (COSTS OFF)
SELECT * FROM
(SELECT empno,
depname,
salary,
- count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c
+ count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
@@ -1198,7 +1198,7 @@ SELECT * FROM
(SELECT empno,
depname,
salary,
- count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c
+ count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
@@ -1209,7 +1209,7 @@ SELECT * FROM
(SELECT empno,
depname,
salary,
- count(empno) OVER () c
+ count(1) OVER () c
FROM empsalary) emp
WHERE c = 1;
@@ -1217,7 +1217,7 @@ WHERE c = 1;
EXPLAIN (COSTS OFF)
SELECT * FROM
(SELECT *,
- count(salary) OVER (PARTITION BY depname || '') c1, -- w1
+ count(1) OVER (PARTITION BY depname || '') c1, -- w1
row_number() OVER (PARTITION BY depname) rn, -- w2
count(*) OVER (PARTITION BY depname) c2, -- w2
count(*) OVER (PARTITION BY '' || depname) c3, -- w3
@@ -1228,7 +1228,7 @@ SELECT * FROM
-- Ensure we correctly filter out all of the run conditions from each window
SELECT * FROM
(SELECT *,
- count(salary) OVER (PARTITION BY depname || '') c1, -- w1
+ count(1) OVER (PARTITION BY depname || '') c1, -- w1
row_number() OVER (PARTITION BY depname) rn, -- w2
count(*) OVER (PARTITION BY depname) c2, -- w2
count(*) OVER (PARTITION BY '' || depname) c3, -- w3
@@ -1236,15 +1236,6 @@ SELECT * FROM
FROM empsalary
) e WHERE rn <= 1 AND c1 <= 3 AND nt < 2;
--- Ensure we remove references to reduced outer joins as nulling rels in run
--- conditions
-EXPLAIN (COSTS OFF)
-SELECT 1 FROM
- (SELECT ntile(e2.salary) OVER (PARTITION BY e1.depname) AS c
- FROM empsalary e1 LEFT JOIN empsalary e2 ON TRUE
- WHERE e1.empno = e2.empno) s
-WHERE s.c = 1;
-
-- Tests to ensure we don't push down the run condition when it's not valid to
-- do so.
@@ -1278,6 +1269,24 @@ SELECT * FROM
FROM empsalary) emp
WHERE c = 1;
+-- Ensure we don't use a run condition when the WindowFunc arg contains a Var
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY empno DESC) c
+ FROM empsalary) emp
+WHERE c = 1;
+
+-- As above but with ntile().
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ ntile(empno::int) OVER (ORDER BY empno DESC) nt
+ FROM empsalary) emp
+WHERE nt = 1;
+
-- Ensure we don't use a run condition when the WindowFunc contains subplans
EXPLAIN (COSTS OFF)
SELECT * FROM
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-19 09:11:32 +0000