Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7e5d20b

Browse files
committed
Disable run condition optimization for some WindowFuncs
94985c2 added code to detect when WindowFuncs were monotonic andallowed additional quals to be "pushed down" into the subquery to beused as WindowClause runConditions in order to short-circuit executionin nodeWindowAgg.c.The Node representation of runConditions wasn't well selected andbecause we do qual pushdown before planning the subquery, the planningof the subquery could perform subquery pull-up of nested subqueries.For WindowFuncs with args, the arguments could be changed after pushingthe qual down to the subquery.This was made more difficult by the fact that the code duplicated theWindowFunc inside an OpExpr to include in the WindowClauses runConditionfield. This could result in duplication of subqueries and a pull-up ofsuch a subquery could result in another initplan parameter being issuedfor the 2nd version of the subplan. This could result in errors such as:ERROR: WindowFunc not found in subplan target listsHere in the backbranches, we don't have the flexibility to improve theNode representation to resolve this, so instead we just disable therunCondition optimization for ntile() unless the argument is a Const,(v16 only) and likewise for count(expr) (both v15 and v16). count(*) isunaffected. All other window functions which support this optimizationall take zero arguments and therefore are unaffected.Bug: #18170Reported-by: Zuming JiangDiscussion:https://postgr.es/m/18170-f1d17bf9a0d58b24@postgresql.orgBackpatch-through 15 (master will be fixed independently)
1 parentfaba2f8 commit7e5d20b

File tree

4 files changed

+67
-24
lines changed

4 files changed

+67
-24
lines changed

‎src/backend/utils/adt/int8.c

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -833,6 +833,21 @@ int8inc_support(PG_FUNCTION_ARGS)
833833
SupportRequestWFuncMonotonic*req= (SupportRequestWFuncMonotonic*)rawreq;
834834
MonotonicFunctionmonotonic=MONOTONICFUNC_NONE;
835835
intframeOptions=req->window_clause->frameOptions;
836+
WindowFunc*wfunc=req->window_func;
837+
838+
if (list_length(wfunc->args)==1)
839+
{
840+
Node*expr=eval_const_expressions(NULL,linitial(wfunc->args));
841+
842+
/*
843+
* Due to the Node representation of WindowClause runConditions in
844+
* version prior to v17, we need to insist that the count arg is
845+
* Const to allow safe application of the runCondition
846+
* optimization.
847+
*/
848+
if (!IsA(expr,Const))
849+
PG_RETURN_POINTER(NULL);
850+
}
836851

837852
/* No ORDER BY clause then all rows are peers */
838853
if (req->window_clause->orderClause==NIL)

‎src/backend/utils/adt/windowfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,7 @@
1414
#include"postgres.h"
1515

1616
#include"nodes/supportnodes.h"
17+
#include"optimizer/optimizer.h"
1718
#include"utils/builtins.h"
1819
#include"windowapi.h"
1920

‎src/test/regress/expected/window.out

Lines changed: 34 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -3481,13 +3481,13 @@ EXPLAIN (COSTS OFF)
34813481
SELECT * FROM
34823482
(SELECT empno,
34833483
salary,
3484-
count(empno) OVER (ORDER BY salary DESC) c
3484+
count(1) OVER (ORDER BY salary DESC) c
34853485
FROM empsalary) emp
34863486
WHERE c <= 3;
3487-
QUERY PLAN
3488-
---------------------------------------------------------
3487+
QUERY PLAN
3488+
-------------------------------------------
34893489
WindowAgg
3490-
Run Condition: (count(empsalary.empno) OVER (?) <= 3)
3490+
Run Condition: (count(1) OVER (?) <= 3)
34913491
-> Sort
34923492
Sort Key: empsalary.salary DESC
34933493
-> Seq Scan on empsalary
@@ -3496,7 +3496,7 @@ WHERE c <= 3;
34963496
SELECT * FROM
34973497
(SELECT empno,
34983498
salary,
3499-
count(empno) OVER (ORDER BY salary DESC) c
3499+
count(1) OVER (ORDER BY salary DESC) c
35003500
FROM empsalary) emp
35013501
WHERE c <= 3;
35023502
empno | salary | c
@@ -3608,19 +3608,19 @@ WHERE rn < 3;
36083608
-> Seq Scan on empsalary
36093609
(6 rows)
36103610

3611-
-- likewise with count(empno) instead of row_number()
3611+
-- likewise with count(1) instead of row_number()
36123612
EXPLAIN (COSTS OFF)
36133613
SELECT * FROM
36143614
(SELECT empno,
36153615
depname,
36163616
salary,
3617-
count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c
3617+
count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c
36183618
FROM empsalary) emp
36193619
WHERE c <= 3;
36203620
QUERY PLAN
36213621
------------------------------------------------------------
36223622
WindowAgg
3623-
Run Condition: (count(empsalary.empno) OVER (?) <= 3)
3623+
Run Condition: (count(1) OVER (?) <= 3)
36243624
-> Sort
36253625
Sort Key: empsalary.depname, empsalary.salary DESC
36263626
-> Seq Scan on empsalary
@@ -3631,7 +3631,7 @@ SELECT * FROM
36313631
(SELECT empno,
36323632
depname,
36333633
salary,
3634-
count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c
3634+
count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c
36353635
FROM empsalary) emp
36363636
WHERE c <= 3;
36373637
empno | depname | salary | c
@@ -3653,21 +3653,21 @@ SELECT * FROM
36533653
(SELECT empno,
36543654
depname,
36553655
salary,
3656-
count(empno) OVER () c
3656+
count(1) OVER () c
36573657
FROM empsalary) emp
36583658
WHERE c = 1;
3659-
QUERY PLAN
3660-
--------------------------------------------------------
3659+
QUERY PLAN
3660+
------------------------------------------
36613661
WindowAgg
3662-
Run Condition: (count(empsalary.empno) OVER (?) = 1)
3662+
Run Condition: (count(1) OVER (?) = 1)
36633663
-> Seq Scan on empsalary
36643664
(3 rows)
36653665

36663666
-- Some more complex cases with multiple window clauses
36673667
EXPLAIN (COSTS OFF)
36683668
SELECT * FROM
36693669
(SELECT *,
3670-
count(salary) OVER (PARTITION BY depname || '') c1, -- w1
3670+
count(1) OVER (PARTITION BY depname || '') c1, -- w1
36713671
row_number() OVER (PARTITION BY depname) rn, -- w2
36723672
count(*) OVER (PARTITION BY depname) c2, -- w2
36733673
count(*) OVER (PARTITION BY '' || depname) c3 -- w3
@@ -3678,7 +3678,7 @@ SELECT * FROM
36783678
Subquery Scan on e
36793679
-> WindowAgg
36803680
Filter: ((row_number() OVER (?)) <= 1)
3681-
Run Condition: (count(empsalary.salary) OVER (?) <= 3)
3681+
Run Condition: (count(1) OVER (?) <= 3)
36823682
-> Sort
36833683
Sort Key: (((empsalary.depname)::text || ''::text))
36843684
-> WindowAgg
@@ -3694,7 +3694,7 @@ SELECT * FROM
36943694
-- Ensure we correctly filter out all of the run conditions from each window
36953695
SELECT * FROM
36963696
(SELECT *,
3697-
count(salary) OVER (PARTITION BY depname || '') c1, -- w1
3697+
count(1) OVER (PARTITION BY depname || '') c1, -- w1
36983698
row_number() OVER (PARTITION BY depname) rn, -- w2
36993699
count(*) OVER (PARTITION BY depname) c2, -- w2
37003700
count(*) OVER (PARTITION BY '' || depname) c3 -- w3
@@ -3765,6 +3765,24 @@ WHERE c = 1;
37653765
-> Seq Scan on empsalary
37663766
(6 rows)
37673767

3768+
-- Ensure we don't use a run condition when the WindowFunc arg contains a Var
3769+
EXPLAIN (COSTS OFF)
3770+
SELECT * FROM
3771+
(SELECT empno,
3772+
salary,
3773+
count(empno) OVER (ORDER BY empno DESC) c
3774+
FROM empsalary) emp
3775+
WHERE c = 1;
3776+
QUERY PLAN
3777+
----------------------------------------------
3778+
Subquery Scan on emp
3779+
Filter: (emp.c = 1)
3780+
-> WindowAgg
3781+
-> Sort
3782+
Sort Key: empsalary.empno DESC
3783+
-> Seq Scan on empsalary
3784+
(6 rows)
3785+
37683786
-- Ensure we don't use a run condition when the WindowFunc contains subplans
37693787
EXPLAIN (COSTS OFF)
37703788
SELECT * FROM

‎src/test/regress/sql/window.sql

Lines changed: 17 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1068,14 +1068,14 @@ EXPLAIN (COSTS OFF)
10681068
SELECT*FROM
10691069
(SELECT empno,
10701070
salary,
1071-
count(empno) OVER (ORDER BY salaryDESC) c
1071+
count(1) OVER (ORDER BY salaryDESC) c
10721072
FROM empsalary) emp
10731073
WHERE c<=3;
10741074

10751075
SELECT*FROM
10761076
(SELECT empno,
10771077
salary,
1078-
count(empno) OVER (ORDER BY salaryDESC) c
1078+
count(1) OVER (ORDER BY salaryDESC) c
10791079
FROM empsalary) emp
10801080
WHERE c<=3;
10811081

@@ -1131,13 +1131,13 @@ SELECT empno, depname FROM
11311131
FROM empsalary) emp
11321132
WHERE rn<3;
11331133

1134-
-- likewise with count(empno) instead of row_number()
1134+
-- likewise with count(1) instead of row_number()
11351135
EXPLAIN (COSTS OFF)
11361136
SELECT*FROM
11371137
(SELECT empno,
11381138
depname,
11391139
salary,
1140-
count(empno) OVER (PARTITION BY depnameORDER BY salaryDESC) c
1140+
count(1) OVER (PARTITION BY depnameORDER BY salaryDESC) c
11411141
FROM empsalary) emp
11421142
WHERE c<=3;
11431143

@@ -1146,7 +1146,7 @@ SELECT * FROM
11461146
(SELECT empno,
11471147
depname,
11481148
salary,
1149-
count(empno) OVER (PARTITION BY depnameORDER BY salaryDESC) c
1149+
count(1) OVER (PARTITION BY depnameORDER BY salaryDESC) c
11501150
FROM empsalary) emp
11511151
WHERE c<=3;
11521152

@@ -1157,15 +1157,15 @@ SELECT * FROM
11571157
(SELECT empno,
11581158
depname,
11591159
salary,
1160-
count(empno) OVER () c
1160+
count(1) OVER () c
11611161
FROM empsalary) emp
11621162
WHERE c=1;
11631163

11641164
-- Some more complex cases with multiple window clauses
11651165
EXPLAIN (COSTS OFF)
11661166
SELECT*FROM
11671167
(SELECT*,
1168-
count(salary) OVER (PARTITION BY depname||'') c1,-- w1
1168+
count(1) OVER (PARTITION BY depname||'') c1,-- w1
11691169
row_number() OVER (PARTITION BY depname) rn,-- w2
11701170
count(*) OVER (PARTITION BY depname) c2,-- w2
11711171
count(*) OVER (PARTITION BY''|| depname) c3-- w3
@@ -1175,7 +1175,7 @@ SELECT * FROM
11751175
-- Ensure we correctly filter out all of the run conditions from each window
11761176
SELECT*FROM
11771177
(SELECT*,
1178-
count(salary) OVER (PARTITION BY depname||'') c1,-- w1
1178+
count(1) OVER (PARTITION BY depname||'') c1,-- w1
11791179
row_number() OVER (PARTITION BY depname) rn,-- w2
11801180
count(*) OVER (PARTITION BY depname) c2,-- w2
11811181
count(*) OVER (PARTITION BY''|| depname) c3-- w3
@@ -1215,6 +1215,15 @@ SELECT * FROM
12151215
FROM empsalary) emp
12161216
WHERE c=1;
12171217

1218+
-- Ensure we don't use a run condition when the WindowFunc arg contains a Var
1219+
EXPLAIN (COSTS OFF)
1220+
SELECT*FROM
1221+
(SELECT empno,
1222+
salary,
1223+
count(empno) OVER (ORDER BY empnoDESC) c
1224+
FROM empsalary) emp
1225+
WHERE c=1;
1226+
12181227
-- Ensure we don't use a run condition when the WindowFunc contains subplans
12191228
EXPLAIN (COSTS OFF)
12201229
SELECT*FROM

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp