v2-0001-Disable-run-conditions-for-ntile-var-and-count-va.patch

From 97812b07cd07bbd65b206f792093d8451ea4fe72 Mon Sep 17 00:00:00 2001
From: David Rowley <dgrowley@gmail.com>
Date: Tue, 23 Apr 2024 14:31:11 +1200
Subject: [PATCH v2 1/2] Disable run conditions for ntile(var) and count(var)

---
 src/backend/utils/adt/int8.c         |  9 +++
 src/backend/utils/adt/windowfuncs.c  | 24 +++++--
 src/test/regress/expected/window.out | 94 +++++++++++++++-------------
 src/test/regress/sql/window.sql      | 43 ++++++++-----
 4 files changed, 105 insertions(+), 65 deletions(-)

diff --git a/src/backend/utils/adt/int8.c b/src/backend/utils/adt/int8.c
index 54fa3bc379..e7b1d389b3 100644
--- a/src/backend/utils/adt/int8.c
+++ b/src/backend/utils/adt/int8.c
@@ -832,6 +832,15 @@ 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));
+
+			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 473c61569f..b49e48109d 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -16,6 +16,7 @@
 #include "nodes/parsenodes.h"
 #include "nodes/supportnodes.h"
 #include "utils/fmgrprotos.h"
+#include "optimizer/optimizer.h"
 #include "windowapi.h"
 
 /*
@@ -487,13 +488,24 @@ 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));
+
+			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 e46710cf31..2f2cb112b1 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -3954,13 +3954,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
@@ -3969,7 +3969,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 
@@ -4081,19 +4081,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
@@ -4104,7 +4104,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 
@@ -4126,13 +4126,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)
 
@@ -4140,7 +4140,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
@@ -4152,7 +4152,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
@@ -4168,7 +4168,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
@@ -4181,32 +4181,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
@@ -4266,6 +4240,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 678aa1a21c..284f47b514 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1252,14 +1252,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;
 
@@ -1315,13 +1315,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;
 
@@ -1330,7 +1330,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;
 
@@ -1341,7 +1341,7 @@ SELECT * FROM
   (SELECT empno,
           depname,
           salary,
-          count(empno) OVER () c
+          count(1) OVER () c
    FROM empsalary) emp
 WHERE c = 1;
 
@@ -1349,7 +1349,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
@@ -1360,7 +1360,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
@@ -1368,15 +1368,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.
 
@@ -1410,6 +1401,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
-- 
2.40.1