postgres.git / summary / log / commit / refs
commit 7e5d20bbd13fae7522003f7c3655c9165fb1b3e7
Author: David Rowley <drowley@postgresql.org>
Date: Wed May 01 04:35:37 2024 +0000
First released in: 15.7, 16.3
Disable run condition optimization for some WindowFuncs
94985c210 added code to detect when WindowFuncs were monotonic and
allowed additional quals to be "pushed down" into the subquery to be
used as WindowClause runConditions in order to short-circuit execution
in nodeWindowAgg.c.
The Node representation of runConditions wasn't well selected and
because we do qual pushdown before planning the subquery, the planning
of the subquery could perform subquery pull-up of nested subqueries.
For WindowFuncs with args, the arguments could be changed after pushing
the qual down to the subquery.
This was made more difficult by the fact that the code duplicated the
WindowFunc inside an OpExpr to include in the WindowClauses runCondition
field. This could result in duplication of subqueries and a pull-up of
such a subquery could result in another initplan parameter being issued
for the 2nd version of the subplan. This could result in errors such as:
ERROR: WindowFunc not found in subplan target lists
Here in the backbranches, we don't have the flexibility to improve the
Node representation to resolve this, so instead we just disable the
runCondition optimization for ntile() unless the argument is a Const,
(v16 only) and likewise for count(expr) (both v15 and v16). count(*) is
unaffected. All other window functions which support this optimization
all take zero arguments and therefore are unaffected.
Bug: #18170
Reported-by: Zuming Jiang
Discussion: https://postgr.es/m/18170-f1d17bf9a0d58b24@postgresql.org
Backpatch-through 15 (master will be fixed independently)
src/backend/utils/adt/int8.c | 15 +++++++++++
src/backend/utils/adt/windowfuncs.c | 1 +
src/test/regress/expected/window.out | 50 ++++++++++++++++++++++++------------
src/test/regress/sql/window.sql | 25 ++++++++++++------
4 files changed, 67 insertions(+), 24 deletions(-)
diff --git a/src/backend/utils/adt/int8.c b/src/backend/utils/adt/int8.c
index 98d43237556..464b64bb5e7 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 596564fa15c..871119eeecc 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"
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 1f23baa3ccb..f8914c0401c 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -3481,13 +3481,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
@@ -3496,7 +3496,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
@@ -3608,19 +3608,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
@@ -3631,7 +3631,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
@@ -3653,13 +3653,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)
@@ -3667,7 +3667,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
@@ -3678,7 +3678,7 @@ SELECT * FROM
Subquery Scan on e
-> WindowAgg
Filter: ((row_number() OVER (?)) <= 1)
- Run Condition: (count(empsalary.salary) OVER (?) <= 3)
+ Run Condition: (count(1) OVER (?) <= 3)
-> Sort
Sort Key: (((empsalary.depname)::text || ''::text))
-> WindowAgg
@@ -3694,7 +3694,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
@@ -3765,6 +3765,24 @@ 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)
+
-- 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 253c1b70892..2a5aa38d29a 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1068,14 +1068,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;
@@ -1131,13 +1131,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;
@@ -1146,7 +1146,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;
@@ -1157,7 +1157,7 @@ SELECT * FROM
(SELECT empno,
depname,
salary,
- count(empno) OVER () c
+ count(1) OVER () c
FROM empsalary) emp
WHERE c = 1;
@@ -1165,7 +1165,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
@@ -1175,7 +1175,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
@@ -1215,6 +1215,15 @@ 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;
+
-- Ensure we don't use a run condition when the WindowFunc contains subplans
EXPLAIN (COSTS OFF)
SELECT * FROM
[parent: faba2f8f35df]